Excel INDEX MATCH: 기본 및 고급 조회

Excel에서 특정 데이터를 정확하게 검색하는 것은 종종 필요한 작업입니다. INDEX와 MATCH 함수는 각각의 강점을 가지고 있지만, 이들을 결합하면 데이터 조회를 위한 강력한 도구 세트가 됩니다. 함께 사용하면 기본적인 가로 및 세로 조회부터 양방향, 대소문자 구분, 다중 조건 검색과 같은 고급 기능까지 다양한 검색 기능을 제공합니다. VLOOKUP에 비해 향상된 기능을 제공하며, INDEX와 MATCH의 조합은 더 넓은 범위의 데이터 조회 옵션을 허용합니다. 이 튜토리얼에서는 이들이 함께 달성할 수 있는 가능성의 깊이를 탐구해 보겠습니다.

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

• 양방향 조회
• 왼쪽 조회
• 대소문자 구분 조회
• 가장 가까운 일치 항목
• 다중 조건 조회
• 여러 열에 걸친 조회
• 첫 번째 비어 있지 않은 값 조회
• 첫 번째 숫자 값 조회
• MAX 또는 MIN 연관 조회
• 팁: 사용자 정의 #N/A 오류 메시지

Excel에서 INDEX와 MATCH 사용 방법

INDEX와 MATCH 함수를 사용하기 전에, 먼저 INDEX와 MATCH가 값을 조회하는 데 어떻게 도움이 되는지 알아보겠습니다.

Excel에서 INDEX 함수 사용 방법

Excel의 INDEX 함수는 특정 범위에서 주어진 위치의 값을 반환합니다. INDEX 함수의 구문은 다음과 같습니다:

• array (필수)는 값을 반환하려는 범위를 참조합니다.
• row_num (필수, column_num이 있는 경우 제외)은 배열의 행 번호를 참조합니다.
• column_num (선택 사항, row_num이 생략된 경우 필수)은 배열의 열 번호를 참조합니다.

예를 들어, 목록에서6번째 학생인 Jeff의 점수를 알고 싶다면, INDEX 함수를 다음과 같이 사용할 수 있습니다:

=INDEX(C2:C11,6)

√ 참고: 범위 C2:C11은 점수가 나열된 곳이며, 숫자6은6번째 학생의 시험 점수를 찾습니다.

여기서 간단한 테스트를 해보겠습니다. 수식 =INDEX(A1:C1,2)는 어떤 값을 반환할까요? --- 네, 주어진 행의 두 번째 값인 생일을 반환합니다.

이제 INDEX 함수가 가로 또는 세로 범위에서 완벽하게 작동할 수 있다는 것을 알았습니다. 하지만 여러 행과 열이 있는 더 큰 범위에서 값을 반환해야 한다면 어떻게 해야 할까요? 이 경우, 행 번호와 열 번호를 모두 적용해야 합니다. 예를 들어, 단일 열이 아닌 테이블 범위 내에서 Jeff의 점수를 찾으려면, A2에서 C11까지의 셀에서 행 번호6과 열 번호3으로 그의 점수를 찾을 수 있습니다:

=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의 값은 어떤 순서로든 배치될 수 있습니다. (일치 유형이0으로 설정된 경우, 와일드카드 문자를 사용할 수 있습니다.)
  • -1, MATCH는 lookup_value보다 크거나 같은 가장 작은 값을 찾습니다. lookup_array의 값은 내림차순으로 배치되어야 합니다.

예를 들어, 이름 목록에서 Vera의 위치를 알고 싶다면, MATCH 수식을 다음과 같이 사용할 수 있습니다:

=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)범위 A2:A11에서 "Evelyn"의 행 위치를 INDEX에 제공합니다.5.
• column_num:3은 INDEX가 배열 내에서 점수를 찾기 위한3번째 열을 지정합니다.
• array: A2:C11은 INDEX가 지정된 행과 열의 교차점에서 일치하는 값을 반환하도록 지시합니다. 최종적으로 우리는90이라는 결과를 얻습니다.

위의 수식에서, 우리는 하드코딩된 값 "Evelyn"을 사용했습니다. 그러나 실제로 하드코딩된 값은 비실용적입니다. 다른 학생의 점수를 검색할 때마다 수정해야 하기 때문입니다. 이러한 경우, 셀 참조를 사용하여 동적 수식을 만들 수 있습니다. 예를 들어, 이 경우 "Evelyn"을 F2로 변경하겠습니다:

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

INDEX와 MATCH 수식 예제

이 부분에서는 다양한 요구를 충족시키기 위해 INDEX와 MATCH 함수를 사용하는 다양한 상황에 대해 이야기할 것입니다.

INDEX와 MATCH를 사용하여 양방향 조회 적용

이전 예제에서는 열 번호를 알고 행 번호를 찾기 위해 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 함수를 포함하여 이를 강화할 수 있습니다. MATCH 함수를 EXACT와 결합하여 INDEX 수식에서 효과적으로 대소문자 구분 조회를 수행할 수 있습니다. 아래와 같이 사용하세요:

• array는 값을 반환하려는 범위를 참조합니다.
• lookup_value는 lookup_array에서 대소문자를 고려하여 일치시킬 값을 참조합니다.
• lookup_array는 MATCH가 lookup_value와 비교할 셀 범위를 참조합니다.

예를 들어, JIMMY의 시험 점수를 알고 싶다면, 다음 수식을 사용하세요:

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

√ 참고: 이는 배열 수식으로, Excel365, Excel2021 및 최신 버전을 제외하고 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))

√ 참고: 이는 배열 수식으로, Excel365, Excel2021 및 최신 버전을 제외하고 Ctrl + Shift + Enter로 입력해야 합니다.

INDEX와 MATCH를 사용하여 다중 조건 조회 적용

여러 조건을 충족하는 값을 찾기 위해 두 개 이상의 열을 검색해야 할 때, 다음 수식을 사용하세요. 이 수식은 다양한 열에 걸쳐 여러 조건을 지정하여 다중 조건 조회를 수행할 수 있게 하여, 모든 지정된 기준을 충족하는 원하는 값을 찾을 수 있도록 도와줍니다.

√ 참고: 이는 배열 수식으로, Ctrl + Shift + Enter로 입력해야 합니다. 그러면 수식 표시줄에 중괄호가 나타납니다.

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

예를 들어,7/2/2008에 태어난 Class A의 Coco의 점수를 찾으려면, 다음 수식을 사용할 수 있습니다:

=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의 여러 열에 대한 인덱스 및 매치 기능이 유용합니다. 이 기능은 특정 항목을 해당 범주와 빠르고 쉽게 일치시킬 수 있도록 프로세스를 간소화합니다. 이 강력한 도구를 잠금 해제하고 Shawn을 그의 반과 쉽게 일치시키려면, Kutools for Excel 애드인을 다운로드하여 설치한 다음 다음과 같이 하세요:

1. 일치하는 반을 표시할 대상 셀을 선택하세요.
2. Kutools 탭에서 함수 도우미 > 조회 및 참조 > 여러 열에 대한 인덱스 및 매치를 클릭하세요.
3. 
3. 팝업 대화 상자에서 다음과 같이 하세요:
   1. 첫 번째 버튼을 클릭하여 찾을 열 옆에 있는 버튼을 클릭하여 반환하려는 키 정보를 포함하는 열을 선택하세요, 즉 반 이름. (여기서는 단일 열만 선택할 수 있습니다.)
   2. 두 번째 버튼을 클릭하여 참조 테이블 옆에 있는 버튼을 클릭하여 선택한 찾을 열의 값을 일치시키기 위한 셀을 선택하세요, 즉 학생 이름.
   3. 세 번째 버튼을 클릭하여 찾을 값 옆에 있는 버튼을 클릭하여 학생의 이름을 포함하는 셀을 선택하여 반과 일치시키세요, 이 경우 Shawn.
   4. 확인을 클릭하세요.
   5. 

결과

Kutools는 자동으로 수식을 생성했으며, 대상 셀에 Shawn의 반 이름이 즉시 표시됩니다.

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))

INDEX와 MATCH를 사용하여 MAX 또는 MIN 연관 조회

범위 내에서 최대값 또는 최소값과 연관된 값을 검색해야 하는 경우, 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 오류가 나타납니다.

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

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

위는 Excel의 INDEX와 MATCH 함수와 관련된 모든 내용입니다. 튜토리얼이 도움이 되길 바랍니다. 더 많은 Excel 팁과 트릭을 탐색하고 싶다면, 여기를 클릭하여 수천 개의 튜토리얼을 포함한 방대한 컬렉션에 액세스하세요.