INDEX MATCH 함수로 VLOOKUP 대신 찾기, 왼쪽도 될까?

엑셀로 사원번호에 맞는 이름을 찾거나 상품코드로 가격을 찾을 때 VLOOKUP을 쓰다가 중간에 열을 하나 삽입하는 순간 결과가 전부 틀어진 경험, 사무직이라면 한 번쯤 겪어봤을 것이다.

결론부터 말하면 INDEX MATCH 함수를 조합하면 VLOOKUP과 똑같이 값을 찾으면서도 열 순서가 바뀌어도 깨지지 않고, 찾는 값의 왼쪽 열도 그대로 가져올 수 있다. 수식은 =INDEX(반환할범위, MATCH(찾을값, 찾을범위, 0)) 한 줄이면 끝난다.

예를 들어 사원번호 300여 건이 들어있는 명단에서 B열에 ‘부서’ 열을 하나 끼워 넣었더니, 기존에 쓰던 VLOOKUP 수식의 네 번째 인수였던 열 번호 3이 전부 어긋나면서 엉뚱한 이름이 줄줄이 표시된 적이 있다.

열 번호를 숫자로 고정하는 VLOOKUP 구조 자체의 한계라서, 열이 바뀔 때마다 수식을 일일이 손으로 고쳐야 했다.

엑셀로 명단이나 재고 목록을 관리하면서 VLOOKUP의 열 번호 세기가 귀찮거나, 찾는 값의 왼쪽 열을 가져오려다 막혔던 직장인을 기준으로 정리했다. 실제로 수식을 입력하는 순서와 자주 막히는 지점까지 함께 써봤다.

INDEX MATCH 함수로 VLOOKUP 대신 값을 어떻게 찾나요

MATCH로 위치를 찾고 INDEX로 그 위치의 값을 꺼내는 두 단계로 생각하면 된다. 아래 순서대로 따라 하면 VLOOKUP 없이도 원하는 값을 찾을 수 있다.

실습에 쓰인 표는 다음과 같다. A열에 사원번호, B열에 부서, C열에 이름이 들어 있고, F2 셀에 찾고 싶은 사원번호를 입력하면 같은 행의 이름을 C열에서 가져오는 구조다.

1. 1단계. 값을 가져올 셀을 선택하고 수식 입력줄에 =INDEX(까지 입력한다.
2. 2단계. 결과로 가져올 열 범위를 지정한다. 이름이 있는 C2:C100 범위를 선택하면 된다.
3. 3단계. 쉼표를 찍고 MATCH(를 이어서 입력한다.
4. 4단계. 찾을 값(F2 셀의 사원번호)을 지정하고, 쉼표 뒤 찾을 범위(A2:A100)를 선택한다.
5. 5단계. 마지막 인수로 0을 입력해 정확히 일치하는 값만 찾도록 한다. 전체 수식은 =INDEX(C2:C100, MATCH(F2, A2:A100, 0)) 형태가 된다.
6. 6단계. Enter를 눌러 수식을 완성하고, 채우기 핸들로 아래 행까지 복사한다.

범위를 매번 드래그하기 번거롭다면 A2:A100을 ‘사원번호’, C2:C100을 ‘이름’으로 미리 이름정의(수식 탭 > 이름 정의)해두면 수식이 =INDEX(이름, MATCH(F2, 사원번호, 0))처럼 훨씬 읽기 쉬워진다.

명단이 자주 늘어난다면 범위를 표(Ctrl+T)로 바꿔 자동으로 확장되게 해두는 것도 좋다.

직접 사원 명단에 적용해보니 INDEX 범위와 MATCH 범위의 행 개수가 다르면 일부 행에서만 #REF! 오류가 나서 원인을 찾느라 한참 걸렸다. 두 범위는 반드시 같은 행 수로 맞춰야 한다.

수식을 직접 타이핑하기 번거로우면 수식 탭 > 함수 라이브러리 > 찾기/참조 영역에서 INDEX와 MATCH를 각각 선택해 함수 인수 대화상자로 채워 넣어도 된다. 셀을 선택한 뒤 Shift+F3을 누르면 바로 함수 마법사가 열린다.

인수 설명은 마이크로소프트 공식 지원 문서에서도 확인할 수 있다.

VLOOKUP보다 INDEX MATCH가 더 편한 이유는 뭔가요

열이 추가되거나 찾는 값이 왼쪽에 있어도 결과가 깨지지 않는다는 점이 가장 큰 차이다.

가령 C열에 상품코드, A열에 원가가 있는 표에서 상품코드로 원가를 찾으려 하면 VLOOKUP은 범위를 반드시 상품코드가 있는 열부터 지정해야 해서 애초에 불가능하다.

반면 INDEX MATCH 함수는 =INDEX(A2:A100, MATCH(F2, C2:C100, 0))처럼 범위만 바꿔 지정하면 왼쪽 열 값을 그대로 가져온다.

특히 이름으로 먼저 찾아서 왼쪽 열인 부서코드를 가져와야 하는 작업은 VLOOKUP 구조로는 아예 불가능해서 포기했었는데, INDEX MATCH 함수로는 범위만 바꿔서 그대로 처리할 수 있었다.

INDEX MATCH 함수가 안 될 때는 뭘 확인해야 하나요

오류 메시지 종류에 따라 원인이 거의 정해져 있어서 표만 보고도 바로 찾을 수 있다.

• 범위가 수천 행을 넘어가면 배열 수식(Ctrl+Shift+Enter)은 계산이 느려질 수 있어, 가능하면 표 서식과 구조적 참조를 함께 쓰는 편이 낫다.
• 찾을 값 셀의 숫자가 텍스트로 저장돼 있으면 #N/A가 나므로, VALUE 함수나 서식 변경으로 숫자 형식을 통일해야 한다.
• MATCH의 세 번째 인수를 0이 아니라 1이나 생략한 채 두면 정확히 같은 값이 아니라 비슷한 값을 찾아버려 의도와 다른 결과가 나올 수 있다.

실제로 가장 많이 겪은 실수는 범위를 절대참조로 고정하지 않아서 수식을 아래로 복사할 때마다 참조 범위가 한 칸씩 밀리는 경우였다. 범위를 지정한 뒤 F4를 눌러 $A$2:$A$100 형태로 고정하면 바로 해결된다.

INDEX MATCH 말고 또 알아두면 좋은 함수가 있나요

Microsoft 365나 Excel 2021 이상이라면 XLOOKUP 함수 하나로 같은 결과를 더 짧게 쓸 수 있다.

XLOOKUP은 =XLOOKUP(찾을값, 찾을범위, 반환범위) 형태로 인수가 더 적고, 찾는 값이 없을 때 대체 텍스트도 바로 지정할 수 있다.

다만 회사에서 Excel 2019 이하 버전으로 파일을 공유해야 한다면 XLOOKUP은 열리지 않으므로 이때는 INDEX MATCH 함수가 더 안전하다.

조건을 두 가지 이상 동시에 맞춰 찾아야 할 때는 MATCH 안에 배열 형태로 조건을 곱해 넣는 방식을 쓴다.

지점명과 월을 함께 맞춰 매출을 찾을 때는 =INDEX(매출범위, MATCH(1, (지점범위=F2)*(월범위=G2), 0))처럼 쓰고, 입력 후 Ctrl+Shift+Enter로 배열 수식을 확정해야 구버전에서도 제대로 동작한다.

지점과 월처럼 행과 열 조건을 모두 바꿔가며 찾아야 하는 교차표라면 MATCH를 두 번 써서 행 위치와 열 위치를 각각 찾은 뒤 INDEX에 두 값을 함께 넣으면 된다.

=INDEX(매출표, MATCH(F2, 지점목록, 0), MATCH(G2, 월목록, 0))처럼 표 전체를 범위로 지정하고 행 번호와 열 번호를 동시에 넘기는 구조다.

엑셀 자체가 실행되지 않거나 설치 과정에서 오류가 난다면 함수를 고칠 게 아니라 프로그램 설치 문제부터 봐야 한다. 윈도우 프로그램 설치 안됨 원인별 해결 순서를 먼저 확인하는 게 순서다.

회사 공용 PC에서 로그인 자체가 막힌다면 로그인 실패 오류 해결법도 참고할 만하다.

INDEX MATCH 함수 자주 묻는 질문

사원 300명 명단 기준으로 직접 비교해보니, 열 삽입 후 수식을 다시 손보는 데 걸리던 시간이 거의 사라졌다. 엑셀 수식 하나 바꾸는 것만으로 반복 작업 시간을 꽤 줄일 수 있다.

다음에는 여러 조건을 한 번에 찾는 배열 수식 활용법을 더 자세히 정리해볼 예정이다. 여러분은 VLOOKUP과 INDEX MATCH 중 어떤 걸 더 많이 쓰고 있나요.

출처: Editlab, https://editlab.luvpp.com

EDITLAB이 매체는 Editlab이 발행합니다분야별 전문 매체 8곳과 무료 데이터 도구를 함께 운영합니다.네트워크 보기 ›

글 Editlab 편집부 · 발행 전 자동 점검(중복·형식·규칙) · 정정 요청 · Google 검색에서 이 매체를 우선 출처로 등록