[엑셀 활용] INDEX + MATCH 함수의 장점 배우기

엑셀 고급 사용자 대부분은 INDEX MATCH 함수가 VLOOKUP 함수보다 낫다고 말한다. 하지만 많은 엑셀 사용자는 여전히 보다 단순한 VLOOKUP 함수를 이용한다. VLOOKUP 에서 INDEX MATCH 함수로의 전환에 대한 잇점을 이해하지 못하기 때문이다. 장점을 알지 못하면서 보다 복잡한 INDEX MATCH 조합의 수식을 배울 이유가 없는 것이다.

이제 그 장점을 이해하여 INDEX MATCH 함수로의 전환을 시도해 보자.

INDEX MATCH 조합이 VLOOKUP 함수보다 나은점

1) 검색범위 왼쪽열 반환

VLOOKUP 함수는 테이블 범위( table_array ) 가장 왼쪽 열에서 검색값을 찾는다. 이 말은 반환 열의 오른쪽이 검색 열이 될 수 없다는 것이다. 하지만, INDEX MATCH 함수는 반환할 열의 오른쪽 열에서 값을 찾을 수도 있다. 이전 게시물의 사용 예제2 는 VLOOKUP 함수만으로는 불가능하다.

[그림1] INDEX MATCH 함수로 검색 열의 왼쪽 열 반환

[컴활 2급] 값을 찾아 그 위치를 숫자로, 엑셀 MATCH 함수 :
http://secstart.tistory.com/547
[엑셀 모바일] INDEX 함수 행 열 주소로 셀 참조하기 - 기본 형식 :
http://secstart.tistory.com/750

2) 열 삽입과 삭제 후 편리한 처리

VLOOKUP 함수는 검색범위에서 반환할 열 순번을 숫자로 지정하기 때문에 열이 추가되거나 삭제되면 오류나 잘못된 결과를 반환할 수 있다.

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

예1: =VLOOKUP( A7, A5:E11, 5, FALSE ) 수식이 이미 작성되었고 5 번째 열에서 결과를 얻는다. 그런데 A5:E11 의 테이블 범위( table_array )에 열이 새로 추가되었을 때, 세 번째 인수 5 를 6 으로 수정하지 않는다면 잘못된 결과를 얻게 된다.

[그림2] VLOOKUP 테이블 범위에 열 삽입이 생길 경우

만일, 위의 예1 의 수식을 INDEX MATCH 로 작성한다면 =INDEX( E5:E11, MATCH( A7, A5:A11, 0 ) ) 의 수식이 된다.
이 수식은 열의 추가나 삭제가 있더라도 예1과 같이 잘못된 결과를 반환하지 않는다. INDEX 함수가 원하는 값이 포함된 열을 직접 지정하였기 때문이다.

[그림3] INDEX MATCH 데이터 범위에 열 삽입이 생길 경우

이는 계속하여 갱신되거나 추가되는 다량의 데이터 작업시 엄청난 잇점이다. 함수식의 수정을 걱정할 필요없이 열을 추가하거나 삭제할 수 있기 때문이다. 이전 게시물의 사용 예제1 이 이와 같은 수식이다.

3) 검색값( lookup_value ) 의 길이 제한 해결

VLOOKUP 함수는 검색값( lookup_value )의 길이가 255자를 넘지 못한다. 그러하다면 #VALUE! 오류를 발생한다. 이 문제를 해결할 방법은 INDEX MATCH 조합을 사용하는 방법밖에 없다.

VLOOKUP 함수를 다음과 같이 사용하였다. 그런데 B6 셀의 값이 255 자를 넘는다면 #VALUE! 오류가 발생한다.
=VLOOKUP( B6, B5:D11 ,3, 0 )

이를 해결하자면 INDEX MATCH 함수의 조합을 사용하여야 한다.
=INDEX( D5:D11, MATCH( TRUE, INDEX( B5:B11=B6, 0 ), 0 ) )

VLOOKUP 수식에서 반환값의 범위는 B5:D11 의 세번째 열인 D 열이다. 따라서, INDEX 함수에서는 지정된 D5:D11 의 범위에서 MATCH 함수로 반환된 행 위치의 셀 값을 반환한다.

[그림4] 255자 이상의 문자열 찾기

여기서, MATCH 함수를 유심히 살펴보자. TRUE를 찾고 검색범위 로는 다시 INDEX 함수를 사용한다. B5:B11 범위의 각 셀과 B6 값을 각각 비교하여 배열로 내부 저장한다. INDEX 의 결과는 {FALSE;TRUE;...} 의 형태가 되고 이 배열에서 TRUE 값의 위치를 찾아 반환하는 것이다. 2가 반환되고 =INDEX( D5:D11, 2 ) 이 최종 수식이 된다.

[그림5] 그림4 의 수식 계산하기

VLOOKUP 함수에서는 검색값의 길이 제한으로 오류가 발생하는 수식이 INDEX MATCH 함수로 이렇게 해결되는 것이다. 이 문제 해결에서 INDEX 함수의 행/열 인수가 0 이다. 이는 모든 array 인수를 말한다. 이점도 유의하자.

4) 빠른 처리 속도

VLOOKUP 함수보다 INDEX MATCH 조합을 사용하면 엑셀의 성능이 향상된다. 적은량의 수식 처리에는 큰 차이를 느낄 수 없지만 한 워크시트에 많은 수식과 많은 행이 포함되어 있다면 상황은 달라진다. 또 여기에 수식이 배열을 포함하고 있다면 더욱 차이를 느낄 수 있다. 이렇게 다량의 데이터 처리에는 INDEX MATCH 함수 조합을 활용해 보자.

엑셀 INDEX MATCH 함수 조합의 유용함과 장점을 알았고, 반대로는 VLOOKUP 함수의 단점을 해결할 방법을 안 것이다. 이제는 VLOOKUP 함수에만 얽매이지 말고 두 함수 조합의 테이블 검색 방법을 활용하여 보자.

[엑셀 활용] INDEX + MATCH 함수로 VLOOKUP 대체하기 :
http://secstart.tistory.com/773

마늘빵.