Note: The other languages of the website are Google-translated. Back to English
로그인  \/ 
x
or
x
Register  \/ 
x

or

Excel에서 INDEX와 MATCH를 함께 사용하는 방법

Excel 표로 작업할 때 값을 조회해야 하는 상황을 지속적으로 찾을 수 있습니다. 이 튜토리얼에서는 가로 및 세로 조회, 양방향 조회, 대소문자 구분 조회, 여러 기준을 충족하는 조회를 만들기 위해 INDEX 및 MATCH 함수의 조합을 적용하는 방법을 보여줍니다.

Excel에서 INDEX 및 MATCH 함수의 기능

INDEX와 MATCH 기능을 함께 사용하는 방법


Excel에서 INDEX 및 MATCH 함수의 기능

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

Excel에서 INDEX 함수 사용

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

=INDEX(array, row_num, [column_num])
  • 정렬 (필수) 값을 반환하려는 범위를 나타냅니다.
  • row_num (column_num이 없는 경우 필수)는 배열의 행 번호를 나타냅니다.
  • column_num (선택 사항이지만 row_num이 생략된 경우 필수) 배열의 열 번호를 나타냅니다.

예를 들어, 알고 Jeff의 기말고사 점수, 목록의 6번째 학생은 다음과 같이 INDEX 함수를 사용할 수 있습니다.

= 인덱스(E2:E11, 6) >>> 반환 60

엑셀 인덱스 매치 01

√ 참고: 범위 E2 : E11 는 기말 시험이 나열된 곳이고 숫자는 6 의 시험 점수를 찾습니다. 6학생.

여기서 약간의 테스트를 해보자. 공식의 경우 = 인덱스(B2:E2,3), 어떤 값을 반환합니까? ---예, 반환됩니다 중국3rd 주어진 범위의 값.

이제 INDEX 함수가 수평 또는 수직 범위에서 완벽하게 작동할 수 있음을 알아야 합니다. 그러나 여러 행과 열이 있는 더 큰 범위의 값을 반환해야 하는 경우에는 어떻게 해야 할까요? 음, 이 경우 행 번호와 열 번호를 모두 적용해야 합니다. 예를 들어 알아보기 위해 Emily의 출신 국가 INDEX를 사용하면 다음과 같이 B8에서 E3까지의 셀에서 행 번호가 2이고 열 번호가 11인 값을 찾을 수 있습니다.

= 인덱스(B2:E11,8,3) >>> 반환 중국

엑셀 인덱스 매치 02

위의 예에 따르면, Excel의 INDEX 함수에 대해 다음을 알아야 합니다.

  • INDEX 기능은 수직 및 수평 범위에서 작동할 수 있습니다.
  • INDEX 함수는 대소문자를 구분하지 않습니다.
  • 행 번호는 INDEX 수식에서 열 번호보다 먼저 옵니다(두 숫자가 모두 필요한 경우).

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

자, 먼저 MATCH 함수의 기본에 대해 알아보자.


Excel에서 MATCH 함수 사용

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

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

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

=MATCH("베라",C2:C11,0) >>> 반환 4

엑셀 인덱스 매치 03

√ 참고: MATCH 기능은 대소문자를 구분하지 않습니다. 결과 "4"는 "Vera"라는 이름이 목록의 4번째 위치에 있음을 나타냅니다. 수식의 "0"은 조회 값 "Vera"와 정확히 동일한 조회 배열의 첫 번째 값을 찾는 일치 유형입니다.

알고 B96에서 E2까지 행에서 점수 "2"의 위치, 다음과 같이 MATCH를 사용할 수 있습니다.

=MATCH(96,B2:E2,0) >>> 반환 4

엑셀 인덱스 매치 04

☞ Excel의 MATCH 함수에 대해 알아야 할 사항:

  • MATCH 함수는 값 자체가 아니라 조회 배열에서 조회 값의 위치를 ​​반환합니다.
  • MATCH 함수는 중복된 경우 첫 번째 일치 항목을 반환합니다.
  • INDEX 함수와 마찬가지로 MATCH 함수는 수직 및 수평 범위에서도 작동할 수 있습니다.
  • MATCH도 대소문자를 구분하지 않습니다.
  • MATCH 수식의 조회 값이 텍스트 형식인 경우 따옴표로 묶습니다.

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


INDEX와 MATCH 기능을 함께 사용하는 방법

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

INDEX와 MATCH를 결합하는 예

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

예를 들어, 알고 Evelyn의 최종 시험 점수, 우리는 공식을 사용해야 합니다:

=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> 반환 90

엑셀 인덱스 매치 05

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

엑셀 인덱스 매치 06

위에서 보시다시피 큰 INDEX 수식에는 세 가지 인수가 포함됩니다.

  • 정렬: A2 : D11 INDEX에 다음을 통해 셀에서 일치하는 값을 반환하도록 지시합니다. A2 ~ D11.
  • row_num: MATCH("에블린",B2:B11,0) INDEX에 값의 정확한 행을 알려줍니다.
  • MATCH 공식에 대해 다음과 같이 설명할 수 있습니다. B2에서 B11까지의 셀에서 "evelyn"과 정확히 동일한 첫 번째 값의 위치를 ​​숫자 값으로 반환하려면 5.
  • column_num: MATCH("기말고사",A1:D1,0) INDEX에 값의 정확한 열을 알려줍니다.
  • MATCH 공식에 대해 다음과 같이 설명할 수 있습니다. A1에서 D1까지의 셀에서 "최종 시험"과 정확히 동일한 첫 번째 값의 위치를 ​​숫자 값으로 반환하려면 4.

따라서 아래에 표시된 것처럼 간단한 큰 공식을 볼 수 있습니다.

= 인덱스(A2 : D11,5,4)

이 예에서는 "evelyn" 및 "final exam"이라는 하드코딩된 값을 사용했습니다. 그러나 이러한 큰 수식에서는 새로운 것을 검색할 때마다 값을 변경해야 하므로 하드코딩된 값을 원하지 않습니다. 이러한 상황에서 셀 참조를 사용하여 다음과 같이 수식을 동적으로 만들 수 있습니다.

= 인덱스(A2 : D11,성냥(G2,B2:B11,0),성냥(F3,A1:D1,0))

엑셀 인덱스 매치 07


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

이제 Evelyn의 클래스를 알아야 한다고 가정해 보겠습니다. INDEX와 MATCH를 사용하여 답을 알 수 있는 방법은 무엇입니까? 주의를 기울이면 클래스 열이 이름 열의 왼쪽에 있으며 다른 Excel의 강력한 조회 기능인 VLOOKUP의 기능을 벗어난다는 것을 알 수 있습니다.

사실 왼쪽 조회 기능은 INDEX와 MATCH의 조합이 VLOOKUP보다 우월한 측면 중 하나다.

알고 에블린의 클래스, F3 셀의 값을 "Class"로 변경하고 위와 동일한 공식을 사용하기만 하면 INDEX 및 MATCH 함수가 즉시 답을 알려줍니다.

=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> 반환 A

엑셀 인덱스 매치 08

설치되어 있는 경우지도 Excel 용 Kutools, 우리 팀에서 개발한 전문 Excel 추가 기능을 사용하여 지정된 값에 대해 왼쪽 조회를 적용할 수도 있습니다. 오른쪽에서 왼쪽으로 조회 몇 번의 클릭으로 기능. 기능을 구현하려면 다음으로 이동하십시오. 쿠툴 Excel의 탭에서 찾기 공식 그룹을 클릭하고 오른쪽에서 왼쪽으로 조회 드롭다운 목록에서 슈퍼 조회. 다음과 같은 팝업 대화 상자가 표시됩니다.

엑셀 인덱스 매치 09

Excel 용 Kutools로 왼쪽 조회 기능을 적용하는 구체적인 단계를 보려면 여기를 클릭하십시오.


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

이제 동적 조회 값을 사용하여 INDEX 및 MATCH 조합 수식을 만들어 양방향 조회를 적용할 수 있습니까? 아래와 같이 G3, G4, G5 셀에 수식을 만드는 연습을 해 봅시다.

엑셀 인덱스 매치 10

답변은 다음과 같습니다.

셀 G3: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
셀 G4: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
셀 G5: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))

√ 참고: 수식을 적용한 후 G2 셀의 이름을 변경하면 모든 학생의 정보를 쉽게 얻을 수 있습니다.


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

위의 예에서 우리는 INDEX 및 MATCH 함수가 대소문자를 구분하지 않는다는 것을 알고 있습니다. 그러나 대문자와 소문자를 구별하는 수식이 필요한 경우 다음을 추가할 수 있습니다. 정확한 다음과 같이 수식에 함수를 적용합니다.

=INDEX(return_range,MATCH(TRUE,EXACT("lookup_value1",range1),0),MATCH("lookup_value2",range2,0))
√ 참고: 이것은 다음과 같이 입력해야 하는 배열 수식입니다. Ctrl + Shift + Enter. 그러면 한 쌍의 중괄호가 수식 입력줄에 표시됩니다.

예를 들어, 알고 JIMMY의 시험 점수, 다음과 같은 기능을 사용합니다.

엑셀 인덱스 매치 11

=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>> 반환 86

또는 셀 참조를 사용할 수 있습니다.

=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>> 반환 86
참고: 입력하는 것을 잊지 마십시오. Ctrl + Shift + Enter.


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

여러 열과 행 캡션이 있는 큰 데이터베이스를 처리할 때 여러 조건을 충족하는 것을 찾는 것은 항상 까다롭습니다. 이 경우 여러 기준을 조회하려면 아래 공식을 참조하십시오.

=INDEX(return_range,MATCH(1,(lookup_value1=range1)*(lookup_value2=range2)*(…),0))
참고: 이것은 다음과 같이 입력해야 하는 배열 수식입니다. Ctrl + Shift + Enter. 그러면 한 쌍의 중괄호가 수식 입력줄에 표시됩니다.

예를 들어, 인도 출신 A반 코코의 기말고사 점수, 공식은 다음과 같습니다.

엑셀 인덱스 매치 12

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> 반환 88
√ 참고: 입력하는 것을 잊지 마세요. Ctrl + Shift + Enter.

글쎄, 당신이 지속적으로 사용하는 것을 잊어 버리면 어떻게 될까요? Ctrl + Shift + Enter 수식이 잘못된 결과를 반환하도록 수식을 완성하시겠습니까? 여기에 더 복잡한 공식이 있습니다. 엔터 버튼 키 :

=INDEX(return_range,MATCH(1,INDEX((lookup_value1=range1)*(lookup_value2=range2)*(…),0,1),0))

위의 동일한 예를 찾으려면 인도 출신 A반 코코의 기말고사 점수, 평소에만 필요한 공식 엔터 버튼 히트는 다음과 같습니다.

엑셀 인덱스 매치 13

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

여기에서는 여러 기준이 있는 경우 보편적인 공식이 필요하므로 하드코딩된 값을 사용하지 않습니다. 이런 식으로 만 위의 예에서 셀 G2, G3, G4의 값을 변경하여 원하는 결과를 쉽게 얻을 수 있습니다.

Excel 용 Kutools'■ 다중 조건 조회 기능을 사용하면 몇 번의 클릭으로 여러 기준으로 특정 값을 조회할 수 있습니다. 기능을 구현하려면 다음으로 이동하십시오. 쿠툴 Excel의 탭에서 찾기 공식 그룹을 클릭하고 다중 조건 조회 드롭다운 목록에서 슈퍼 조회. 그러면 아래와 같은 팝업 대화 상자가 나타납니다.

엑셀 인덱스 매치 14

Excel 용 Kutools로 다중 조건 조회 기능을 적용하는 구체적인 단계를 보려면 여기를 클릭하십시오.


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

아래와 같이 하나의 캡션을 공유하는 다른 열이 있는 Excel 스프레드시트가 있는 경우 INDEX 및 MATCH를 사용하여 각 학생의 이름을 해당 클래스와 어떻게 일치시킬 수 있습니까?

엑셀 인덱스 매치 15

여기에서 전문 도구로 작업을 완료하는 방법을 보여 드리겠습니다. Excel 용 Kutools. 그것으로 수식 도우미, 아래 표시된 단계에 따라 학생들과 수업을 빠르게 일치시킬 수 있습니다.

1. 기능을 적용할 대상 셀을 선택합니다.

2. 아래의 쿠툴 탭으로 이동 수식 도우미클릭 수식 도우미 드롭 다운 목록에서

엑셀 인덱스 매치 16

3. 고르다 공식 유형에서 조회, 다음을 클릭하십시오. 여러 열에 대한 인덱싱 및 일치.

엑셀 인덱스 매치 17

4. 1번을 클릭 엑셀 색인 일치 아이콘오른쪽에 있는 버튼 조회_열 값을 반환하려는 셀(예: 클래스 이름)을 선택합니다. (여기서 하나의 열이나 행만 선택할 수 있습니다.)
    NS. 2번째를 클릭 엑셀 색인 일치 아이콘오른쪽에 있는 버튼 Table_rng 선택한 값과 일치하는 셀을 선택하려면 조회_열, 즉, 학생의 이름.
    씨. 3번째를 클릭 엑셀 색인 일치 아이콘오른쪽에 있는 버튼 조회 _ 값 조회할 셀, 즉 해당 클래스와 일치시키려는 학생의 이름을 선택합니다.

엑셀 인덱스 매치 18

5. 클릭 Ok, 대상 셀에 Jimmy의 클래스 이름이 표시되는 것을 볼 수 있습니다.

엑셀 인덱스 매치 19

6. 이제 채우기 핸들을 아래로 끌어 다른 학생의 수업을 채울 수 있습니다.

엑셀 인덱스 매치 20

30일 무료 평가판을 위해 Excel용 Kutools를 다운로드하려면 클릭하십시오..



  • 슈퍼 포뮬러 바 (여러 줄의 텍스트와 수식을 쉽게 편집 할 수 있습니다.) 레이아웃 읽기 (많은 수의 셀을 쉽게 읽고 편집합니다.) 필터링 된 범위에 붙여 넣기...
  • 셀 / 행 / 열 병합 및 데이터 보관; 셀 내용 분할; 중복 행과 합계 / 평균 결합... 중복 셀 방지; 범위 비교...
  • 중복 또는 고유 선택 행; 빈 행 선택 (모든 셀이 비어 있음); 슈퍼 찾기 및 퍼지 찾기 많은 통합 문서에서; 무작위 선택 ...
  • 정확한 사본 수식 참조를 변경하지 않고 여러 셀; 참조 자동 생성 여러 시트에; 글 머리 기호 삽입, 확인란 등 ...
  • 즐겨 찾기 및 빠른 수식 삽입, 범위, 차트 및 그림; 셀 암호화 암호로; 메일 링리스트 생성 이메일 보내기 ...
  • 텍스트 추출, 텍스트 추가, 위치 별 제거, 공간 제거; 페이징 부분합을 만들고 인쇄합니다. 셀 내용과 주석 간 변환...
  • 슈퍼 필터 (다른 시트에 필터 구성표 저장 및 적용) 고급 정렬 월 / 주 / 일, 빈도 등 특수 필터 굵은 기울임 꼴로 ...
  • 통합 문서와 워크 시트 결합; 키 열을 기반으로 테이블 병합; 데이터를 여러 시트로 분할; xls, xlsx 및 PDF 일괄 변환...
  • 피벗 테이블 그룹화 기준 주 번호, 요일 등 ... 잠금 해제되고 잠긴 셀 표시 다른 색상으로; 수식 / 이름이있는 셀 강조 표시...
kte 탭 201905
  • Word, Excel, PowerPoint에서 탭 편집 및 읽기 사용, Publisher, Access, Visio 및 Project.
  • 새 창이 아닌 동일한 창의 새 탭에서 여러 문서를 열고 만듭니다.
  • 생산성이 50% 증가하고 매일 수백 번의 마우스 클릭이 줄어듭니다!
officetab 하단
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.