엑셀 INDEX와 MATCH 함수 완벽 활용 가이드: VLOOKUP보다 뛰어난 조회 방법

핵심 요약

INDEX 함수는 단독으로 사용할 수 있지만, 그 안에 MATCH 함수를 중첩하면 훨씬 강력한 고급 조회 기능을 구현할 수 있습니다.
이 조합은 VLOOKUP보다 유연하며, 더 빠른 결과를 얻을 수 있습니다.

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

INDEX 함수와 MATCH 함수란?

INDEX와 MATCH는 엑셀의 대표적인 조회(lookup) 함수입니다. 두 함수는 각각 독립적으로 사용할 수도 있지만, 서로 결합하면 훨씬 더 강력한 고급 수식을 만들 수 있습니다.

INDEX 함수는 지정된 범위 내에서 특정 위치에 있는 값 또는 값의 참조를 반환합니다. 예를 들어 데이터 집합에서 두 번째 행의 값을 찾거나, 다섯 번째 행 세 번째 열에 있는 값을 가져오는 데 사용할 수 있습니다.

INDEX는 물론 단독으로도 충분히 유용하지만, MATCH 함수를 수식 안에 중첩하면 활용도가 한층 높아집니다. MATCH 함수는 지정된 셀 범위에서 특정 항목을 검색한 후, 해당 항목이 범위 내에서 몇 번째 위치에 있는지 상대적 위치를 반환합니다. 예를 들어 이름 목록에서 특정 이름이 세 번째 항목임을 확인하는 데 쓸 수 있습니다.

INDEX와 MATCH의 구문 및 인수

엑셀이 이해할 수 있도록 두 함수는 다음과 같은 형식으로 작성해야 합니다.

=INDEX(array, row_num, [column_num])

array: 수식에서 사용할 셀 범위입니다. A1:D5처럼 하나 이상의 행과 열로 구성될 수 있습니다. 필수 인수입니다.
row_num: 값을 반환할 배열 내 행 번호입니다(예: 2 또는 18). column_num이 있는 경우가 아니라면 필수입니다.
column_num: 값을 반환할 배열 내 열 번호입니다(예: 1 또는 9). 선택 사항입니다.

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value: lookup_array에서 찾으려는 값입니다. 숫자, 텍스트 또는 논리값일 수 있으며, 직접 입력하거나 셀 참조로 지정합니다. 필수 인수입니다.
lookup_array: 검색할 셀 범위입니다. A2:D2나 G1:G45처럼 단일 행 또는 단일 열이어야 합니다. 필수 인수입니다.
match_type: -1, 0, 1 중 하나를 지정하며, lookup_value를 lookup_array의 값과 어떻게 일치시킬지 결정합니다(아래 표 참조). 생략하면 기본값인 1이 적용됩니다.

match_type 선택 기준

어떤 match_type을 사용해야 할까?
match_type	기능	규칙	예시
1	lookup_value보다 작거나 같은 값 중 가장 큰 값을 찾습니다.	lookup_array의 값은 오름차순으로 정렬되어 있어야 합니다(예: -2, -1, 0, 1, 2 / A~Z / FALSE, TRUE).	lookup_value가 25인데 목록에 없으면, 그다음으로 작은 숫자(예: 22)의 위치를 반환합니다.
0	lookup_value와 정확히 일치하는 첫 번째 값을 찾습니다.	lookup_array의 값은 어떤 순서로 정렬되어 있어도 됩니다.	lookup_value가 25이면 25의 위치를 반환합니다.
-1	lookup_value보다 크거나 같은 값 중 가장 작은 값을 찾습니다.	lookup_array의 값은 내림차순으로 정렬되어 있어야 합니다(예: 2, 1, 0, -1, -2).	lookup_value가 25인데 목록에 없으면, 그다음으로 큰 숫자(예: 34)의 위치를 반환합니다.

팁: 숫자 데이터를 다루면서 근사치 조회가 허용되는 경우에는 1 또는 -1을 사용하세요. 단, match_type을 지정하지 않으면 기본값 1이 적용되므로, 정확한 일치를 원한다면 결과가 왜곡될 수 있다는 점을 유의해야 합니다.

INDEX 및 MATCH 수식 예제

두 함수를 하나의 수식으로 결합하기 전에, 먼저 각 함수가 단독으로 어떻게 작동하는지 이해해야 합니다.

INDEX 함수 예제

=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 함수 예제

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

MATCH 함수의 네 가지 간단한 예제입니다.

=MATCH("Stacy",A2:D2,0): A2:D2 범위에서 Stacy를 검색하여 결과로 3을 반환합니다.
=MATCH(14,D1:D2): D1:D2 범위에서 14를 찾지만 표에 없으므로, 14보다 작거나 같은 값 중 가장 큰 값인 13을 찾아 그 위치인 1을 반환합니다.
=MATCH(14,D1:D2,-1): 위 수식과 동일하지만, match_type이 -1이 요구하는 내림차순 정렬이 되어 있지 않아 오류가 발생합니다.
=MATCH(13,A1:D1,0): 시트의 첫 번째 행에서 13을 찾으며, 해당 배열에서 네 번째 항목이므로 4를 반환합니다.

INDEX-MATCH 결합 예제

이번에는 INDEX와 MATCH를 하나의 수식으로 결합하는 두 가지 예제를 살펴보겠습니다.

표에서 셀 참조 찾기

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

이 예제는 MATCH 수식을 INDEX 수식 안에 중첩한 것으로, 목표는 품목 번호를 이용해 품목 색상을 확인하는 것입니다.

수식이 분리된 상태("Separated" 행)를 보면 각 수식이 개별적으로 어떻게 작성되는지 알 수 있지만, 중첩했을 때 실제로 일어나는 과정은 다음과 같습니다.

MATCH(F1,A2:A5): F1 셀의 값(8795)을 A2:A5 데이터 집합에서 찾습니다. 열을 따라 내려가면 두 번째 위치에 있으므로 MATCH 함수는 2를 반환합니다.
최종적으로 찾고자 하는 값이 B열에 있으므로, INDEX의 배열은 B2:B5입니다.
MATCH가 찾은 값이 2이므로, INDEX 수식은 INDEX(B2:B5, 2, [column_num])으로 다시 쓸 수 있습니다.
column_num은 선택 사항이므로 제거하면 INDEX(B2:B5,2)가 남습니다.
결국 이것은 B2:B5에서 두 번째 항목의 값을 찾는 일반적인 INDEX 수식과 같으며, 결과는 red(빨강)입니다.

행 및 열 제목으로 조회하기

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

이 MATCH와 INDEX 예제는 양방향(two-way) 조회를 수행합니다. 목표는 Green(녹색) 품목이 May(5월)에 얼마의 매출을 올렸는지 확인하는 것입니다. 앞선 예제와 매우 비슷하지만, INDEX 안에 MATCH 수식이 하나 더 중첩되어 있습니다.

MATCH(G1,A2:A13,0): 이 수식에서 처음 계산되는 부분으로, G1 셀의 값("May")을 A2:A13에서 찾습니다. 화면에는 보이지 않지만 결과는 5입니다.
MATCH(G2,B1:E1,0): 두 번째 MATCH 수식으로, 첫 번째와 비슷하지만 G2 셀의 값("Green")을 B1:E1의 열 제목에서 찾습니다. 결과는 3입니다.
이제 INDEX 수식을 =INDEX(B2:E13,5,3)으로 다시 써서 동작을 시각화할 수 있습니다. 즉, 전체 표 B2:E13에서 다섯 번째 행, 세 번째 열의 값을 찾는 것이며, 결과는 $180입니다.

MATCH와 INDEX 사용 시 주의사항

이 함수들로 수식을 작성할 때 기억해야 할 몇 가지 사항이 있습니다.

MATCH는 대소문자를 구분하지 않으므로, 텍스트 값을 일치시킬 때 대문자와 소문자가 동일하게 처리됩니다.
MATCH는 여러 가지 이유로 #N/A 오류를 반환할 수 있습니다. match_type이 0인데 lookup_value를 찾지 못한 경우, match_type이 -1인데 lookup_array가 내림차순으로 정렬되어 있지 않은 경우, match_type이 1인데 lookup_array가 오름차순으로 정렬되어 있지 않은 경우, 그리고 lookup_array가 단일 행이나 단일 열이 아닌 경우 등입니다.
match_type이 0이고 lookup_value가 텍스트 문자열이라면 와일드카드 문자를 사용할 수 있습니다. 물음표(?)는 임의의 한 문자와 일치하고, 별표(*)는 임의의 문자열과 일치합니다(예: =MATCH("Jo*",1:1,0)). 실제 물음표나 별표 자체를 찾으려면 앞에 물결표(~)를 입력하세요.
INDEX는 row_num과 column_num이 배열 내부의 셀을 가리키지 않으면 #REF! 오류를 반환합니다.