Excel에서 INDEX 및 MATCH 기능을 사용하는 방법

알아야 할 사항

INDEX 함수는 단독으로 사용할 수 있지만 내부에 MATCH 함수를 중첩하면 고급 조회가 생성됩니다.
이 중첩 함수는 VLOOKUP보다 유연하며 더 빠르게 결과를 얻을 수 있습니다.

이 문서에서는 Excel 2019 및 Microsoft 365를 포함한 모든 버전의 Excel에서 INDEX 및 MATCH 함수를 함께 사용하는 방법을 설명합니다.

INDEX, MATCH 기능이란?

INDEX 및 MATCH는 Excel 조회 기능입니다. 이 두 함수는 완전히 별개의 기능으로 자체적으로 사용할 수 있지만 결합하여 고급 수식을 만들 수도 있습니다.

INDEX 함수는 특정 선택 내에서 값 또는 값에 대한 참조를 반환합니다. 예를 들어, 데이터 세트의 두 번째 행이나 다섯 번째 행과 세 번째 열에서 값을 찾는 데 사용할 수 있습니다.

INDEX는 단독으로 사용할 수 있지만 수식에 MATCH를 중첩하면 좀 더 유용합니다. MATCH 함수는 셀 범위에서 지정된 항목을 검색한 다음 범위에서 항목의 상대 위치를 반환합니다. 예를 들어, 특정 이름이 이름 목록의 세 번째 항목인지 확인하는 데 사용할 수 있습니다.

INDEX 및 MATCH 구문 및 인수

Excel에서 두 함수를 모두 이해하려면 다음과 같이 작성해야 합니다.

=INDEX (배열 , row_num , [column_num ])

배열 수식이 사용할 셀 범위입니다. A1:D5와 같이 하나 이상의 행과 열이 될 수 있습니다. 필수입니다.
row_num 2 또는 18과 같은 값을 반환할 배열의 행입니다. column_num이 아닌 경우 필수 항목입니다. 있습니다.
column_num 1 또는 9와 같은 값을 반환할 배열의 열입니다. 선택 사항입니다.

=매치 (lookup_value , lookup_array , [일치 유형 ])

lookup_value lookup_array에서 일치시키려는 값입니다. . 수동으로 입력하거나 셀 참조를 통해 참조되는 숫자, 텍스트 또는 논리값일 수 있습니다. 필수 항목입니다.
lookup_array 살펴볼 셀의 범위입니다. A2:D2 또는 G1:G45와 같이 단일 행 또는 단일 열일 수 있습니다. 필수 항목입니다.
일치 유형 -1일 수 있습니다. , 0 , 또는 1 . lookup_value 방법을 지정합니다. lookup_array의 값과 일치합니다. (아래 참조). 1 이 인수가 생략된 경우 기본값입니다.

사용할 일치 유형 검색 유형 기능 규칙 예 1lookup_value보다 작거나 같은 가장 큰 값을 찾습니다. . lookup_array 값은 오름차순으로 배치해야 합니다(예:-2, -1, 0, 1, 2 또는 A-Z, 또는 FALSE, TRUE.lookup_value 25이지만 lookup_array에서 누락되었습니다. , 따라서 22와 같이 다음으로 가장 작은 숫자의 위치가 대신 반환됩니다.0lookup_value와 정확히 같은 첫 번째 값을 찾습니다. . lookup_array 값의 순서는 상관없습니다.lookup_value 25이므로 25의 위치를 반환합니다.-1lookup_value보다 크거나 같은 가장 작은 값을 찾습니다. .lookup_array 값은 내림차순으로 배치해야 합니다(예:2, 1, 0, -1, -2).lookup_value 25이지만 lookup_array에서 누락되었습니다. , 따라서 34와 같이 다음으로 큰 숫자의 위치가 대신 반환됩니다.

1 사용 또는 -1 숫자를 다룰 때나 근사치가 괜찮은 경우와 같이 척도를 따라 대략적인 조회를 실행해야 하는 경우. 하지만 match_type을 지정하지 않으면 , 1 정확히 일치를 원하는 경우 결과가 왜곡될 수 있습니다.

INDEX 및 MATCH 공식의 예

INDEX와 MATCH를 하나의 공식으로 결합하는 방법을 살펴보기 전에 이러한 기능이 자체적으로 어떻게 작동하는지 이해해야 합니다.

색인 예

=INDEX(A1:B2,2,2)
=INDEX(A1:B1,1)
=INDEX(2:2,1)
=INDEX(B1:B2,1)

이 첫 번째 예에는 서로 다른 값을 가져오는 데 사용할 수 있는 네 가지 INDEX 공식이 있습니다.

=INDEX(A1:B2,2,2) A1:B2를 살펴보고 두 번째 열과 두 번째 행(Stacy)에서 값을 찾습니다. .
=INDEX(A1:B1,1) A1:B1을 살펴보고 첫 번째 열인 Jon에서 값을 찾습니다. .
=INDEX(2:2,1) 두 번째 행의 모든 항목을 살펴보고 첫 번째 열인 Tim.의 값을 찾습니다.
=INDEX(B1:B2,1) B1:B2를 통해 Amy인 첫 번째 행의 값을 찾습니다. .

매치 예시

=MATCH("Stacy",A2:D2,0)
=MATCH(14,D1:D2)
=MATCH(14,D1:D2,-1)
=MATCH(13,A1:D1,0)

다음은 MATCH 기능의 네 가지 쉬운 예입니다.

=MATCH("스테이시",A2:D2,0) 스테이시를 찾고 있습니다. A2:D2 범위 내 3을 반환합니다. 결과적으로.
=MATCH(14,D1:D2) 14을(를) 찾고 있습니다. D1:D2 범위 내 , 하지만 테이블에서 찾을 수 없기 때문에 MATCH는 다음 을 찾습니다. 14보다 작거나 같은 가장 큰 값 , 이 경우 13 , 위치 1 에 있음 lookup_array의 .
=MATCH(14,D1:D2,-1) 위의 수식과 동일하지만 배열이 -1과 같이 내림차순이 아니기 때문에 필요합니다. 오류가 발생합니다.
=MATCH(13,A1:D1,0) 13을(를) 찾고 있습니다. 시트의 첫 번째 행에서 4를 반환합니다. 이 배열의 네 번째 항목이기 때문입니다.

INDEX-MATCH 예

다음은 하나의 공식으로 INDEX와 MATCH를 결합할 수 있는 두 가지 예입니다.

표에서 셀 참조 찾기

=INDEX(B2:B5,MATCH(F1,A2:A5))

이 예는 INDEX 수식 내에 MATCH 수식을 중첩합니다. 항목 번호를 사용하여 항목 색상을 식별하는 것이 목표입니다.

이미지를 보면 "구분된" 행에서 수식이 자체적으로 작성되는 방식을 볼 수 있지만, 우리가 그것들을 중첩하고 있기 때문에 다음과 같은 일이 일어나고 있습니다.

매치(F1,A2:A5) F1을 찾고 있습니다. A2:A5 데이터 세트의 값(8795) . 열을 세어보면 2임을 알 수 있습니다. , 그래서 MATCH 함수가 방금 알아낸 것입니다.
INDEX 배열은 B2:B5입니다. 궁극적으로 해당 열에서 값을 찾고 있기 때문입니다.
이제 2 이후로 INDEX 함수를 다음과 같이 다시 작성할 수 있습니다. MATCH에서 찾은 항목:INDEX(B2:B5, 2, [column_num]) .
column_num 이후 선택 사항이며 다음과 같이 남겨둘 수 있습니다. INDEX(B2:B5,2) .
이제는 B2:B5에서 두 번째 항목의 값을 찾는 일반적인 INDEX 공식과 같습니다. , 빨간색 .

행 및 열 제목으로 조회

=INDEX(B2:E13,MATCH(G1,A2:A13,0),MATCH(G2,B1:E1,0))

이 MATCH 및 INDEX 예에서는 양방향 조회를 수행합니다. 아이디어는 Green으로 얼마나 많은 돈을 벌었는지 확인하는 것입니다. 5월 항목 . 위의 예와 매우 유사하지만 INDEX에 추가 MATCH 공식이 중첩되어 있습니다.

매치(G1,A2:A13,0) 이 공식에서 해결된 첫 번째 항목입니다. G1 을(를) 찾고 있습니다. (단어 "5월") A2:A13 특정 값을 얻으려면. 여기에서는 보이지 않지만 5입니다. .
매치(G2,B1:E1,0) 두 번째 MATCH 공식이며 첫 번째와 매우 유사하지만 대신 G2를 찾고 있습니다. (단어 "녹색") B1:E1 열 제목 . 이것은 3으로 해결됩니다. .
이제 무슨 일이 일어나고 있는지 시각화하기 위해 다음과 같이 INDEX 공식을 다시 작성할 수 있습니다. =INDEX(B2:E13,5,3) . 이것은 전체 테이블에서 찾고 있습니다. B2:E13 , 다섯 번째 행과 세 번째 열에 대해 $180 반환 .

매치 및 인덱스 규칙

다음 함수로 수식을 작성할 때 염두에 두어야 할 몇 가지 사항이 있습니다.

MATCH는 대소문자를 구분하지 않으므로 텍스트 값을 일치시킬 때 대문자와 소문자가 동일하게 처리됩니다.
MATCH는 #N/A 를 반환합니다. 여러 가지 이유로:match_type인 경우 0입니다. 및 lookup_value match_type 인 경우 찾을 수 없습니다. -1입니다. 및 lookup_array match_type인 경우 내림차순이 아닙니다. 1입니다. 및 lookup_array 오름차순이 아니며 lookup_array인 경우 단일 행이나 열이 아닙니다.
lookup_value에서 와일드카드 문자를 사용할 수 있습니다. match_type 인 경우 인수 0입니다. 및 lookup_value 텍스트 문자열입니다. 물음표는 단일 문자와 일치하고 별표는 모든 문자 시퀀스와 일치합니다(예:=MATCH("Jo*",1:1,0) ). MATCH를 사용하여 실제 물음표나 별표를 찾으려면 ~ 먼저 입력하세요.
INDEX는 #REF!를 반환합니다. row_num인 경우 및 column_num 배열 내의 셀을 가리키지 마십시오.