찾기/참조영역에서 가장 많이 쓰이는 함수가 VLOOKUP 함수가 아닐까 싶은데요. 이 함수의 경우 하나의 조건을 만족하는 값만 반환해주기 때문에 여러가지 조건을 만족하는 값을 찾기 위해서는 이번시간에 알아볼 INDEX 함수와 MATCH 함수를 사용해야 합니다.
Thank you for reading this post, don't forget to subscribe!이 함수들은 단독으로 사용하기도 하지만, 실제 두 함수를 함께 사용할 경우 상당히 뛰어난 기능을 구현할 수 있기 때문에 사용법을 반드시 기억해 두는 것이 좋은데요.
지금부터 사용하는 방법을 하나씩 알아보도록 하겠습니다.
INDEX 함수와 MATCH 함수 사용법
먼저 두함수의 기본적인 사용법부터 잠깐 확인해 보도록 하겠습니다.
INDEX 함수의 구문은 =INDEX(참조할범위,셀위치) 로 표현할 수 있으며, MATCH 함수는 =MATCH(찾을값,찾을범위,찾을형태)라고 표현할 수 있습니다.
이렇게 두 함수를 함께 사용하게 되면 한가지 조건뿐만 아니라 적용하기에 따라 여러가지 조건을 만족하는 방법을 찾을수 있게 됩니다.
예제 1. 한가지 조건 만족하는 값 찾기
먼저 한가지 조건을 만족하는 값을 찾고자 할 때 이 두함수를 어떻게 사용하는지 알아보도록 하겠습니다.
구문을 살펴보면,
=INDEX(참조할범위,MATCH(찾을값,찾을범위,찾을형태))
이렇게 설명할 수 있습니다. 조금 어려울수도 있는데요. 다음 표를 보면 좀 더 쉽게 이해할 수 있을 것입니다.
위 표를 기준으로 서울 지역에서 단가라는 항목을 찾으려면 =INDEX(D3:D10,MATCH(H3,A3:A10,0)) 이라고 수식을 작성하게 됩니다.
한가지 조건만을 만족하는 형태로 수식을 작성하였기 때문에 실제 서울이라는 지역이 두곳이지만, 맨 윗쪽에 있는 값만 확인할 수 있게 됩니다.
이럴 경우 굳이 INDEX 함수와 MACTH 함수를 사용하기보다는 VLOOKUP 함수를 사용하는 것이 더 편합니다.
=VLOOKUP(H3,A3:A10,4)라고 입력하면 동일한 결과를 얻을 수 있기 때문이죠.
그럼에도 불구하고 INDEX 함수와 MATCH 함수를 사용하는 이유는 다음 예제에서 확인할 조건이 많을 경우 때문입니다.
예제 2. 두 가지 조건을 만족하는 값 찾기
두가지 조건을 만족하는 값을 찾기 위해서는 인수를 사용할 때 &를 사용하여 연결해 주어야 합니다.
위 표를 기준으로 서울이라는 지역의 A라는 품목의 단가를 구하고자 할때 수식은 다음과 같이 작성하게 됩니다.
=INDEX(D3:D10,MATCH(H4&I4,A3:A10&B3:B10,0))
이렇게 말이죠.
첫번째 예제와 달라진 점은 &를 사용했다는 것과 {}가 사용되었다는 점인데요. &를 사용해서 인수를 작성 했고, 또한 이런 배열이 들어간 함수를 그대로 작성하게 되면 #VALUE! 에러가 발생하게 되는데, CTRL+SHIFT+ENTER 키를 눌러 배열함수로 적용해 주어야 정상적인 값이 반환되도록 한 것입니다.
배열함수로 적용하는 방법은 셀을 선택하고 수식입력줄에 커서를 이동시킨후 CTRL+SHIFT+ENTER 키를 동시에 누르거나 혹은 셀을 더블클릭한뒤에 동시에 눌러주면 적용이 됩니다.
예제3. 세가지 조건을 만족하는 값 찾기
그럼 마지막으로 세가지 조건을 만족하는 값을 찾기 위해서는 수식을 어떻게 작성하는지 알아보겠습니다.
먼저 문제로 작성해 보면, 서울 지역 A라는 품목의 단위가 box인 제품의 단가를 구하라? 라고 가정했을 때 수식을 작성하면 다음과 같습니다.
=INDEX(D3:D10,MATCH(H5&I5&J5,A3:A10&B3:B10&C3:C10,0))
두번째 예시에서 단위 부분이 더 추가되었죠.
마찬가지 이 경우에도 CTRL+SHIFT+ENTER키를 눌러 배열 함수로 작성해 주어야 정상적으로 값을 반환해 주게 됩니다.
조건에 따른 수식 비교
=INDEX(D3:D10,MATCH(H3,A3:A10,0)) – 한가지 조건 만족
=INDEX(D3:D10,MATCH(H4&I4,A3:A10&B3:B10,0)) – 두가지 조건 만족
=INDEX(D3:D10,MATCH(H5&I5&J5,A3:A10&B3:B10&C3:C10,0)) – 세가지 조건 만족
위 수식들을 비교해 보면 훨씬 더 쉽게 이해할 수 있을 것입니다.
조건이 늘어날 수록 &와 조건만 추가해 주면 되는 것이기 때문에 너무 어렵게 생각하지 않아도 됩니다.
결론
이번 시간에는 INDEX 함수와 MATCH 함수를 이용하여 여러가지 조건을 만족하는 값을 찾고자 할때 사용할 수 있는 방법에 대해 알아보았습니다.
INDEX 라는 함수의 구문에 MATCH 함수를 인수로 사용하여 간단히 작성하는 것만으로도 생각보다 뛰어난 결과를 얻을 수 있다는 것을 확인하는 계기가 되었으리라 생각되는데요.
다소 어렵게 느껴질 수 있지만, 실제 사용해 보면 구문이 단순하기 때문에 별 어려움 없이 사용할 수 있습니다.
그렇게 여러가지 조건을 추가해가면서 연습 하다보면 VLOOKUP 함수로 해결할 수 없는 문제도 충분히 해결할 수 있으리라 생각됩니다.
VLOOKUP 함수 자세히 알아보기
엑셀 VLOOKUP 함수로 목록에서 데이터 불러오기(예제 3가지)
INDEX 함수 사용법
MATCH 함수 사용법