INDEX+MATCH 실무 완전정복: VLOOKUP보다 유연한 이유와 예제

지난 글에서 VLOOKUP과 XLOOKUP을 비교했지만, 현실에서는 이런 상황이 자주 나옵니다. “회사 엑셀 버전이 제각각이라 XLOOKUP이 안 된다”, “구글시트에서 같은 방식으로 쓰고 싶다”, “VLOOKUP은 열 삽입만 하면 망가진다” 같은 문제요.

이럴 때 가장 안정적인 해법이 INDEX + MATCH 조합입니다. 겉보기에는 어렵게 느껴지지만, 한 번 원리를 잡으면 왼쪽 조회, 열 삽입에 강함, 다중 조건 확장까지 실무에서 오래 살아남는 방식입니다.

이번 글에서는 “왜 INDEX+MATCH를 쓰는지”를 이유로만 말하지 않고, 바로 가져다 쓸 수 있게 예제 중심으로 정리하겠습니다.

1. INDEX와 MATCH 역할을 한 문장으로 이해하기

• MATCH: “어디에 있나?” → 검색값이 있는 행 번호(위치)를 찾는다
• INDEX: “그 위치의 값이 뭐냐?” → 해당 위치의 값을 꺼낸다

즉, MATCH로 위치를 찾고, INDEX로 값을 뽑는다가 전부입니다.

2. 기본 문법(가장 많이 쓰는 형태)

예시 상황: A열에 상품코드, B열에 상품명, C열에 단가가 있는 “마스터 표”에서 현재 시트의 A2(상품코드)로 상품명을 가져오고 싶다.

2-1) 상품명 조회(정확 일치)

=INDEX(반환범위, MATCH(검색값, 검색범위, 0))

• 예시 수식: =INDEX(Sheet2!$B$2:$B$1000, MATCH(A2, Sheet2!$A$2:$A$1000, 0))

마지막 인수 0은 “정확히 일치”를 의미합니다. 코드/사번/ID 조회는 거의 항상 0(정확 일치)로 고정하세요.

2-2) 단가 조회

• =INDEX(Sheet2!$C$2:$C$1000, MATCH(A2, Sheet2!$A$2:$A$1000, 0))

3. INDEX+MATCH가 VLOOKUP보다 유연한 이유 4가지

1. 왼쪽 조회가 가능: VLOOKUP은 “검색열이 가장 왼쪽”이어야 하지만 INDEX+MATCH는 제한이 없습니다.
2. 열 삽입에 깨지지 않음: VLOOKUP은 열번호(2,3,4...)가 바뀌면 결과가 틀어지지만, INDEX+MATCH는 반환범위를 직접 지정하니 안전합니다.
3. 가독성이 좋아진다(표준화하면): “검색범위/반환범위”를 명확히 분리해두면 유지보수가 쉬워집니다.
4. 다중 조건으로 확장 가능: 부서+이름, 상품코드+옵션 같은 복합 키에도 대응할 수 있습니다.

4. 실무 예제 1) ‘왼쪽 값’ 가져오기(역조회)

예: 마스터에서 B열(상품명)로 A열(상품코드)을 찾고 싶다. VLOOKUP으로는 원칙적으로 불가능하지만, INDEX+MATCH는 가능합니다.

• 예시: =INDEX(Sheet2!$A$2:$A$1000, MATCH(B2, Sheet2!$B$2:$B$1000, 0))

5. 실무 예제 2) 조회값이 없을 때(#N/A) 처리

조회값이 없으면 MATCH가 #N/A를 반환합니다. 실무에서는 오류 대신 빈칸 또는 “확인 필요” 같은 문구가 더 안전합니다.

5-1) (엑셀/구글시트 공통) IFERROR로 감싸기

• =IFERROR(INDEX(Sheet2!$B$2:$B$1000, MATCH(A2, Sheet2!$A$2:$A$1000, 0)),"")

5-2) 팀 운영 팁

• 빈칸: 보고서용(보기 깔끔)
• “확인 필요”: 운영용(누락을 놓치지 않음)

6. 실무 예제 3) “중복 키”가 있을 때의 기준

조회는 기본적으로 첫 번째로 매칭되는 값을 가져옵니다. 키가 중복이라면, 어떤 값을 가져와야 하는지 기준이 필요합니다.

• 가능하면 마스터에서 키를 유일하게 관리(가장 권장)
• 중복이 불가피하면 “추가 조건(옵션/날짜/버전)”으로 키를 확장

다음 섹션의 다중 조건 예제가 바로 그 확장 방식입니다.

7. 실무 예제 4) 다중 조건 조회(구글시트/엑셀 공통 아이디어)

예: 상품코드(A2)와 옵션(B2)이 모두 같은 행의 단가를 가져오고 싶다. 이럴 때는 “두 조건이 모두 맞는 행”을 찾아야 합니다.

7-1) 방법 A: 보조열(결합 키) 만들기(가장 안정적)

마스터 표에 “코드&옵션”을 합친 키 열을 추가해두면, 조회는 단일 조건처럼 쉬워집니다.

• 마스터의 결합키 (예: D열): =A2 & "|" & B2
• 현재 시트에서 조회: =IFERROR(INDEX(Sheet2!$C$2:$C$1000, MATCH(A2 & "|" & B2, Sheet2!$D$2:$D$1000, 0)),"")

구분자 |는 데이터에 잘 안 들어가는 문자라 충돌이 적습니다.

7-2) 방법 B: (엑셀 고급) 조건 배열로 MATCH (환경에 따라 제한)

엑셀 최신 환경에서는 조건 배열로도 가능하지만, 조직/버전/공유를 고려하면 보조열 방식이 가장 안전합니다.

8. 성능/유지보수 팁(실무에서 오래 쓰려면)

• 전체열(A:A, B:B) 남발 줄이기: 행이 매우 많아지면 속도가 느려질 수 있습니다. 가능한 적당한 범위(예: 2:5000)로 잡거나 표(Table)를 활용하세요.
• 마스터는 테이블 구조로 관리: 빈 행/소계/병합이 섞이면 조회가 흔들립니다.
• 키 열 표준화: 앞뒤 공백(TRIM), 텍스트/숫자 타입 불일치가 #N/A의 주범입니다.
• 오류는 숨기지 말고 정책을 정하기: 빈칸으로 둘지, “확인 필요”로 둘지 팀 기준을 정해두면 관리가 쉬워집니다.

9. 한 번에 정리: 상황별 추천 조합

• 엑셀 최신: XLOOKUP 우선 + 필요 시 INDEX+MATCH
• 버전 혼용/구글시트 공유: INDEX+MATCH(가장 호환성 좋음)
• 열이 자주 바뀌는 마스터: INDEX+MATCH 또는 XLOOKUP
• 다중 조건: 보조열 결합 키 + INDEX+MATCH

마무리

INDEX+MATCH는 “어려운 고급기술”이라기보다, 실무에서 파일이 바뀌어도 오래 버티는 안정적인 조회 습관입니다. 오늘 정리한 기본 공식 한 줄만 확실히 기억하세요. MATCH로 위치를 찾고, INDEX로 값을 뽑는다.