찾기/참조영역에서 가장 많이 쓰이는 함수가 VLOOKUP 함수가 아닐까 싶은데요. 이 함수의 경우 하나의 조건을 만족하는 값만 반환해주기 때문에 여러가지 조건을 만족하는 값을 찾기 위해서는 이번시간에 알아볼 INDEX 함수와 MATCH 함수를 사용해야 합니다.
이 함수들은 단독으로 사용하기도 하지만, 실제 두 함수를 함께 사용할 경우 상당히 뛰어난 기능을 구현할 수 있기 때문에 사용법을 반드시 기억해 두는 것이 좋은데요.
지금부터 사용하는 방법을 하나씩 알아보도록 하겠습니다.
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 함수 사용법
