[엑셀] 엑셀 vlookup 함수 여러개 조건 만족값 찾기예제(index,match)

출처: http://exceltong.tistory.com/22

엑셀 vlookup함수에 대한 활용은 입이 마르도록 얘기해도 과하지 않다.

엑셀 vlookup함수 기본사용, iferror함수로 오류값 제거 그리고 마지막으로 vlookup 기준값이 2개이상(복수) 조건을 만족해야되는 경우에 대해 이전글에서 살펴봤다.

 

[엑셀 vlookup 사용법] - 엑셀 vlookup함수 사용법 2개이상 조건 일치값 찾기

[엑셀 vlookup 사용법] - 엑셀 vlookup 사용법 iferror함수로 #N/A 오류값 숨기기

[엑셀 vlookup 사용법] - 엑셀 원하는값 찾기 vlookup함수 예제와 사용법

 

이중 vlookup 여러개 조건을 만족하는 값 찾기로 소개된 위 첫번째 글에서 찾고자 하는값(기준값)이 참조영역에 유일하게 존재해야하는 전제를 해결하기 위해 여러개 조건을 문자열 조합으로 새로운 기준값 항목을 만드는 엑셀예제를 소개한 바 있다.

 

오늘은 vlookup함수의 한계를 넘어 INDEX, MATCH 조합으로 여러개 조건을 만족하는 값을 찾아보자.

 

INDEX(array, row_num, [column_num])

 - array(배열) 배열 상수나 셀 범위입니다.
 - 배열에 행이나 열이 하나만 있을 때는 row_num이나 column_num 인수를 생략가능
 - row_num 필수요소, column_num 선택요소

 

MATCH(lookup_value, lookup_array, [match_type])

 - lookup_value lookup_array에서 찾으려는 값.
 - lookup_array 검색할 셀 범위
 - match_type 숫자 -1, 0 또는 1로 일치하는 값을 찾고자 할때는 0 값을 입력

 

이제 엑셀예제를 통해 여러개의 조건을 만족하는 값을 찾아보도록 하자.

 

엑셀예제는 ㅇㅇ문구점 판매현황으로 물품수량, 단가 등 정리된 자료(sheet명 : 데이터)

물품 중 "도장", "상호"와 일치되는 값을 찾는 예제이다.

 

기준값(찾고자 하는 값)은 10호 도장, 11호 도장, 13호 도장의 수량, 단가 등을 찾는 예제로 vlookup함수를 이용한다면 앞서 소개한 글처럼 "데이터", "예제" 시트(sheet)에 제일 왼쪽 열에 기준값인 "상호", "품명"을 문자열 조합의 새로운 항목으로 만들어야 가능하다.

 

하지만 INDEX, MATCH 조합의 배열수식으로 쉽게 해결할 수 있다.

 

○ 수식 구문( 10호 도장, 수량 셀에 적용)
 {=INDEX(데이터!D$2:D$36,MATCH(예제!$B2&예제!$C2,데이터!$B$2:$B$36&데이터!$C$2:$C$36,0))}

 

○ 구문설명

 -  INDEX 찾을 자료 영역(데이터!D$2:D$36)은 실질적인 수치값 영역을 선택한 후 row_num 인수를 match함수로 찾는다.

 - MATCH 찾을 값(예제!$B2&예제!$C2)은 "상호", "품명" 문자열조합으로 구성하고 찾을 영역은(데이터!$B$2:$B$36&데이터!$C$2:$C$36,0)) 배열수식 문자열조합으로 구성한다.

 - 고정할 열과 가변적인 열을 찬찬히 살펴보면 절대참조($)을 적절히 활용하여 복사 후 붙여넣기로 모두 찾는다.

 

○ 구문에 {} 표시는 배열수식으로 CSE(Ctrl + Shift + Enter) 동시에 눌러야 된다는 것은 누차 강조했다.

○ 아래 엑셀예제를 내려받아 적용된 수식 구문을 살펴보면 쉽게 파악된다.(수식 보기 단축키 Ctrl + `)

 

엑셀예제파일- 엑셀 vlookup 여러개 조건 만족값 찾기예제(index,match).xls