(엑셀) MATCH, INDEX 함수 사용법 - 고급편 (2가지 조건을 만족하는 값 찾기)

지난 시간에 이어 MATCH와 INDEX 함수를 좀 더 알아보겠습니다.

이름하여 고급편~! (빠~밤!)

기본편의 경우 VLOOKUP 함수를 쓰면 구할 수 있는 걸 굳이 MATCH와 INDEX 함수를 써서 힘들게 해결해 보았는데요.

이번 시간에는 VLOOKUP 함수만으로는 해결할 수 없는 문제를 MATCH와 INDEX 함수를 응용해서 해결해 보겠습니다.

강의 자료는 포스팅 제일 밑에 있으니 엑셀 파일 다운 받으셔서 직접 수식 확인하시면 됩니다.

아래와 같은 표가 있습니다. 다시멸치와 쥐포를 취급하는 거래처와 연락처, 판매가격에 관한 표인데요.

 

최종적으로 원하는 것은 위에 표를 참고해서 아래 표에 광주상회의 다시멸치, 대전상회의 쥐포 판매가격을 찾는 것입니다. 참고로 VLOOKUP 함수를 이용해 구해보면 D17, D18 셀과 같이 잘 못된 판매가격이 출력됩니다. 

 

왜냐하면 VLOOKUP 함수는 한 가지 조건 밖에 고려하지 못하기 때문인데요. 함수 인자에서 Lookup_value로 B17(다시멸치)을 지정하면 그 Table_array에서 만족되는 값을 입력된 순서대로 찾기 때문입니다.

 

즉, 다시멸치의 경우 서울상회가 가장 위에 위치하기 때문에 판매가격으로 10,000이 찾아지는 것 입니다. 거래처는 고려하지 못 하죠. 하지만 MATCH와 INDEX 함수를 조금만 응용하면 제품명과 거래처 2가지 조건을 모두 고려하여 만족하는 판매가격을 찾아올 수 있습니다.

 

아래는 제가 최종적으로 완성한 결과물인데요. 광주상회의 다시멸치, 대전상회의 쥐포 판매가격이 정상적으로 찾아진 것을 확인하실 수 있습니다. 그러면 어떻게 찾았는지 수식 분석을 해보겠습니다.

 

먼저 보다 쉽게 이해하실 수 있게 『제품명&거래처_조건』이라는 열을 하나 더 만들었는데요. 이것부터 설명드리겠습니다. E17셀에 입력되어 있는 수식은 다음과 같습니다. (E17의 결과값은 2)
=MATCH(B17&C17,INDEX($B$5:$B$12&$C$5:$C$12,,),0)
다소 복잡합니다. 

 

이 수식에서 원하는 건 제품명과 거래처 2가지 조건을 만족하는 행의 위치를 불러오고자 하기 위함입니다. 행의 위치를 불러오기 위해 MATCH 함수가 메인이 됩니다. 그리고 그 안에 INDEX 함수를 넣었는데요. 여기서 INDEX 함수의 역할은 원하는 위치의 값을 찾는 것이 아닌 Lookup_array가 되는 것 입니다.

 

쉽게 말씀드리면 내가 지정한 범위가 된다고 보시면 되겠습니다. INDEX 함수를 보면 제품명과 거래처 열을 각각 범위로 선택한 뒤 『&』을 이용해 연결해주었구요, 행과 열의 위치를 지정하는 값은 별도로 입력하지 않았습니다. 그리고 MATCH 함수를 보면 제품명과 거래처를 『&』을 통해 연결했습니다.

 

예를들어 수식에 『"다시멸치"&"서울상회"』라고 입력하면 결과값은 『다시멸치서울상회』가 됩니다. 이것을 이용해 사실은 2가지 조건을 고려했다기 보다는2가지 조건을 하나로 합쳤다고 보는게 맞습니다. 이렇게 수식을 입력하게 되면 제품명&거래처를 만족하는 행의 상대위치가 출력되게 됩니다.

 

 

제품명과 거래처 2가지 조건을 만족하는 행의 상대위치가 나왔으니 INDEX 함수를 이용해 판매가격을 찾으면 됩니다. F17셀에 입력된 수식은 다음과 같습니다.
=INDEX($B$5:$E$12,MATCH(B17&C17,INDEX($B$5:$B$12&$C$5:$C$12,,),0),4)
녹색 부분에 입력된게 E17셀에 입력된 수식에서 추가된 부분입니다.

B5부터 E12까지 셀 범위를 지정하고, 행위치는 E17에 입력된 수식을 그대로 활용하고, 판매가격은 지정한 셀 범위(array)에서 4번째 열에 위치하였으니 열 위치로 4를 입력한 것 입니다.

 

어떠세요? 이해가 좀 되시나요? 이렇게 INDEX와 MATCH 함수를 응용하면 VLOOKUP 함수와 달리 여러가지 조건을 만족하는 값을 찾는 일도 가능합니다. 보기만 해선 어려우는 반드시 강의자료를 다운 받으셔서 직접해보시기 바랍니다.

사실 이제서야 말씀드리지만 약간의 꼼수(?)를 발휘하면 VLOOKUP 함수만으로도 위의 문제는 해결 가능합니다. ㅋㅋㅋ...
아래 보시는 것처럼 연락처는 필요없으니 없애고, 여기에 제품명과 거래처를 합친 새로운 열을 만들어줍니다. 그러면 이 값은 제품명과 거래처 모두를 만족하는 고유의 값이 되기 때문에 제품명과 거래처 단독 열처럼 중복이 발생하지 않습니다. 이걸 기준으로 VLOOKUP 함수를 이용하면 판매가격을 쉽게 찾아올 수 있습니다. 개인적으로는 MATCH나 INDEX 함수를 복잡하게 쓰는 것 보다 이 방법을 선호하긴 합니다. ^^;;

 

강의자료를 첨부하였으니 참고하시고 이해가 안가는 부분은 댓글 남겨주시기 바랍니다.

 

감사합니다.

 

170620_노센스의_엑셀강의_MATCH_INDEX_함수_고급편.xlsx

0.08MB