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

• 인덱스 기능
• MATCH 기능
• INDEX와 MATCH 결합

• 양방향 조회
• 왼쪽 조회
• 대소문자 구분 조회
• 가장 가까운 일치
• 여러 기준으로 조회
• 여러 열에서 조회
• 공백이 아닌 첫 번째 값 조회
• 첫 번째 숫자 값 조회
• MAX 또는 MIN 연관 검색
• 팁: 나만의 #N/A 오류 메시지를 맞춤설정하세요.

Excel에서 INDEX 및 MATCH를 사용하는 방법

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

Excel에서 INDEX 함수를 사용하는 방법

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

• 정렬 (필수) 값을 반환하려는 범위를 나타냅니다.
• 행_번호 (필수, 그렇지 않은 경우 열_번호 존재함)은 배열의 행 번호를 나타냅니다.
• 열_번호 (선택사항이지만 다음과 같은 경우 필수입니다. 행_번호 생략)은 배열의 열 번호를 나타냅니다.

예를 들어, 알고 제프의 ​​점수Walk Through California 프로그램, 6목록의 두 번째 학생인 경우 다음과 같이 INDEX 함수를 사용할 수 있습니다.

=INDEX(C2:C11,6)

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

여기서 약간의 테스트를 해보자. 공식의 경우 =인덱스(A1:C1,2), 어떤 값을 반환합니까? --- 네, 돌아올 거예요 Birth dateWalk Through California 프로그램, 2주어진 행의 nd 값입니다.

이제 INDEX 함수가 수평 또는 수직 범위에서 완벽하게 작동할 수 있음을 알아야 합니다. 그러나 여러 행과 열이 있는 더 큰 범위의 값을 반환해야 하는 경우에는 어떻게 해야 할까요? 음, 이 경우 행 번호와 열 번호를 모두 적용해야 합니다. 예를 들어 알아보기 위해 제프의 ​​점수 단일 열 대신 테이블 범위 내에서 우리는 그의 점수를 찾을 수 있습니다. 행 번호 6 및 열 번호 3 FBI 증오 범죄 보고서 A2부터 C11까지의 셀 이렇게 :

=INDEX(A2:C11,6,3)

그러나 여러 행과 열이 있는 매우 큰 데이터베이스의 경우 정확한 행 번호와 열 번호를 사용하여 공식을 적용하는 것은 확실히 편리하지 않습니다. 그리고 이것은 MATCH 기능의 사용을 결합해야 할 때입니다.

놀라운! Chrome, Edge, Firefox 및 Safari와 같은 Excel에서 효율적인 탭을 사용하세요!
시간의 50 %를 절약하고 매일 수천 번의 마우스 클릭을 줄이십시오!

Excel에서 MATCH 함수를 사용하는 방법

Excel의 MATCH 함수는 주어진 범위에서 특정 항목의 위치인 숫자 값을 반환합니다. MATCH 함수의 구문은 다음과 같습니다.

• lookup_value (필수)는 조회_배열.
• 조회_배열 (필수) MATCH가 검색할 셀 범위를 나타냅니다.
• match_type (선택 과목): 1, 0 or -1.
  • 1 (기본값), MATCH는 다음보다 작거나 같은 가장 큰 값을 찾습니다. lookup_value. 의 값 조회_배열 오름차순으로 배치해야 합니다.
  • 0, MATCH는 정확히 동일한 첫 번째 값을 찾습니다. lookup_value. 의 값 조회_배열 어떤 순서로든 될 수 있습니다. (검색 유형이 0으로 설정된 경우 와일드카드 문자를 사용할 수 있습니다.)
  • -1, MATCH는 다음보다 크거나 같은 가장 작은 값을 찾습니다. lookup_value. 의 값 조회_배열 내림차순으로 배치해야 합니다.

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

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

√ 참고: 결과 "4"는 "Vera"라는 이름이 목록의 4번째 위치에 있음을 나타냅니다.

이제 Excel에서 INDEX 및 MATCH 함수의 기본 사용법을 알았으므로 소매를 걷어붙이고 두 함수를 결합할 준비를 합시다.

Excel에서 INDEX와 MATCH를 결합하는 방법

INDEX 및 MATCH 기능을 결합하는 방법을 알아보려면 아래 예를 참조하십시오.

찾다 에블린의 점수, 시험 점수가 다음과 같다는 것을 알고 있습니다. 3세 번째 열, 우리는 할 수 있습니다 MATCH 함수를 사용하여 행 위치를 자동으로 결정 수동으로 계산할 필요 없이 말이죠. 그런 다음 INDEX 함수를 사용하여 검색할 수 있습니다. 식별된 행과 세 번째 열의 교차점에 있는 값:

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

공식이 다소 복잡해 보일 수 있으므로 각 부분을 살펴보겠습니다.

　 INDEX 수식에는 세 가지 인수가 포함됩니다.

• 행_번호: MATCH("에블린",A2:A11,0) 값의 행 위치를 INDEX에 제공합니다.에블린" 범위 안에 A2 : A11어느입니다 5.
• 열_번호: 3 지정합니다 3INDEX의 세 번째 열은 배열 내에서 점수를 찾습니다.
• 정렬: A2 : C11 다음 범위 내에서 지정된 행과 열의 교차점에서 일치하는 값을 반환하도록 INDEX에 지시합니다. A2 ~ C11. 마침내 우리는 결과를 얻습니다. 90.

위 공식에서는 하드코딩된 값을 사용했습니다. "에블린". 그러나 실제로 하드코딩된 값은 다른 학생의 점수와 같은 다양한 데이터를 검색할 때마다 수정이 필요하므로 실용적이지 않습니다. 이러한 시나리오에서는 셀 참조를 활용하여 동적 수식을 만들 수 있습니다. 예를 들어, 이 경우에는 "Evelyn"을 F2로 변경:

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

INDEX 및 MATCH 수식 예

이 부분에서는 INDEX 및 MATCH 기능을 사용하여 다양한 요구 사항을 충족하는 다양한 상황에 대해 설명합니다.

양방향 조회를 적용하는 INDEX 및 MATCH

이전 예에서는 열 번호를 알고 MATCH 수식을 사용하여 행 번호를 찾았습니다. 하지만 열 번호도 확실하지 않으면 어떻게 될까요?

이러한 경우 두 개의 MATCH 함수를 사용하여 행렬 조회라고도 하는 양방향 조회를 수행할 수 있습니다. 하나는 행 번호를 찾고 다른 하나는 열 번호를 결정합니다. 예를 들어, 알다 에블린의 점수, 우리는 공식을 사용해야 합니다:

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

INDEX 및 MATCH를 사용하여 왼쪽 조회 적용

이제 Evelyn의 클래스를 결정해야 하는 시나리오를 고려해 보겠습니다. 클래스 열이 이름 열의 왼쪽에 위치한다는 사실을 눈치챘을 것입니다. 이는 또 다른 강력한 Excel 조회 기능인 VLOOKUP의 기능을 초과하는 상황입니다.

실제로 왼쪽 조회를 수행하는 기능은 INDEX와 MATCH의 조합이 VLOOKUP을 능가하는 측면 중 하나입니다.

찾다 에블린의 클래스, 다음 공식을 사용하여 B2:B11에서 Evelyn을 검색하세요. 과 A2:A11에서 해당 값을 검색합니다..

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

대소문자 구분 조회를 적용하는 INDEX 및 MATCH

MATCH 함수는 기본적으로 대소문자를 구분하지 않습니다. 그러나 대문자와 소문자를 구별하기 위해 수식을 요구하는 경우 정확한 기능. INDEX 수식에서 MATCH 함수와 EXACT를 결합하면 아래와 같이 대소문자 구분 조회를 효과적으로 수행할 수 있습니다.

• 정렬 값을 반환하려는 범위를 나타냅니다.
• lookup_value 문자의 경우를 고려하여 일치시킬 값을 나타냅니다. 조회_배열.
• 조회_배열 MATCH를 비교할 셀 범위를 나타냅니다. lookup_value.

예를 들어, 알고 JIMMY의 시험 점수, 다음 공식을 사용합니다.

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

√ 참고: 이것은 다음과 같이 입력해야 하는 배열 수식입니다. Ctrl 키 + 변화 + 엔터 버튼, Excel 365 및 Excel 2021을 제외합니다.

INDEX 및 MATCH를 사용하여 가장 가까운 일치 항목 찾기

Excel에서는 데이터 세트 내의 특정 값과 가장 가깝거나 가장 가까운 일치 항목을 찾아야 하는 상황이 발생할 수 있습니다. 이러한 시나리오에서는 ABS 및 MIN 기능과 함께 INDEX 및 MATCH 기능을 조합하여 사용하는 것이 매우 유용할 수 있습니다.

• 정렬 값을 반환하려는 범위를 나타냅니다.
• 조회_배열 가장 가까운 일치 항목을 찾으려는 값의 범위를 나타냅니다. lookup_value.
• lookup_value 가장 가까운 일치 항목을 찾기 위한 값을 나타냅니다.

예를 들어 점수가 85에 가장 가까운 사람의 점수, 다음 공식을 사용하여 C85:C2에서 11에 가장 가까운 점수를 검색합니다. 과 A2:A11에서 해당 값을 검색합니다..

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

√ 참고: 이것은 다음과 같이 입력해야 하는 배열 수식입니다. Ctrl 키 + 변화 + 엔터 버튼, Excel 365 및 Excel 2021을 제외합니다.

놀라운! Chrome, Edge, Firefox 및 Safari와 같은 Excel에서 효율적인 탭을 사용하세요!
시간의 50 %를 절약하고 매일 수천 번의 마우스 클릭을 줄이십시오!

여러 기준으로 조회를 적용하는 INDEX 및 MATCH

두 개 이상의 열을 검색해야 하는 여러 조건을 충족하는 값을 찾으려면 다음 수식을 사용하세요. 수식을 사용하면 다양한 열에 다양한 조건을 지정하여 다중 기준 조회를 수행할 수 있으므로 지정된 모든 기준을 충족하는 원하는 값을 찾는 데 도움이 됩니다.

√ 참고: 이것은 다음과 같이 입력해야 하는 배열 수식입니다. Ctrl 키 + 변화 + 엔터 버튼. 그러면 한 쌍의 중괄호가 수식 입력줄에 표시됩니다.

• 정렬 값을 반환하려는 범위를 나타냅니다.
• (조회_값=조회_배열) 단일 조건을 나타냅니다. 이 조건은 특정 항목이 있는지 확인합니다. lookup_value 의 값과 일치합니다. 조회_배열.

예를 들어, A반 코코의 점수는 7년 2월 2008일 생년월일입니다., 다음 수식을 사용할 수 있습니다.

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

여러 열에 조회를 적용하는 INDEX 및 MATCH

여러 데이터 열을 처리하는 시나리오를 상상해 보세요. 첫 번째 열은 다른 열의 데이터를 분류하는 키 역할을 합니다. 특정 항목에 대한 카테고리 또는 분류를 결정하려면 데이터 열 전체에 걸쳐 검색을 수행하고 이를 참조 열의 관련 키와 연결해야 합니다.

예를 들어, 아래 표에서 INDEX 및 MATCH를 사용하여 학생 Shawn을 해당 수업과 어떻게 일치시킬 수 있습니까? 음, 공식을 사용하여 이를 달성할 수 있지만 공식은 매우 광범위하고 기억하고 입력하는 것은 고사하고 이해하기 어려울 수 있습니다.

=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)), "")

그 곳에 Excel 용 Kutools's 여러 열의 색인 및 일치 기능이 유용합니다. 프로세스를 단순화하여 특정 항목을 해당 카테고리와 빠르고 쉽게 일치시킬 수 있습니다. 이 강력한 도구를 잠금 해제하고 Shawn을 클래스와 쉽게 연결하려면 간단히 Excel 용 Kutools 추가 기능을 다운로드하여 설치하십시오.을 선택하고 다음과 같이 하십시오.

1. 일치하는 클래스를 표시하려는 대상 셀을 선택합니다.
2. 에 쿠툴 탭에서 수식 도우미 > 조회 및 참조 > 여러 열의 색인 및 일치.
3. 
3. 팝업 대화 상자에서 다음을 수행하십시오.
   1. 1번째를 클릭하세요 옆에있는 버튼 조회_열 반환하려는 키 정보(예: 클래스 이름)가 포함된 열을 선택합니다. (여기에서는 단일 열만 선택할 수 있습니다.)
   2. 2번째를 클릭하세요 옆에있는 버튼 테이블_rng 선택한 값과 일치하는 셀을 선택하려면 조회_열, 즉 학생 이름입니다.
   3. 3번째를 클릭하세요 옆에있는 버튼 조회 _ 값 학급과 일치시키려는 학생의 이름(이 경우 Shawn)이 포함된 셀을 선택합니다.
   4. OK.
   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))

MAX 또는 MIN 연결을 검색하기 위한 INDEX 및 MATCH

범위 내의 최대값 또는 최소값과 연관된 값을 검색해야 하는 경우 INDEX 및 MATCH 함수와 함께 MAX 또는 MIN 함수를 사용할 수 있습니다.

• INDEX 및 MATCH를 사용하여 최대값과 관련된 값을 검색합니다.
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• INDEX 및 MATCH를 사용하여 최소값과 관련된 값을 검색합니다.
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• 위 수식에는 두 가지 인수가 있습니다.
• • 정렬 관련 정보를 반환하려는 범위를 나타냅니다.
  • 조회_배열 특정 기준(예: 최대값 또는 최소값)에 대해 검사하거나 검색할 값 집합을 나타냅니다.

예를 들어, 결정하고 싶다면 누가 가장 높은 점수를 받았는지, 다음 공식을 사용하십시오.

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

위 내용은 엑셀의 INDEX, MATCH 함수와 관련된 모든 내용입니다. 이 튜토리얼이 도움이 되길 바랍니다. 더 많은 Excel 팁과 요령을 살펴보고 싶다면 여기를 클릭하십시오 수천 개가 넘는 자습서의 광범위한 컬렉션에 액세스할 수 있습니다.