엑셀을 능숙하게 다루는 분이라면 VLOOKUP 함수를 이미 잘 알고 있을 것입니다. VLOOKUP 함수는 같은 행 내에서 일치하는 텍스트를 기준으로 다른 셀의 값을 찾아오는 데 사용되는 대표적인 조회 함수입니다.
아직 VLOOKUP 함수가 익숙하지 않은 분이라면, 먼저 엑셀에서 VLOOKUP을 사용하는 기본 방법을 다룬 글을 참고하신 후 이 글을 읽으시는 것을 추천합니다.
VLOOKUP은 강력한 함수이지만, 수식이 정상적으로 작동하려면 참조 테이블이 반드시 특정 구조로 정렬되어 있어야 한다는 제약이 있습니다.
이 글에서는 VLOOKUP을 사용할 수 없는 상황이 어떤 것인지 살펴보고, 이 문제를 해결할 수 있는 INDEX-MATCH 함수를 소개하겠습니다.
엑셀 INDEX MATCH 예제
아래 예제 엑셀 스프레드시트에는 차량 소유자 이름과 차량 이름 목록이 정리되어 있습니다. 이 예제에서는 여러 소유자 아래에 나열된 차량 모델(Car Model)을 기준으로 차량 ID(Car ID)를 가져오는 작업을 해보겠습니다.
CarType이라는 별도의 시트에는 ID, 차량 모델, 색상으로 구성된 간단한 차량 데이터베이스가 들어 있습니다.
이렇게 테이블이 구성되어 있을 때, VLOOKUP 함수가 작동하려면 가져오려는 데이터가 일치시키려는 값(차량 모델 필드)의 오른쪽 열에 위치해야 합니다.
다시 말해, 이 테이블 구조에서는 차량 모델을 기준으로 매칭하기 때문에 얻을 수 있는 정보는 색상뿐입니다. ID 열이 차량 모델 열의 왼쪽에 있기 때문에 ID는 가져올 수 없습니다.
그 이유는 VLOOKUP에서는 찾을 값이 반드시 첫 번째 열에 있어야 하고, 조회하려는 열들은 그 오른쪽에 있어야 하기 때문입니다. 우리 예제에서는 이 조건이 하나도 충족되지 않습니다.
다행히 INDEX-MATCH를 사용하면 이 문제를 손쉽게 해결할 수 있습니다. 실제로 INDEX-MATCH는 각각 독립적으로도 사용할 수 있는 두 가지 엑셀 함수, 즉 INDEX 함수와 MATCH 함수를 조합한 형태입니다.
다만 이 글에서는 VLOOKUP의 기능을 대체하는 것을 목표로 두 함수의 조합에 대해서만 집중적으로 다루겠습니다.
처음에는 수식이 다소 길고 어려워 보일 수 있습니다. 하지만 몇 번만 사용해 보면 금방 구문을 익히게 될 것입니다.
예제에서 사용한 전체 수식은 다음과 같습니다.
=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))
이제 각 부분을 하나씩 살펴보겠습니다.
=INDEX( – "="는 셀에서 수식이 시작됨을 나타내며, INDEX는 사용하는 엑셀 함수의 첫 번째 부분입니다.
CarType!$A$2:$A$5 – 가져오려는 데이터가 들어 있는 CarType 시트의 열 범위입니다. 이 예제에서는 각 차량 모델의 ID에 해당합니다.
MATCH( – 사용하는 엑셀 함수의 두 번째 부분입니다.
B4 – 검색 텍스트(차량 모델)가 들어 있는 셀입니다.
CarType!$B$2:$B$5 – 검색 텍스트와 일치 여부를 비교할 데이터가 있는 CarType 시트의 열 범위입니다.
0)) – 검색 텍스트가 매칭 열(CarType!$B$2:$B$5)의 텍스트와 정확히 일치해야 함을 나타냅니다. 정확히 일치하는 값을 찾지 못하면 수식은 #N/A 오류를 반환합니다.
참고: 함수 끝의 닫는 괄호 두 개 "))"와 인수 사이의 쉼표를 잊지 마세요.
저 역시 개인적으로 VLOOKUP을 사용하지 않게 되었고, 지금은 INDEX-MATCH를 주로 사용합니다. VLOOKUP보다 더 많은 작업을 수행할 수 있기 때문입니다.
INDEX-MATCH 함수는 VLOOKUP에 비해 다음과 같은 장점도 가지고 있습니다.
1. 더 빠른 계산 속도
VLOOKUP 함수가 많이 사용되는 대규모 데이터셋에서는 계산 자체에 상당한 시간이 걸릴 수 있습니다. 이런 수식을 모두 INDEX-MATCH로 교체하면 전체 계산 속도가 눈에 띄게 빨라지는 것을 경험하게 될 것입니다.
2. 상대 열 개수를 셀 필요가 없음
VLOOKUP을 사용할 때 참조 테이블에서 찾으려는 키 값이 C열에 있고, 가져와야 할 데이터가 AQ열에 있다면 C열과 AQ열 사이에 몇 개의 열이 있는지 일일이 세어야 합니다.
반면 INDEX-MATCH 함수를 사용하면 데이터를 가져올 인덱스 열(예: AQ열)과 매칭할 열(예: C열)을 직접 선택하기만 하면 되므로 훨씬 직관적입니다.
3. 더 전문가처럼 보임
VLOOKUP은 요즘 매우 흔하게 사용되지만, INDEX-MATCH를 함께 조합해 사용하는 방법을 아는 사람은 많지 않습니다.
INDEX-MATCH 함수의 긴 수식은 복잡하고 고급스러운 엑셀 함수를 능숙하게 다루는 전문가로 보이게 해 줄 것입니다. 지금 바로 활용해 보세요!