Excel INDEX MATCH: 수식, 예제 및 일반적인 오류

By Mirko Peters · 2026-11-12

Excel의 INDEX MATCH를 자세히 설명합니다. 수식, 각 함수의 작동 방식, 왼쪽 및 두 방향 조회, 여러 조건, 일반적인 오류를 다루며 VLOOKUP과의 비교도 함께 제공합니다.

Automatically translated with AI from the original post. Read the English original

Excel의 INDEX MATCH는 두 함수로 구성된 조회 수식입니다: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). MATCH는 값이 목록의 어디에 있는지 찾고, INDEX는 다른 목록에서 같은 위치에 있는 항목을 반환합니다. 이 글은 VLOOKUP 수식을 작성하다가 한계에 부딪혔거나, INDEX MATCH가 가득한 통합 문서를 물려받아 이해하고 싶은 Excel 사용자를 위한 것입니다. 각 함수를 개별적으로 살펴보고, 두 함수가 어떻게 결합되는지, 왼쪽을 조회하는 방법, 두 방향 조회, 두 개의 조건 사용법, 그리고 오류의 의미를 다룹니다.

핵심 포인트

• INDEX MATCH는 한 수식에 담긴 두 함수입니다: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). MATCH는 위치를 찾고, INDEX는 그 위치에 있는 항목을 반환합니다.
• 정확히 일치시키려면 MATCH의 세 번째 인수로 0을 사용합니다. 기본값인 1로 두면 MATCH는 데이터가 오름차순으로 정렬되어 있다고 가정합니다.
• 조회 범위와 반환 범위가 분리되어 있으므로 INDEX MATCH는 조회 열의 왼쪽을 조회할 수 있습니다. VLOOKUP으로는 할 수 없는 작업입니다.
• 두 가지 조건을 사용하려면 도우미 열에서 열을 결합하거나, MATCH 내부에서 두 비교 결과를 곱하는 수식을 사용하세요.
• 수식을 IFNA로 감싸면 #N/A만 메시지로 바꾸고 다른 오류는 그대로 표시되므로, 실제 실수가 가려지지 않습니다.

INDEX MATCH는 XLOOKUP이 나오기 훨씬 전부터 모든 버전의 Excel에서 작동했기 때문에 많은 이전 통합 문서에서 찾아볼 수 있습니다. 지금도 그렇기 때문에 이 함수를 배워야 하는 가장 큰 이유입니다. 아래 세부 내용은 Microsoft의 MATCH, INDEX 및 VLOOKUP 문서를 바탕으로 합니다.

INDEX의 역할

INDEX는 범위 내 위치에 있는 값을 반환합니다. Microsoft 문서는 배열 형식을 INDEX(array, row_num, [column_num])로 제시합니다.

• array는 값을 가져올 범위입니다.
• row_num은 그 범위 안의 행입니다.
• column_num은 그 범위 안의 열입니다. 범위가 열 하나뿐이라면 생략할 수 있습니다.

번호는 시트가 아니라 범위의 왼쪽 위 셀부터 셉니다. Microsoft 예제는 첫 번째 열에 Apples와 Bananas, 두 번째 열에 Lemons와 Pears가 있는 블록을 사용합니다. =INDEX(A2:B3,2,2)는 Pears를, =INDEX(A2:B3,2,1)은 Bananas를 반환합니다.

같은 페이지에 있는 두 가지 규칙을 기억해 둘 가치가 있습니다. row_num 또는 column_num이 범위 밖을 가리키면 INDEX는 #REF! 오류를 반환합니다. 반대로 row_num 또는 column_num을 0으로 설정하면 INDEX는 열 또는 행 전체를 반환합니다. 최신 Microsoft 365에서는 평소처럼 Enter를 누르면 됩니다. 이전 버전에서는 Ctrl+Shift+Enter로 배열 수식으로 입력합니다.

INDEX만으로는 위치를 미리 알고 있어야 합니다. 이때 MATCH가 필요합니다.

MATCH의 역할

MATCH는 범위를 검색하고 위치를 반환합니다. Microsoft는 구문을 MATCH(lookup_value, lookup_array, [match_type])로 제시합니다. 항목 자체를 반환하지 않고, 범위 안에서의 상대 위치만 반환합니다.

세 번째 인수가 일치 방식을 결정합니다:

• 0은 정확히 일치하는 첫 번째 값을 찾습니다. 범위는 어떤 순서로 정렬되어 있어도 됩니다. 대부분의 경우 이 옵션을 가장 많이 사용하게 됩니다.
• 1은 인수를 생략했을 때의 기본값이며, 조회 값보다 작거나 같은 값 중 가장 큰 값을 찾습니다. 범위는 오름차순으로 정렬되어 있어야 합니다.
• -1은 조회 값보다 크거나 같은 값 중 가장 작은 값을 찾습니다. 범위는 내림차순으로 정렬되어 있어야 합니다.

Microsoft 예제에서는 B2:B5에 25, 38, 40, 41이라는 값이 들어 있습니다. =MATCH(39,B2:B5,1)은 39보다 작은 값 중 가장 큰 값인 38의 위치인 2를 반환합니다. =MATCH(41,B2:B5,0)은 4를 반환합니다. 그리고 =MATCH(40,B2:B5,-1)은 값이 내림차순으로 정렬되어 있지 않으므로 #N/A를 반환합니다.

문서에서 설명하는 다른 동작도 있습니다. 텍스트 일치는 대소문자를 구분하지 않으므로 "apple"과 "APPLE"은 같은 값으로 취급됩니다. 일치 유형이 0이고 조회 값이 텍스트인 경우 ?는 임의의 문자 한 개와, *는 임의의 문자열과 일치합니다. 두 기호 앞에 물결표(~)를 붙이면 와일드카드 문자 자체를 찾습니다. MATCH가 값을 찾지 못하면 #N/A를 반환합니다.

기본값 1은 많은 사람이 실수하는 부분입니다. 세 번째 인수를 빠뜨리면 MATCH는 데이터가 정렬되어 있다고 임의로 가정하며, 오류 없이 잘못된 위치를 반환할 수 있습니다. ID, 이름, 코드처럼 거의 모든 조회 작업에서는 0을 입력하십시오.

두 함수를 함께 사용하기

패턴은 항상 같습니다. MATCH를 INDEX 안에 넣고, 행 번호를 MATCH가 제공하도록 합니다.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

A2:A100에 직원 ID, B2:B100에 이름, C2:C100에 부서가 입력된 시트가 있다고 가정하겠습니다. F2에 ID를 입력하고 부서를 찾고 싶습니다.

1. 결과를 표시할 셀, 예를 들어 G2를 클릭합니다.
2. =INDEX(C2:C100,를 입력하여 답을 가져올 범위를 지정합니다.
3. MATCH(F2,A2:A100,0))를 추가하여 ID를 찾을 위치를 지정합니다.
4. Enter 키를 누릅니다.

완성된 수식은 =INDEX(C2:C100,MATCH(F2,A2:A100,0))입니다. MATCH는 A2:A100에서 F2의 값을 찾아 예를 들어 14를 반환합니다. 그러면 INDEX는 C2:C100에서 14번째 항목을 반환합니다.

두 범위의 크기와 시작 행을 동일하게 맞추십시오. MATCH가 A2:A100을 검색하는데 INDEX가 C3:C101을 읽으면 위치가 잘못된 행을 가리키게 되고, 결과는 오류 메시지 없이 틀리게 됩니다. 두 범위의 시작 행과 끝 행을 같게 하고, 수식을 아래로 복사할 계획이라면 $A$2:$A$100처럼 달러 기호($)를 붙여 범위를 고정하십시오.

왼쪽 조회와 VLOOKUP과의 차이

Microsoft의 VLOOKUP 페이지에서는 구문을 VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])로 설명합니다. 조회 값은 범위의 첫 번째 열에 있어야 하며, range_lookup을 생략하면 정렬된 데이터를 가정하는 근사 일치가 적용된다고 설명합니다. 이 페이지는 XLOOKUP을 사용해 보라고도 권장합니다.

같은 작업을 두 가지 방식으로 보겠습니다. A열에 ID, B열에 이름이 있는 경우입니다.

• =VLOOKUP(F2,A2:B100,2,FALSE)
• =INDEX(B2:B100,MATCH(F2,A2:A100,0))

INDEX MATCH에는 실질적인 장점이 두 가지 있습니다. 첫째, 왼쪽으로 조회할 수 있습니다. 이름이 B열에 있고 A열의 ID를 가져오려면 범위만 바꾸면 됩니다: =INDEX(A2:A100,MATCH(F2,B2:B100,0)). VLOOKUP은 조회 값이 첫 번째 열에 있어야 하므로 이것이 불가능합니다. 둘째, 반환할 열을 개수로 세지 않고 직접 지정합니다. VLOOKUP에서 수식의 2는 "범위의 두 번째 열"을 뜻합니다. 범위 안에 열을 삽입하면 이 숫자가 다른 열을 가리키게 됩니다. INDEX MATCH에서는 참조가 다른 참조와 마찬가지로 자동으로 조정됩니다.

INDEX MATCH가 더 빠르다는 내용도 보게 될 것입니다. Microsoft 페이지에서는 이런 주장을 하지 않으며, 차이는 매우 큰 통합 문서에서만 의미가 있습니다. 따라서 속도 때문에 선택하지 마십시오. 유연성 때문에 선택하십시오.

양방향 조회

양방향 조회는 행과 열이 만나는 지점의 값을 찾습니다. 예를 들어 한 제품의 특정 월 매출이 이에 해당합니다. 행용 MATCH 하나와 열용 MATCH 하나, 두 개의 MATCH 함수를 사용합니다:

=INDEX(B2:M20,MATCH(P1,A2:A20,0),MATCH(Q1,B1:M1,0))

여기서 B2:M20에는 숫자가, A2:A20에는 제품명이, B1:M1에는 월 제목이 있으며, P1과 Q1에는 찾으려는 제품과 월이 들어 있습니다. 첫 번째 MATCH는 행을, 두 번째 MATCH는 열을 찾고, INDEX는 두 위치가 만나는 곳의 숫자를 반환합니다. 반환 범위가 A1이 아닌 B2에서 시작한다는 점에 유의하세요. 그래야 위치가 레이블 범위와 맞아떨어집니다. 두 MATCH 범위도 숫자 범위와 같은 행 또는 열에서 시작합니다.

두 가지 조건으로 일치 찾기

MATCH는 조회 값을 하나만 받습니다. 레코드를 범주와 상태처럼 두 개의 필드로만 식별할 수 있다면 두 가지 방법이 있습니다.

도우미 열. 두 필드를 결합하는 열을 추가합니다. 예를 들어 D2에 =A2&"|"&B2를 입력하고 아래로 채웁니다. 그런 다음 결합된 텍스트를 조회합니다:

=INDEX(C2:C100,MATCH(F2&"|"&G2,D2:D100,0))

구분자가 중요합니다. 구분자가 없으면 "AB"와 "C", 그리고 "A"와 "BC"가 모두 "ABC"가 됩니다. 도우미 열은 읽고 확인하기 쉬우며, 모든 버전에서 동작합니다.

배열 수식. 도우미 열 없이 두 비교 결과를 곱할 수도 있습니다:

=INDEX(C2:C100,MATCH(1,(A2:A100=F2)*(B2:B100=G2),0))

각 비교는 TRUE 또는 FALSE를 반환합니다. 곱하면 두 조건이 모두 참인 곳에서만 1이 되고, MATCH는 첫 번째 1을 찾습니다. 이 방법은 Microsoft의 INDEX 및 MATCH 문서에 나오지 않으므로 작은 범위에서 먼저 테스트하세요. 이전 버전의 Excel에서는 Microsoft가 INDEX의 배열 결과에 대해 설명하는 것과 같은 방식으로 Ctrl+Shift+Enter를 눌러 입력해야 할 수도 있습니다. 현재 Microsoft 365에서는 Enter만으로 충분합니다.

와일드카드와 근사값

일치 유형 0과 텍스트를 사용하면 MATCH가 와일드카드를 허용합니다. =INDEX(B2:B100,MATCH("Jan*",A2:A100,0))는 "Jan"으로 시작하는 첫 항목 옆의 항목을 반환합니다. 부분 코드를 다룰 때 유용합니다. MATCH는 첫 번째 일치에서 멈추므로 패턴을 구체적으로 지정하세요.

세율 구간이나 수수료 구간처럼 등급을 나눌 때는 기본 일치 유형 1을 의도적으로 사용하고, 기준값을 오름차순으로 정렬해 두세요. A2:A4에 0, 1000, 5000을 넣고 B2:B4에 요율을 넣으면, =INDEX(B2:B4,MATCH(E2,A2:A4,1))는 E2를 넘지 않는 가장 큰 기준값의 요율을 반환합니다. 정렬된 기본값이 올바른 선택이 되는 몇 안 되는 경우 중 하나입니다.

오류 해결

• #N/A. MATCH가 아무것도 찾지 못했습니다. 오타, 뒤쪽 공백, 텍스트로 저장된 숫자를 확인하세요. "100"과 100은 서로 다릅니다. 일치 유형이 0인지 확인하고, 정렬 데이터용 일치 유형(1 또는 -1)을 쓴다면 데이터가 실제로 정렬되어 있는지 확인하세요.
• #REF!. INDEX가 범위를 벗어난 행이나 열을 요청했습니다. 수식이 사용하는 행이나 열을 삭제했을 때도 이런 일이 생깁니다. MATCH 범위와 INDEX 범위의 크기가 같은지 확인하세요.
• 오류 없이 잘못된 결과. 보통 MATCH에서 0을 빠뜨렸거나, 범위의 시작 행이 서로 다른 경우입니다.
• 아래로 복사한 뒤 잘못된 결과. 범위가 고정되어 있지 않아 이동했기 때문입니다. 범위에 $를 추가하세요.

오류를 메시지로 바꾸려면 Microsoft의 IFNA 함수를 사용할 수 있습니다. 수식이 #N/A를 반환할 때 지정한 값을 표시하고, 다른 오류는 그대로 통과시킵니다:

=IFNA(INDEX(C2:C100,MATCH(F2,A2:A100,0)),"Not found")

조회에는 IFERROR보다 IFNA를 사용하세요. IFERROR는 깨진 범위로 인한 #REF!를 포함해 모든 오류를 숨기므로, 실제 결함이 평범한 "Not found"처럼 보이게 됩니다. IFNA는 예상한 경우만 처리합니다. Microsoft는 IFNA를 Excel 2016 이상에서 지원한다고 안내합니다.

INDEX MATCH 또는 XLOOKUP

파일을 여는 모든 사용자가 최신 Microsoft 365를 사용한다면, XLOOKUP은 하나의 함수로 같은 작업을 수행하고 찾을 수 없을 때의 메시지도 기본으로 제공합니다. Excel의 XLOOKUP 가이드에서 자세히 다룹니다. 다만 Microsoft의 XLOOKUP 문서는 Excel 2016과 2019 지원 여부가 명확하지 않으며, Excel for the web도 나열하지 않습니다. INDEX MATCH는 이 모든 환경에서 동작합니다. 따라서 선택은 실용적인 문제입니다. 최신 버전을 쓰는 사용자를 위한 파일에는 XLOOKUP을, 파일을 이전 버전 사용자에게 전달하거나 이미 INDEX MATCH를 쓰고 있는 통합 문서를 유지 관리할 때는 INDEX MATCH를 사용하세요.

흔한 실수

• INDEX와 MATCH에서 0을 빼고 기본값을 믿는 것.
• INDEX와 MATCH에 크기나 시작 행이 다른 범위를 사용하는 것.
• 수식을 아래로 복사하기 전에 $로 범위를 고정하지 않는 것.
• 도우미 열에서 구분자 없이 조건을 결합하는 것.
• 모든 것을 IFERROR로 감싸서, 깨진 수식이 누락된 데이터처럼 보이게 하는 것.
• INDEX MATCH가 일치하는 항목을 모두 반환할 것으로 기대하셨겠지만, 실제로는 첫 번째 항목만 반환합니다.

함께 살펴볼 관련 주제

• XLOOKUP. 검색 방향과 찾을 수 없을 때의 인수를 기본으로 갖춘 최신 조회 함수입니다.
• 여러 결과를 한 번에 가져오기. INDEX MATCH는 첫 번째 일치 항목만 반환합니다. 일치하는 항목을 모두 가져와야 한다면, 해당 기능을 지원하는 버전에서는 FILTER 함수를 사용하십시오.
• 표와 이름 정의된 범위. 구조적 참조와 이름을 사용하면 조회 수식을 더 읽기 쉽게 작성할 수 있으며, 데이터가 늘어나도 범위가 함께 확장됩니다.
• 대용량 시트의 성능. 열 전체 대신 범위를 지정해서 사용하고, 범위를 가능한 한 좁게 유지하십시오.
• 대소문자를 구분하는 조회. MATCH는 대소문자를 구분하지 않으므로, 대소문자를 구분하는 조회에는 다른 방법이 필요합니다. 우회 방법을 적용하기 전에 반드시 테스트하십시오.

다음 단계

VLOOKUP 수식 중 하나를 INDEX MATCH로 바꿔 작성하고, 알고 있는 몇 가지 값으로 결과를 비교해 보십시오. 그다음에는 같은 수식을 왼쪽 방향 조회로 시도해 보십시오. 두 가지를 모두 해보면, Excel에서 INDEX MATCH가 어떤 작업에 더 적합한 도구인지 알 수 있을 것입니다.