[엑셀 활용] INDEX + MATCH 함수로 VLOOKUP 대체하기

업무나 일상에서 자주 사용되는 엑셀 찾기 함수가 VLOOKUP 함수이다. 이 엑셀 VLOOKUP 함수는 왼쪽 첫 열에서 값을 찾아 같은 행에서의 원하는 열의 값을 반환하는 함수이다. 이 VLOOKUP 함수와 HLOOKUP 함수까지 대체할 수 있는 조합이 INDEX + MATCH 함수의 활용이다.

VLOOKUP 을 대신하는 INDEX + MATCH 의 사용법을 배우면서 그 장점도 살펴보자.
먼저 두 함수의 구문과 간략한 설명을 본다.

엑셀 INDEX 함수 구문

구문1 : INDEX( array, row_num, [col_num] )
구문2 : INDEX( 범위, 행 위치, [열 위치] )

엑셀 INDEX 함수는 전달된 array 인수 범위에서의 행 위치와 열 위치를 숫자로 전달하여 해당 셀 값을 얻는다.

[엑셀 모바일] INDEX 함수 행 열 주소로 셀 참조하기 - 기본 형식 :
http://secstart.tistory.com/750

엑셀 MATCH 함수 구문

구문1 : MATCH( lookup_value, lookup_array, [match_type] )
구문2 : MATCH( 검색값, 검색범위, [검색방법] )

엑셀 MATCH 함수는 검색값( lookup_array ) 을 검색범위( lookup_array ) 에서 찾아 검색범위 에서의 상대위치를 숫자로 반환한다. 세번째 인수는 검색값 을 찾는 방법을 선택한다. ( 1 : 일치값이 없을 때는 보다 작은 가장 큰 값, 0 : 정확한 일치 값, -1 : 보다 큰 가장 작은 값 )

[컴활 2급] 값을 찾아 그 위치를 숫자로, 엑셀 MATCH 함수 :
http://secstart.tistory.com/547

엑셀 INDEX + MATCH 함수 조합 사용 예제

중첩되는 MATCH 함수로 원하는 값을 찾고 반환된 위치를 INDEX 에 전달하여 행이나 열의 상대위치로 사용한다. 이것이 INDEX + MATCH 함수 조합이다. INDEX 함수의 행( row_num )이나 열( col_num )인수 자리에 MATCH 함수를 포함하는 것이다.

=INDEX( 범위, MATCH( 검색값1, 검색범위1, 검색방법1 ), MATCH( 검색값2, 검색범위2, 검색방법2 ) )

위의 함수 구문이 INDEX + MATCH 함수 조합의 기본 구문이다. 행/열 두 인수 중 하나만 MATCH 함수를 사용할 수도 있다.

이 기본 구문에서 주의 할 것이 있다. INDEX 함수의 범위( array ) 와 MATCH 함수의 검색범위( lookup_array ) 가 서로 다르더라도 그 행이나 열 크기가 같아야 한다. 그렇지 않으면 잘못된 결과를 얻을 수 있다.

[그림1] 엑셀 INDEX + MATCH 함수로 VLOOKUP 대신하기

사용 예제1 : =INDEX( E5:E11, MATCH( "엑셀 실무", A5:A11, 0 ) )
사용 결과1 : 90000

- MATCH 함수의 검색값 으로 "엑셀 실무" 가 직접 전달되었다. 이 값을 A5:A11 의 검색범위 에서 찾는다. 세 번째 인수가 0 으로 주어져 정확하게 일치하는 값을 찾는다. "엑셀 실무" 값의 위치는 A10 이며 검색범위 에서의 상대위치 6 이 MATCH 함수의 결과이다.

- INDEX 함수로 지정한 행과 열 위치의 셀 값을 E5:E11 범위에서 반환한다. MATCH 함수의 결과 6 이 사용되어 예제1의 수식은 =INDEX( E5:E11, 6 ) 과 같아진다. 범위( E5:E11 )에서 6 행의 위치, E10 의 셀 값, 90000 이 최종 결과로 반환된다. ( 열 인수가 생략되면 모든 열을 의미한다 )

사용 예제2 : =INDEX( A5:E11, MATCH( B7, B5:B11, 0 ), 1 )
사용 결과2 : 컴활 2급

- MATCH 함수의 검색값인 B7 셀의 값, '이서린' 을 B5:B11 의 검색범위 에서 찾는다. 세 번째 인수가 0 이라 역시 정확한 일치값을 찾는다. B7 셀에 위치하기에 상대위치 3 이 결과 반환된다.

- INDEX 함수로 전달된 A5:E11 의 범위 인수에서 MATCH 의 결과 3 이 행 위치로, 1 이 열 위치로 사용된다. 상대 위치인 3 행과 1 열이 교차하는 셀은 A7 이다. 셀 값 "컴활 2급" 이 최종 결과로 반환된다.

엑셀 INDEX MATCH 함수의 조합은 이렇게 사용되어 VLOOKUP 함수의 기능을 수행할 수 있다. 또, MATCH 함수가 INDEX 함수의 col_num 인수 자리에 사용되면 HLOOKUP 함수의 기능을 대신하게 되는 것이다. VLOOKUP 함수를 대신할 수 있는 INDEX MATCH 함수 조합 구문을 배웠다. 활용해 보기 바란다.

[엑셀 모바일] VLOOKUP 함수로 수직 검색 후 지정 열 반환하기 :
http://secstart.tistory.com/754

아! 그렇다면 간단한 VLOOKUP 함수를 두고 왜 굳이 더 복잡한 INDEX MATCH 함수 조합을 사용하는 것일까? 잘 생각해 보자. 이는 다음 게시물에서 함께 배울 것이다.

INDEX MATCH 조합이 VLOOKUP 함수보다 나은점 :
http://secstart.tistory.com/774

마늘빵.