Excel INDEX MATCH： 기본 및 고급 조회

Excel 에서 특정 데이터를 정확하게 검색하는 것은 종종 빈번한 요구사항입니다. INDEX 및 MATCH 함수는 각각 고유한 장점을 지니지만, 이 둘을 결합하면 데이터 조회를 위한 강력한 도구 모음을 활용할 수 있습니다. 함께 사용할 때 기본적인 가로·세로 조회부터 양방향, 대소문자 구분, 다중 조건 검색과 같은 보다 고급 기능에 이르기까지 다양한 검색 기능을 수행할 수 있습니다. VLOOKUP 보다 향상된 기능을 제공하는 INDEX 와 MATCH 의 조합은 더 폭넓은 데이터 조회 옵션을 가능하게 합니다。 이번 자습서에서는 이 두 함수가 함께 구현할 수 있는 가능성의 깊이를 살펴보겠습니다。

• INDEX 함수
• MATCH 함수
• INDEX 와 MATCH 결합하기

• 양방향 조회
• 왼쪽 조회
• 대소문자 구분 조회
• 가장 근접한 값 조회
• 여러 조건을 사용한 조회
• 여러 열에 걸친 조회
• 처음 나오는 비어 있지 않은 값 조회
• 첫 번째 숫자 값 찾기
• MAX 또는 MIN 연관성 검색
• 팁： 사용자 정의 #N/A 오류 메시지를 설정하세요

Excel 에서 INDEX 및 MATCH 사용 방법

INDEX 및 MATCH 함수를 사용하기 전에， 먼저 이 함수들이 값 조회에 어떻게 도움을 주는지 이해해 보겠습니다。

Excel 에서 INDEX 함수 사용 방법

Excel 의INDEX함수는 특정 범위 내에서 지정된 위치의 값을 반환합니다。 INDEX 함수의 구문은 다음과 같습니다：

• array(필수)는 값을 반환받고자 하는 범위를 나타냅니다。
• row_num(column_num)이 지정되지 않은 경우 필수)는 배열의 행 번호를 나타냅니다。 is present) refers to the row number of the array.
• column_num(선택 사항이지만 row_num)이 생략된 경우 필수)는 배열의 열 번호를 나타냅니다。 is omitted) refers to the column number of the array.

예를 들어，Jeff 의 점수를 알아보려면 목록에서6번째 학생의 점수를 다음과 같이 INDEX 함수를 사용하여 구할 수 있습니다：

=INDEX(C2:C11,6)

√ 참고： 범위C2:C11은 점수가 나열된 영역이며， 숫자6은 목록에서6번째 학생의 시험 점수를 찾습니다。

여기서 간단한 테스트를 해 보겠습니다。 수식=INDEX(A1:C1,2)는 어떤 값을 반환할까요？ — 맞습니다。 이 수식은 주어진 행에서생년월일인2번째 값을 반환합니다。

이제 INDEX 함수가 가로 또는 세로 범위에서 완벽하게 작동함을 알게 되었습니다。 그러나 여러 행과 열로 구성된 더 큰 범위에서 값을 반환해야 한다면 어떻게 해야 할까요？ 이런 경우 행 번호와 열 번호를 모두 지정해야 합니다。 예를 들어， 단일 열이 아닌 전체 테이블 범위 내에서Jeff 의 점수를 찾으려면행 번호 6과열 번호 3를 사용하여A2 에서 C11 까지의 셀 범위에서 다음과 같이 점수를 찾을 수 있습니다：

=INDEX(A2:C11,6,3)

그러나 실제로는 행과 열이 매우 많은 대규모 데이터베이스의 경우， 정확한 행 번호와 열 번호를 직접 입력하는 방식은 분명히 불편합니다。 바로 이때 MATCH 함수를 함께 사용해야 합니다。

Excel 에서 MATCH 함수 사용 방법

Excel 의 MATCH 함수는 지정된 범위에서 특정 항목의 위치를 나타내는 숫자 값을 반환합니다。 MATCH 함수의 구문은 다음과 같습니다：

• lookup_value(필수)는lookup_array에서 일치시킬 값을 나타냅니다。
• lookup_array(필수)는 MATCH 함수가 검색할 셀 범위를 나타냅니다。
• match_type(선택 사항)：1,0또는-1.
  • 1(기본값)인 경우, MATCH 는lookup_value보다 작거나 같은 가장 큰 값을 찾습니다。lookup_array의 값은 오름차순으로 정렬되어야 합니다。
  • 0인 경우, MATCH 는lookup_value과 정확히 일치하는 첫 번째 값을 찾습니다。lookup_array의 값은 어떤 순서로든 배치될 수 있습니다. (MATCH 유형이 0 로 설정된 경우 와일드카드 문자를 사용할 수 있습니다。)
  • -1인 경우, MATCH 는lookup_value보다 크거나 같은 가장 작은 값을 찾습니다。lookup_array의 값은 내림차순으로 정렬되어야 합니다。

예를 들어，이름 목록에서 Vera 의 위치를 확인하려면 다음과 같이 수식 구분를 사용할 수 있습니다：

=MATCH("Vera",A2:A11,0)

√ 참고: 결과 “4”은 이름 “Vera”가 목록에서 4 번째 위치에 있음을 나타냅니다。

이제 Excel 에서 INDEX 및 MATCH 함수의 기본 사용법을 알게 되었으니， 두 함수를 결합해 보겠습니다。

Excel 에서 INDEX 와 MATCH 결합 방법

아래 예제를 통해 INDEX 와 MATCH 함수를 어떻게 결합할 수 있는지 살펴보겠습니다：

시험 점수가Evelyn 의 점수임을 알고 있으며， 해당 점수가3번째 열에 있음을 전제로，MATCH 함수를 사용하여 행 위치를 자동으로 결정할 수 있습니다. 이렇게 하면 수작업으로 행 번호를 세어볼 필요가 없습니다. 이후 INDEX 함수를 사용하여 식별된 행과 3 번째 열이 교차하는 지점의값을 검색할 수 있습니다：

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

수식이 다소 복잡해 보일 수 있으므로， 각 부분을 차례로 살펴보겠습니다。

INDEX 수식은 세 가지 인수로 구성됩니다：

• row_num:MATCH(「Evelyn」,A2:A11,0)은 INDEX 함수에 범위EvelynA2:A11 에서 ““ 값의 행 위치()5)를 제공합니다。
• column_num:3은 INDEX 함수가 배열 내에서 점수를 찾을3번째 열을 지정합니다。
• array:A2:C11은 INDEX 함수에게 지정된 행과 열이 교차하는 위치에서 일치하는 값을 반환하도록 지시합니다。 이 범위는A2 부터 C11 까지입니다。 최종적으로 결과값90을 얻게 됩니다。

위 수식에서는 하드코딩된 값「Evelyn」을 사용했습니다。 그러나 실제로는 하드코딩된 값은 비효율적입니다。 다른 학생의 점수처럼 다른 데이터를 검색할 때마다 수식을 수정해야 하기 때문입니다。 이런 경우 셀 참조를 사용하여 동적 수식을 만들 수 있습니다。 예를 들어， 여기서는「Evelyn」을 F2 로 변경하겠습니다：

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

INDEX 및 수식 구분 예제

이번에는 다양한 상황에서 INDEX 및 MATCH 함수를 사용하여 서로 다른 요구사항을 충족하는 방법을 살펴보겠습니다。

INDEX 와 MATCH 를 사용한 양방향 조회

이전 예제에서는 열 번호를 알고 있었고 수식 구분을 사용하여 행 번호를 찾았습니다。 그런데 열 번호도 모를 경우에는 어떻게 해야 할까요？

이런 경우 두 개의 MATCH 함수를 사용하여 행 번호와 열 번호를 각각 찾아내는 양방향 조회(매트릭스 조회라고도 함)를 수행할 수 있습니다。 예를 들어，Evelyn 의 점수를 확인하려면 다음 수식을 사용합니다：

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

INDEX 와 MATCH 를 사용한 좌측 조회

이제 Evelyn 의 반을 알아내야 하는 상황을 생각해 보겠습니다. 반 정보가 이름 열의 왼쪽에 위치하고 있음을 눈치채셨을 것입니다. 이는 또 다른 강력한 Excel 조회 함수인 VLOOKUP 의 기능을 초과하는 사례입니다。

사실 좌측 조회 기능은 INDEX 와 MATCH 조합이 VLOOKUP 보다 뛰어난 측면 중 하나입니다。

Evelyn 의 반을 찾으려면다음 수식을 사용하여B2:B11 에서 Evelyn 을 검색하고A2:A11 에서 해당 값을 검색하세요。

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

INDEX 와 MATCH 를 사용한 대소문자 구분 조회

MATCH 함수는 기본적으로 대소문자를 구분하지 않습니다。 그러나 수식이 대소문자를 구분해야 하는 경우，EXACT함수를 결합하여 이를 강화할 수 있습니다。 INDEX 수식 내에서 MATCH 함수와 EXACT 함수를 함께 사용하면 아래와 같이 효과적으로 대소문자 구분 조회를 수행할 수 있습니다：

• array는 값을 반환받고자 하는 범위를 나타냅니다。
• lookup_value는 문자의 대소문자를 고려하여lookup_array에서 일치시킬 값을 나타냅니다。
• lookup_array는 MATCH 함수가lookup_value와 비교할 셀 범위를 나타냅니다。

예를 들어，JIMMY 의 시험 점수를 확인하려면 다음 수식을 사용하세요：

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ 참고： 이는 배열 수식으로， Excel 365, Excel 2021 이상 버전을 제외하고Ctrl+Shift+Enter을 눌러 입력해야 합니다。

INDEX 와 MATCH 를 사용한 가장 근접한 값 찾기

Excel 에서는 데이터 집합 내에서 특정 값과 가장 가까운 값을 찾아야 하는 상황이 발생할 수 있습니다. 이런 경우 INDEX 및 MATCH 함수를 ABS 와 MIN 함수와 함께 사용하면 매우 유용합니다。

• array는 값을 반환받고자 하는 범위를 나타냅니다。
• lookup_array는lookup_value에 가장 근접한 값을 찾고자 하는 값의 범위를 나타냅니다。
• lookup_value는 가장 근접한 값을 찾고자 하는 값을 나타냅니다。

예를 들어，85 에 가장 가까운 점수를 가진 사람을 찾으려면 다음 수식을 사용하여C2:C11 에서 85 에 가장 근접한 점수를 검색하고A2:A11 에서 해당 값을 검색하세요。

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ 참고： 이는 배열 수식으로， Excel 365, Excel 2021 이상 버전을 제외하고Ctrl+Shift+Enter을 눌러 입력해야 합니다。

INDEX 와 MATCH 를 사용한 다중 조건 조회

두 개 이상의 열에서 여러 조건을 동시에 만족하는 값을 찾으려면 다음 수식을 사용하세요。 이 수식은 서로 다른 열에 다양한 조건을 지정하여 모든 조건을 충족하는 원하는 값을 찾을 수 있도록 다중 조건 조회를 수행합니다。

√ 참고： 이는 배열 수식으로，Ctrl+Shift+Enter을 눌러 입력해야 합니다。 그러면 수식 표시줄에 중괄호 쌍이 표시됩니다。

• array는 값을 반환받고자 하는 범위를 나타냅니다。
• (lookup_value=lookup_array)는 단일 조건을 나타냅니다。 이 조건은 특정lookup_value가lookup_array의 값들과 일치하는지 확인합니다。

예를 들어, 생일이 7/2/2008 인 A 반의 코코의 점수를 찾으려면다음 수식을 사용할 수 있습니다：

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

여러 열에 걸쳐 조회를 수행하기 위한 INDEX 및 MATCH

여러 데이터 열을 다루는 상황을 가정해 보겠습니다。 첫 번째 열은 다른 열의 데이터를 분류하는 키 역할을 합니다。 특정 항목의 범주나 분류를 결정하려면 데이터 열 전체에서 검색을 수행한 후 참조 열의 관련 키와 연결해야 합니다。

예를 들어, 아래 표에서 학생 션(Shawn)을 그의 해당 반과 어떻게 매칭할 수 있을까요? INDEX 및 MATCH 를 사용하면 가능하지만， 이 경우 수식이 매우 길고 이해하기 어려울 뿐만 아니라 기억하거나 입력하기도 쉽지 않습니다。

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

이럴 때Kutools for Excel의여러 열에 대한 인덱스 및 매치기능이 유용합니다。 이 기능은 프로세스를 단순화하여 특정 항목을 해당 범주와 빠르고 쉽게 매칭할 수 있게 해줍니다。 이 강력한 도구를 활용해 션을 그의 반과 손쉽게 매칭하려면 다음을 수행하세요：Kutools for Excel 애드인을 다운로드하여 설치하고

1. 일치하는 클래스를 표시할 대상 셀을 선택하세요。
2. Kutools 탭에서함수 도우미>조회 및 참조>여러 열에 대한 인덱스 및 매치를 클릭하세요。.
3. 
3. 팝업 대화 상자에서 다음을 수행하세요：
   1. 첫 번째버튼을 클릭하여Lookup_col옆에 있는 반환하려는 핵심 정보(예： 반 이름)가 포함된 열을 선택합니다。 (여기서는 단일 열만 선택할 수 있습니다。)
   2. 두 번째버튼을 클릭하여Table_rng옆에 있는 셀 범위를 선택합니다。 이 범위는 선택한Lookup_col의 값(예： 학생 이름)과 일치시킬 데이터입니다。
   3. 세 번째버튼을 클릭하여Lookup_value옆에 있는 셀을 선택합니다。 이 셀에는 학생 이름(예： Shawn)이 포함되어 있으며， 해당 이름을 반 정보와 매칭합니다。
   4. 클릭하세요확인.
   5. 

결과

Kutools 가 자동으로 수식을 생성했으며， 대상 셀에 즉시 션의 반 이름이 표시됩니다。

첫 번째 공백이 아닌 값을 조회하기 위한 INDEX 및 MATCH

열이나 행에서 오류를 무시하고 첫 번째 공백이 아닌 값을 가져오려면 INDEX 및 MATCH 함수를 기반으로 한 수식을 사용할 수 있습니다。 그러나 범위 내 오류를 무시하고 싶지 않다면 ISBLANK 함수를 추가하세요。

• 오류를 무시하고 열 또는 행에서 처음 나오는 비어 있지 않은 값 가져오기：

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• 오류를 포함하여 열 또는 행에서 처음 나오는 비어 있지 않은 값 가져오기：

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

첫 번째 숫자 값을 조회하기 위한 INDEX 및 MATCH

열이나 행에서 첫 번째 숫자 값을 가져오려면 INDEX， MATCH 및 ISNUMBER 함수를 기반으로 한 수식을 사용하세요。

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

MAX 또는 MIN 값과 연관된 항목을 조회하기 위한 INDEX 및 MATCH

범위 내 최대값 또는 최소값과 연관된 값을 가져와야 할 경우 MAX 또는 MIN 함수를 INDEX 및 MATCH 함수와 함께 사용할 수 있습니다。

• INDEX 및 MATCH 를 사용하여 최대값과 연관된 값을 검색하기：
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• INDEX 및 MATCH 를 사용하여 최소값과 연관된 값을 검색하기：
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• 상기 수식에는 두 개의 인수가 있습니다：
• • array는 관련 정보를 반환하려는 범위를 의미합니다。
  • lookup_array는 특정 조건(예： 최댓값 또는 최소값)을 검색할 값 집합을 나타냅니다。

예를 들어，누가 가장 높은 점수를 받았는지 확인하려면다음 수식을 사용하세요：

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

팁： 사용자 정의 #N/A 오류 메시지를 설정하세요

Excel 의 INDEX 및 MATCH 함수를 사용할 때 일치하는 결과가 없으면 #N/A 오류가 발생할 수 있습니다。 예를 들어， 아래 표에서 사만다(Samantha)라는 학생의 점수를 찾으려 하면， 해당 데이터 집합에 그녀가 존재하지 않기 때문에 #N/A 오류가 나타납니다。

스프레드시트를 더 사용자 친화적으로 만들기 위해 IFNA 함수로 INDEX 수식 구분를 감싸 이 오류 메시지를 사용자 정의할 수 있습니다：

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

위 내용은 Excel 에서 INDEX 및 MATCH 함수와 관련된 모든 정보입니다。 이 튜토리얼이 도움이 되었기를 바랍니다。 더 많은 Excel 팁과 요령을 알아보고 싶으시다면여기를 클릭하세요수천 개 이상의 튜토리얼을 확인하실 수 있습니다。