INDEX MATCH 쉽게 쓰는 법: 위치 확인부터 오류 복구까지

INDEX MATCH 쉽게 쓰는 법을 MATCH 위치 확인, INDEX 결과 검산, #N/A 오류 복구 순서로 나눠 설명합니다. 6행 가상 데이터와 연습 파일로 중간값과 최종값을 직접 비교할 수 있습니다.

수식이 다른 제품의 값을 가져오면 전체를 다시 쓰기보다 MATCH의 위치부터 확인하세요. 위치가 맞으면 INDEX의 반환 범위를 살피고, 두 단계가 모두 맞을 때 한 줄로 합칩니다.

완성 목표는 분명합니다. 조회!B2에 P-004를 입력했을 때 MATCH 결과가 4, 단가가 15,800, 제품명이 USB 허브로 나오면 통과입니다. 예제의 제품명과 금액은 함수 연습을 위해 만든 가상 데이터입니다.

사용 전제와 준비 환경

새 통합 문서에서 따라 할 때는 상품 시트와 조회 시트를 만듭니다. 첨부 파일에는 두 시트가 이미 본문과 같은 주소로 준비되어 있으며, 검산, 범위오류, README가 추가로 있습니다. 매크로와 외부 연결은 없고 시트 잠금도 없으므로 사본에서 입력칸만 바꿉니다. 저장된 미리보기나 설명용 그림은 실제 Excel 실행 증빙이 아닙니다. 이 실습에는 셀에 수식을 입력하고 시트 이름을 바꿀 수 있는 Excel 환경이 필요합니다. 함수 이름은 영문으로 입력하고, 수식의 범위는 아래 예제와 같은 2행부터 7행까지만 사용합니다. 전체 열 참조를 피하면 어떤 여섯 행을 검산하는지 분명해집니다.

설명용 이미지이며 실제 Excel 실행 결과 화면이 아닙니다.

주제 개요와 전체 흐름

준비할 셀은 다음과 같습니다.

• 상품!A1:D7: 제품 코드, 제품명, 분류, 단가가 있는 원본 표

• 조회!B2: 찾을 제품 코드를 입력하는 셀

• 조회!B3: MATCH가 반환한 상대 위치

• 조회!B4: INDEX가 반환한 단가

• 조회!B5: INDEX와 MATCH를 한 줄로 합친 단가

• 조회!B6: 같은 위치를 이용해 가져온 제품명

완료 판정은 B3=4, B4=15800, B5=15800, B6=USB 허브 네 가지가 동시에 맞는지 확인하는 방식입니다. 단순히 오류 표시가 없다는 이유만으로 완료 처리하지 않습니다.

INDEX MATCH를 역순으로 이해하는 핵심

MATCH는 값을 직접 가져오는 함수가 아니라, 찾은 항목이 지정 범위에서 몇 번째인지 반환합니다. Microsoft의 MATCH 함수 문서도 MATCH가 검색 범위 안의 상대 위치를 반환한다고 설명합니다.

설명용 이미지이며 실제 Excel 실행 결과 화면이 아닙니다.

단계별 실행 절차

INDEX는 범위와 위치 번호를 받아 그 자리에 있는 값을 반환합니다. INDEX 함수 문서의 배열형 구문에서 array와 row_num의 관계를 확인할 수 있습니다.

따라서 한 줄 수식을 바로 외우기보다 아래 순서로 조립하면 됩니다.

1. MATCH로 제품 코드의 상대 위치를 찾습니다.

2. INDEX에 단가 범위와 위치 번호를 넣습니다.

3. 확인이 끝나면 INDEX의 위치 번호 자리에 MATCH 수식을 끼웁니다.

가상 제품 데이터 6행 입력하기

상품 시트 A1부터 아래 표를 입력합니다. 금액은 함수 결과를 검산하기 위한 가상 값이며 실제 가격 정보가 아닙니다.

설명용 이미지이며 실제 Excel 실행 결과 화면이 아닙니다.

점검 체크리스트

조회!A2에는 조회 코드, B2에는 P-004를 입력합니다. 이 예제에서 P-004는 원본 범위 A2:A7의 네 번째 항목입니다. 워크시트 행 번호 5와 MATCH 결과 4를 혼동하지 않는 것이 첫 번째 검산 포인트입니다.

1단계: MATCH만 실행해 위치 4 확인하기

조회!A3에 상대 위치, B3에 다음 수식을 입력합니다.

=MATCH(B2,상품!$A$2:$A$7,0)

마지막 인수 0은 정확히 같은 제품 코드를 찾겠다는 뜻입니다. 결과가 4이면 MATCH 단계는 통과입니다. P-004가 상품 시트 5행에 있지만 검색 범위는 2행에서 시작하므로, 범위 안에서는 네 번째라는 점을 확인합니다.

결과가 #N/A라면 INDEX를 붙이지 않습니다. 먼저 B2의 코드와 상품 시트의 코드가 완전히 같은지 확인하고, =COUNTIF(상품!$A$2:$A$7,B2)가 1인지 검사합니다.

2단계: INDEX에 위치 셀을 넣어 단가 확인하기

조회!A4에 단가, B4에 다음 수식을 입력합니다.

=INDEX(상품!$D$2:$D$7,B3)

INDEX의 대상 범위는 단가가 있는 D2:D7이고, 위치 번호는 앞에서 검산한 B3입니다. B3이 4이므로 D2:D7의 네 번째 값인 15800이 나와야 합니다.

이 단계에서 #REF!가 나오면 INDEX 범위의 시작 행과 끝 행을 확인합니다. MATCH는 6개 항목을 찾는데 INDEX 범위를 D2:D6처럼 5개만 지정하면 위치 6을 처리할 수 없습니다. 두 범위를 모두 2행부터 7행까지 맞춘 뒤 다시 계산합니다.

3단계: 두 함수를 한 줄로 결합하기

B3과 B4가 각각 4, 15800으로 확인됐다면 조회!A5에 결합 단가, B5에 다음 수식을 입력합니다.

=INDEX(상품!$D$2:$D$7,MATCH(B2,상품!$A$2:$A$7,0))

안쪽 MATCH가 위치 4를 만들고, 바깥 INDEX가 단가 범위의 네 번째 값을 가져옵니다. B4와 B5가 모두 15800이면 단독 수식과 결합 수식이 같은 결과를 냈으므로 통과입니다.

수식을 읽을 때는 괄호 안쪽부터 확인합니다. MATCH(B2,상품!$A$2:$A$7,0)을 먼저 읽고, 그 결과가 INDEX(상품!$D$2:$D$7,4)에 들어간다고 바꾸어 쓰면 구조를 놓치지 않습니다.

4단계: 같은 위치로 제품명 가져오기

위치 찾기 범위는 그대로 두고 INDEX의 반환 범위만 바꾸면 같은 행의 다른 값을 가져올 수 있습니다. 조회!A6에 제품명, B6에 다음 수식을 입력합니다.

=INDEX(상품!$B$2:$B$7,MATCH(B2,상품!$A$2:$A$7,0))

P-004의 제품명인 USB 허브가 나오면 통과입니다. 단가 수식과 비교하면 MATCH 부분은 같고 INDEX 범위만 D2:D7에서 B2:B7로 바뀌었습니다.

완성 예제의 입력·중간값·출력

아래 표의 네 결과가 동시에 맞아야 예제가 완성됩니다.

중간값 B3을 남겨 두는 이유는 오류 위치를 분리하기 위해서입니다. B3이 틀리면 MATCH의 입력값·검색 범위·일치 유형을 보고, B3은 맞지만 B4가 틀리면 INDEX의 반환 범위를 봅니다.

다른 코드로 재검산하기

한 번 맞은 수식만 보고 끝내지 말고 입력 코드를 바꿔 같은 구조가 유지되는지 확인합니다.

1. 조회!B2를 P-006으로 바꿉니다.

2. B3이 6인지 확인합니다.

3. B4와 B5가 모두 9900인지 확인합니다.

4. B6이 HDMI 케이블인지 확인합니다.

5. 조회!B2를 P-999로 바꾸고 B3이 #N/A가 되는지 확인합니다.

P-999는 가상 원본 표에 없는 코드이므로 조회 B3:B6의 #N/A가 모두 예상된 미발견 결과입니다. 첨부 검산 시트의 P-999 행도 오류를 숨기지 않고 남겨 두었습니다. 없는 값을 임의의 단가로 바꾸지 않는다는 점까지 확인해야 조회 오류를 숨기지 않습니다.

오류별 원인과 복구 절차

MATCH의 정확 일치는 같은 값이 여러 개면 첫 번째 상대 위치를 반환합니다. 이 동작은 MATCH 함수 문서에서 확인할 수 있습니다. 중복 코드가 허용되는 데이터라면 이 예제의 단일 키 조회를 그대로 사용하지 말고, 먼저 고유키 설계를 정해야 합니다.

범위 오류를 사본에서 재현하고 복구하기

정상 입력 P-004에서 조회 B4의 반환 범위만 상품!$D$3:$D$8로 한 행 밀어 봅니다. MATCH 위치는 여전히 4지만 INDEX가 읽는 셀은 D6이므로 가상 오류값 35000이 나옵니다. 오류 표시가 없다는 것과 맞는 단가라는 것은 다릅니다. B4의 반환 범위를 D2:D7로 복원하면 15800이 다시 기대값입니다.

끝 행 오류는 P-006으로 구분합니다. 조회 B2를 P-006으로 바꾸고 B4의 INDEX 범위를 D2:D6으로 줄이면 여섯 번째 위치를 다섯 칸에서 찾으므로 #REF!가 예상됩니다. D2:D7로 복원한 뒤 9900을 확인하고, 마지막에는 B2를 P-004로 되돌립니다.

첨부 범위오류 시트는 이 두 오류와 정상 수식을 나란히 둔 비교표입니다. P-004 행은 밀린 범위 35000·짧은 범위 15800·정상 15800, P-006 행은 밀린 범위 0·짧은 범위 #REF!·정상 9900이 가상 기대값입니다. 여기서 0은 밀린 범위가 비어 있는 상품 D8을 가리킨 결과이며 실제 단가가 0이라는 뜻이 아닙니다. 이 시트의 오류는 교육용으로 남겨 둔 것이므로 기대값을 바꿔 숨기지 않습니다.

미발견과 형식·공백 문제를 구분하는 일반적인 점검 순서는 Microsoft의 #N/A 오류 안내도 참고할 수 있습니다. 이 글의 실패 사례는 독자가 사본에서 실행해 비교할 예제이며 실제 실행 완료 기록은 아닙니다.

결과 검증 체크리스트

검증 대상과 합격 값을 분리해 기록하면 재실행 때도 같은 결과를 확인할 수 있습니다.

• 원본 행 수: =COUNTA(상품!A2:A7) 결과가 6

• P-004 존재 수: =COUNTIF(상품!A2:A7,"P-004") 결과가 1

• P-004 위치: =MATCH("P-004",상품!A2:A7,0) 결과가 4

• P-004 단가: 결합 수식 결과가 15800

• P-006 단가: 입력 변경 후 결과가 9900

• 없는 코드: P-999 입력 시 #N/A

위 여섯 항목 중 하나라도 다르면 발행된 수식을 그대로 복사해 쓰기보다 해당 단계의 범위와 입력값부터 고칩니다. 특히 위치 4와 워크시트 행 5는 서로 다른 개념입니다.

연습 파일을 사용하는 순서

첨부 파일 상태 확인(2026-10-02): 현재 배포된 XLSX의 조회 시트 B3:B8 등 일부 셀에는 저장된 계산값으로 #NAME?가 남아 있습니다. 파일 내부값을 확인한 사실이며, Microsoft Excel에서 다시 계산한 결과를 뜻하지는 않습니다. 미리보기만으로 실습 성공 여부를 판단하지 마세요. 본문의 상품 여섯 행과 수식을 새 사본에 직접 입력해 위치 4·단가 15800·제품명 USB 허브를 확인하는 방법도 있습니다. 첨부를 열었다면 다시 계산한 뒤 이 기대값과 대조하고, 오류가 계속되면 시트 이름·함수 인식·참조 범위를 확인하세요. 실제 업무에 바로 적용하지 말고 검증용 사본에서만 사용하세요.

첨부된 연습 파일은 먼저 원본 표를 확인하고, MATCH의 중간 결과를 검산한 다음 INDEX 수식을 결합하는 순서로 사용합니다. 파일을 열었을 때 수식 결과가 바로 계산되지 않으면 통합 문서의 수식 셀을 선택해 다시 계산한 뒤, 이 글의 합격 값과 비교합니다.

파일을 수정할 때는 조회 코드를 P-004와 P-006으로 번갈아 입력해 위치와 반환값이 함께 바뀌는지 확인합니다. 단순히 예제 파일을 내려받는 데서 끝내지 않고, 입력 두 개와 없는 코드 하나까지 시험해야 연습이 완료됩니다.

이 방식의 한계

MATCH는 텍스트의 대소문자를 구분하지 않으며, 정확 일치 0에서도 문자 조회값의 별표와 물음표는 와일드카드로 사용할 수 있습니다. 이 예제는 P-001 형태의 고정 문자코드만 사용합니다. 대소문자를 구분해야 하거나 특수문자 자체가 키인 데이터에는 같은 일치 규칙을 그대로 적용하지 않습니다. 이 범위는 Microsoft MATCH 설명을 기준으로 합니다.

이 실습은 한 개의 고유한 제품 코드로 한 행을 찾는 상황에 맞습니다. 여러 조건을 동시에 사용하거나 중복 행을 모두 반환해야 하는 작업은 별도의 배열 수식이나 데이터 구조가 필요합니다. MATCH의 중간값을 남기는 방식은 학습과 오류 진단에는 유용하지만, 최종 업무 시트에서는 필요한 검산 셀만 유지하면 됩니다.

관련 자료와 다음 행동

이 글의 고유 범위는 MATCH 중간 위치와 INDEX 단독 결과를 서로 다른 셀에서 검산하고 P-004, P-006, P-999로 오류 위치를 나누는 실습입니다.

왼쪽 방향 조회에서 VLOOKUP과 결과를 나란히 비교하려면 6행 직원표로 INDEX·MATCH와 XLOOKUP 검산하기를 참고할 수 있습니다. 세 함수의 선택 기준을 한 번에 보고 싶다면 XLOOKUP 전환 전 VLOOKUP 결과와 비교하기로 이어가면 됩니다.

공식 문서와 확인 범위

이 글의 함수 역할과 구문은 Microsoft의 INDEX 함수와 MATCH 함수 문서를 기준으로 정리했습니다. 제품표의 이름·분류·금액과 P-004, P-006 결과는 문서 사용법을 설명하기 위해 이 글에서 만든 가상 예제입니다. 실제 파일에 적용할 때는 자신의 원본 범위와 고유키 조건으로 다시 검산해야 합니다.

첨부 검산 시트에는 P-004·P-006·P-999의 현재 수식 결과와 고정 기대값이 분리되어 있습니다. 상품 원본과 기대값을 보존하고 조회 입력만 바꿉니다. 공식 문서 확인일은 2026-09-12이며 가상 단가는 실제 가격 정보가 아닙니다.

작성자 소개

최지원

엑셀·VBA·파이썬·AI 업무 자동화의 사용 절차와 실습 자료를 정리합니다.

WP 게시물 작성자의 글 보기