엑셀 INDEX MATCH 사용법: VLOOKUP과 차이, 왼쪽 열 찾기, 가로·세로 동시 조회, 다중 조건
TUREFF• •0
VLOOKUP은 편하지만 찾는 값이 표의 맨 왼쪽에 있어야 하고, 열을 끼워 넣으면 결과가 틀어지는 약점이 있습니다. INDEX와 MATCH를 조합하면 이런 제약 없이 표 어디서든 값을 찾아올 수 있습니다. XLOOKUP이 없는 버전에서도 쓸 수 있어 실무에서 여전히 많이 씁니다.
1. 두 함수를 따로 이해하기
MATCH는 찾는 값이 범위의 몇 번째에 있는지 순서를 알려 줍니다.
=MATCH(찾을 값, 찾을 범위, 0)
마지막 인수 0은 정확히 일치를 뜻합니다. 실무에서는 거의 항상 0을 씁니다. 생략하면 근삿값 찾기가 되어 엉뚱한 결과가 나올 수 있습니다.
INDEX는 범위에서 몇 번째 칸의 값을 꺼내 줍니다.
=INDEX(가져올 범위, 행 번호)
두 함수를 합치면 "MATCH로 위치를 찾고, INDEX로 그 위치의 값을 꺼낸다"가 됩니다.
2. 기본 공식
A열에 사번, B열에 이름, C열에 부서가 있고, F2에 입력한 사번의 부서를 찾는다고 해 보겠습니다.
=INDEX(C2:C100, MATCH(F2, A2:A100, 0))
- MATCH가 A2:A100에서 F2가 몇 번째인지 찾습니다.
- INDEX가 C2:C100에서 그 번째 칸의 값을 돌려줍니다.
두 범위의 시작 행과 크기를 똑같이 맞추는 것이 핵심입니다. 한쪽은 2행부터, 다른 쪽은 1행부터 시작하면 한 칸씩 밀린 값이 나옵니다.
수식을 아래로 복사할 거라면 범위를 절대참조로 고정합니다.
=INDEX($C$2:$C$100, MATCH(F2, $A$2:$A$100, 0))
3. VLOOKUP과 비교한 장점
- 왼쪽 열도 찾을 수 있다: VLOOKUP은 찾는 열 오른쪽만 가져오지만, INDEX MATCH는 가져올 범위와 찾을 범위를 따로 지정하므로 방향 제한이 없습니다.
- 열을 끼워 넣어도 안 깨진다: VLOOKUP의 열 번호는 숫자라서 중간에 열을 추가하면 다른 열을 가져옵니다. INDEX MATCH는 범위를 직접 가리켜 자동으로 따라갑니다.
- 필요한 열만 참조한다: 넓은 표 전체가 아니라 두 열만 참조하니 큰 파일에서 계산 부담이 줄어드는 경우가 있습니다.
4. 왼쪽 열 찾기 예시
B열의 이름으로 A열의 사번을 찾아야 하는 경우입니다. VLOOKUP으로는 열 순서를 바꾸지 않는 한 불가능하지만 INDEX MATCH로는 그대로 됩니다.
=INDEX(A2:A100, MATCH(F2, B2:B100, 0))
5. 가로·세로 동시 조회(2차원 조회)
월별 지점 매출표처럼 행 머리글(지점)과 열 머리글(월)이 있는 표에서 교차점 값을 찾을 때 INDEX MATCH가 가장 빛납니다. A2:A20에 지점명, B1:M1에 월, B2:M20에 매출이 있다고 하면:
=INDEX(B2:M20, MATCH(H2, A2:A20, 0), MATCH(H3, B1:M1, 0))
- 첫 번째 MATCH는 지점이 몇 번째 행인지 찾습니다.
- 두 번째 MATCH는 월이 몇 번째 열인지 찾습니다.
- INDEX가 그 행과 열이 만나는 값을 꺼냅니다.
H2와 H3에 드롭다운 목록을 만들어 두면 지점과 월만 골라 바로 값을 보는 조회표가 됩니다.
6. 조건이 두 개 이상일 때
이름이 같은 직원이 여러 부서에 있다면 이름만으로는 정확한 값을 찾을 수 없습니다. 가장 쉬운 방법은 도우미 열입니다.
- 표 맨 왼쪽에 새 열을 만들고 =B2&"|"&C2 처럼 조건이 되는 열을 이어 붙입니다.
- 찾을 때도 같은 방식으로 이어 붙인 값을 MATCH에 넣습니다.
=INDEX(D2:D100, MATCH(G2&"|"&H2, A2:A100, 0))
가운데 구분자(|)를 넣는 이유는 "12"와 "3", "1"과 "23"처럼 이어 붙였을 때 같아지는 경우를 막기 위해서입니다.
Microsoft 365나 엑셀 2021 이상이라면 도우미 열 없이 범위끼리 이어 붙여 바로 찾을 수도 있습니다.
=INDEX(D2:D100, MATCH(G2&"|"&H2, B2:B100&"|"&C2:C100, 0))
이전 버전에서는 이 수식을 Ctrl+Shift+Enter로 입력해야 배열 수식으로 동작합니다. 헷갈린다면 도우미 열 방식이 가장 확실합니다.
7. 오류 처리
찾는 값이 없으면 #N/A가 나옵니다. 보고서에 오류가 그대로 보이면 지저분하니 감싸 줍니다.
=IFERROR(INDEX(C2:C100, MATCH(F2, A2:A100, 0)), "없음")
다만 IFERROR는 범위를 잘못 잡은 실수까지 모두 숨깁니다. 값이 없을 때만 처리하고 싶다면 IFNA를 쓰는 편이 안전합니다.
값이 분명히 있는데 #N/A가 나온다면 대부분 아래 셋 중 하나입니다.
- 앞뒤에 공백이 붙어 있다: TRIM으로 정리합니다.
- 한쪽은 숫자, 한쪽은 텍스트로 저장된 숫자다: 사번이 왼쪽 정렬되어 있다면 텍스트일 가능성이 큽니다.
- MATCH의 마지막 인수 0을 빠뜨렸다.
8. XLOOKUP을 쓸 수 있다면
Microsoft 365와 엑셀 2021 이상에는 XLOOKUP과 XMATCH가 있어 같은 일을 더 짧게 할 수 있습니다. 하지만 이전 버전 사용자와 파일을 주고받는다면 INDEX MATCH가 가장 호환성이 좋습니다.
INDEX MATCH로 해결한 실무 사례나 더 깔끔하게 쓰는 요령이 있다면 댓글로 공유해 주세요. 막히는 수식은 표 구조와 함께 올려 주시면 같이 풀어 보겠습니다.