찾기/참조의 클래식, INDEX와 MATCH 함수 콤비네이션

VLOOKUP을 썼는데 #REF! 오류가 뜬다면?

안녕하세요. 여러분의 엑셀 러닝메이트 직장인 엑셀 꿀팁 저장소입니다!

지난 포스팅에서는 흩어진 텍스트를 깔끔하게 합쳐주는 TEXTJOIN 함수에 대해 알아봤습니다. 데이터 정리가 끝났으니, 이제 방대한 데이터베이스에서 원하는 값을 쏙쏙 뽑아오는 ‘참조(Lookup)’ 기술을 배울 차례입니다.

직장인 10명 중 9명은 데이터를 찾을 때 VLOOKUP 함수를 사용합니다. 물론 훌륭한 함수입니다. 하지만 실무를 하다 보면 치명적인 한계에 부딪히게 됩니다.

1. “찾으려는 기준값보다 왼쪽에 있는 데이터는 가져올 수 없다.”
2. “원본 데이터 사이에 열을 하나 삽입했더니 수식이 다 깨져버렸다.”

이 두 가지 문제 때문에 수식을 다 지우고 다시 짜거나, 원본 데이터 순서를 바꾸느라 고생한 경험이 있으실 겁니다. 오늘 소개해 드릴 엑셀 INDEX MATCH 함수 조합은 이 모든 문제를 완벽하게 해결합니다. 조금 어렵게 느껴질 수 있지만, 한 번 익혀두면 여러분의 엑셀 레벨이 ‘중수’에서 ‘고수’로 점프하는 가장 확실한 기술입니다.

기초 다지기: INDEX 함수는 ‘GPS 좌표’다

두 함수를 합치기 전에, 각각의 역할을 명확히 알아야 합니다. 먼저 INDEX 함수입니다.

이 녀석은 지도상의 좌표(GPS)와 같습니다. “이 범위 안에서 3번째 줄, 2번째 칸에 있는 값이 뭐야?”라고 물으면 그 위치에 있는 데이터를 뱉어냅니다.

=INDEX(범위, 행번호, [열번호])

• 범위(Array): 값을 찾을 전체 영역입니다.
• 행번호(Row_num): 위에서부터 몇 번째 줄인지 입력합니다.
• 열번호(Column_num): 왼쪽에서부터 몇 번째 칸인지 입력합니다. (범위가 1줄이면 생략 가능)

예를 들어 A1:A5에 [사과, 배, 포도, 귤, 감]이 있을 때, =INDEX(A1:A5, 3)을 입력하면 세 번째에 있는 ‘포도’가 나옵니다. 아주 단순하죠? INDEX는 스스로 데이터를 찾지 못합니다. 오직 “몇 번째”라는 숫자만 받아서 심부름을 할 뿐입니다.

기초 다지기: MATCH 함수는 ‘수색대’다

INDEX에게 “몇 번째”인지 숫자를 알려주기 위해서는, 누군가가 먼저 가서 그 위치를 찾아내야 합니다. 그 역할을 하는 것이 바로 MATCH 함수입니다.

이 녀석은 수색대와 같습니다. “이 범위에서 ‘포도’가 몇 번째에 있어?”라고 물으면 위치 번호(숫자)를 반환합니다.

=MATCH(찾을값, 찾을범위, 0)

• 찾을값(Lookup_value): 내가 찾고 싶은 키워드(예: “포도”)입니다.
• 찾을범위(Lookup_array): 검색할 명단입니다. (반드시 한 줄이어야 합니다)
• 0(Match_type): 정확히 일치하는 것을 찾으라는 옵션입니다. (무조건 0을 쓴다고 기억하세요)

예를 들어 =MATCH("포도", A1:A5, 0)을 입력하면, 포도가 세 번째에 있으므로 숫자 3을 반환합니다.

더 깊이 있는 원리가 궁금하다면 마이크로소프트 공식 INDEX 함수 가이드를 참고하세요.

최강의 조합: INDEX & MATCH 함수 합체하기

이제 두 함수를 합체시켜 보겠습니다. 원리는 간단합니다. MATCH가 위치(숫자)를 찾아오면, 그 숫자를 INDEX에 넣어서 실제 값을 가져오는 것입니다.

VLOOKUP과 비교하면 구조가 훨씬 유연합니다.

• VLOOKUP: =VLOOKUP(찾을값, 전체범위, 열번호, 0) → 전체 범위를 통째로 잡아야 하고, 열 번호를 직접 세야 함.
• INDEX MATCH: =INDEX(결과열, MATCH(찾을값, 기준열, 0)) → 결과열과 기준열이 따로 놀아도 됨.

실전 1단계: VLOOKUP이 못 하는 ‘왼쪽’ 데이터 가져오기

가장 흔한 상황입니다. B열에 ‘이름’, A열에 ‘사번’이 있습니다. 우리는 이름을 가지고 사번을 찾고 싶습니다. 기준(이름)보다 결과(사번)가 왼쪽에 있죠? VLOOKUP은 여기서 작동하지 않습니다.

하지만 INDEX MATCH는 가능합니다.

1. INDEX 작성: 먼저 내가 가져오고 싶은 건 ‘사번’이니, A열을 잡습니다.
   =INDEX(A:A, ...
2. MATCH 작성: ‘이름’이 몇 번째인지 B열에서 찾습니다.
   MATCH("김철수", B:B, 0)
3. 합체:
   =INDEX(A:A, MATCH("김철수", B:B, 0))

이렇게 하면 김철수가 B열의 5번째에 있다면, MATCH가 5를 주고, INDEX가 A열의 5번째 값(사번)을 가져오게 됩니다. 방향 따위는 중요하지 않습니다.

실전 2단계: 행과 열을 동시에 찾는 ‘매트릭스 조회’

실무에서는 조건이 하나가 아닐 때가 많습니다. 예를 들어 가로축에는 ‘월(Month)’, 세로축에는 ‘제품명’이 있는 표에서, “제품 A의 3월 매출”을 찾고 싶다면 어떻게 할까요?

VLOOKUP을 쓴다면 열 번호를 찾기 위해 또 다른 MATCH를 쓰거나 숫자를 직접 세야 합니다. 하지만 INDEX MATCH는 INDEX 함수의 [행번호], [열번호] 자리에 각각 MATCH를 넣어주면 끝입니다.

=INDEX( 전체표, MATCH(제품찾기), MATCH(월찾기) )

• 행 찾기: MATCH(C2, 제품열, 0) → 몇 번째 줄인지 찾음.
• 열 찾기: MATCH("2월", 월헤더, 0) → 몇 번째 칸인지 찾음.
• 최종 수식: =INDEX(F2:H4, MATCH(C2, E2:E4, 0), MATCH(C3, F1:H1, 0))

이것을 ‘2차원 조회(Two-way Lookup)’라고 부릅니다. 이 공식을 이해하는 순간, 여러분은 어떤 형태의 표에서도 원하는 값을 자유자재로 추출할 수 있습니다.

XLOOKUP이 나왔는데 이걸 배워야 하나요?

최신 엑셀(M365, 2021)에는 XLOOKUP이라는 괴물 같은 신규 함수가 나왔습니다. INDEX MATCH의 장점을 흡수하고 사용법은 더 쉬워졌죠.

그럼에도 불구하고 여러분은 INDEX MATCH를 반드시 배워야 합니다.

1. 호환성: 여러분이 만든 파일을 거래처나 상사가 열었는데, 그들의 엑셀이 2016 버전이나 2013 버전이라면? XLOOKUP은 #NAME? 오류를 뿜으며 작동하지 않습니다. INDEX MATCH는 엑셀 97 버전부터 존재했기에 어디서든 안전합니다.
2. 무거움: 데이터가 수십만 행이 넘어가는 초대용량 파일에서는 XLOOKUP보다 INDEX MATCH가 미세하게 더 가볍고 빠르다는 벤치마크 결과가 있습니다.

XLOOKUP이 주력 무기라면, INDEX MATCH는 언제 어디서나 믿고 쓸 수 있는 든든한 보조 무기인 셈입니다.

마무리하며

오늘은 엑셀 고수로 가는 관문, INDEX와 MATCH 함수 조합을 완벽하게 파헤쳐 봤습니다. 처음엔 괄호도 많고 복잡해 보이지만, “MATCH로 찾고(Where), INDEX로 가져온다(What)”는 원리만 기억하면 VLOOKUP보다 훨씬 직관적입니다.

다음 시간에는 밋밋한 엑셀 표를 신호등처럼 알록달록하게 만들어서 데이터의 트렌드를 한눈에 보여주는 조건부 서식으로 데이터 시각화하기 편으로 찾아오겠습니다. 여러분의 칼퇴를 엑셀 꿀팁 저장소가 응원합니다!