이 문서는 Microsoft Excel에서 데이터를 조회하고 추출하는 데 가장 강력하고 유연한 도구 중 하나인 INDEX 함수와 MATCH 함수를 사용하는 방법에 대한 포괄적인 가이드입니다. 이 두 함수를 함께 사용하면 기존의 VLOOKUP 함수가 가진 한계를 극복하고, 더욱 복잡하고 다양한 조회 시나리오를 처리할 수 있습니다. 각 함수의 기본 개념부터 실제 적용 예시, 그리고 INDEX/MATCH 조합의 장점까지 상세하게 다룹니다.

이 가이드를 통해 엑셀 데이터 관리 및 분석 능력을 한 단계 더 향상시킬 수 있을 것입니다.

MATCH 함수는 지정된 범위(단일 행 또는 단일 열) 내에서 특정 항목의 상대적인 위치를 찾아 반환합니다. 예를 들어, 목록에서 “사과”가 몇 번째 항목인지 알고 싶을 때 유용합니다.

• 특정 값을 찾을 범위 내에서 몇 번째 위치에 있는지 숫자로 반환합니다.
• 이 숫자는 INDEX 함수에서 행 번호나 열 번호로 사용될 수 있습니다.

MATCH 함수의 기본 구문은 다음과 같습니다.

• MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: 찾으려는 값입니다. 숫자, 텍스트, 논리값 또는 숫자에 대한 셀 참조일 수 있습니다.
• lookup_array: 값을 찾을 영역입니다. 단일 행 또는 단일 열의 범위여야 합니다.
• [match_type]: 선택 사항이며, 일치 유형을 지정합니다.
  • 0 (또는 생략): lookup_value와 정확히 일치하는 첫 번째 값을 찾습니다. lookup_array는 정렬될 필요가 없습니다.
  • 1: lookup_value보다 작거나 같은 값 중 가장 큰 값을 찾습니다. lookup_array는 오름차순으로 정렬되어야 합니다.
  • -1: lookup_value보다 크거나 같은 값 중 가장 작은 값을 찾습니다. lookup_array는 내림차순으로 정렬되어야 합니다.

다음 표에서 “바나나”가 몇 번째 위치에 있는지 찾아봅시다.

=MATCH("바나나", A2:A5, 0)

위 수식은 “바나나”가 A2:A5 범위에서 2번째 위치에 있으므로 2를 반환합니다.

INDEX 함수는 지정된 범위 또는 배열 내에서 특정 행과 열의 교차점에 있는 값을 반환합니다. 이는 MATCH 함수가 반환하는 위치 값을 활용하여 데이터를 추출할 때 매우 유용합니다.

• 주어진 배열 또는 범위에서 특정 행 번호와 열 번호에 해당하는 값을 반환합니다.
• 단일 행 또는 단일 열에서 값을 찾을 수도 있고, 2차원 범위에서 값을 찾을 수도 있습니다.

INDEX 함수는 두 가지 구문 형태를 가집니다.

• 배열 형태: INDEX(array, row_num, [column_num])
• 참조 형태: INDEX(reference, row_num, [column_num], [area_num])

일반적으로 배열 형태가 INDEX/MATCH 조합에서 사용됩니다.

• array: 값을 반환할 셀 또는 셀 범위입니다.
• row_num: array 내에서 값을 반환할 행의 번호입니다.
• [column_num]: 선택 사항이며, array 내에서 값을 반환할 열의 번호입니다. array가 단일 열인 경우 생략할 수 있습니다.
• [area_num]: 선택 사항이며, reference가 여러 범위로 구성될 때 어떤 영역에서 값을 가져올지 지정합니다. (일반적인 INDEX/MATCH 조합에서는 거의 사용되지 않습니다.)

다음 표에서 3행 2열에 있는 값을 찾아봅시다.

=INDEX(A2:C5, 3, 2)

위 수식은 A2:C5 범위에서 3행 2열에 있는 값인 200000을 반환합니다.

INDEX와 MATCH 함수를 결합하면 VLOOKUP의 단점을 보완하고 훨씬 유연한 데이터 조회를 수행할 수 있습니다. MATCH 함수로 원하는 값의 위치(행 또는 열 번호)를 찾고, 그 위치를 INDEX 함수에 전달하여 실제 데이터를 추출하는 방식입니다.

• MATCH 함수는 lookup_value가 lookup_array에서 몇 번째 위치에 있는지 반환합니다.
• INDEX 함수는 MATCH 함수가 반환한 위치를 기반으로 특정 범위에서 값을 가져옵니다.
• 기본 형태: INDEX(데이터_범위, MATCH(찾을_값, 찾을_열_범위, 0), [MATCH(찾을_열_이름, 찾을_행_범위, 0)])

다음 표에서 제품 ID로 제품명을 찾아봅시다.

우리가 찾고자 하는 제품 ID가 P002라고 가정해 봅시다.

=INDEX(B2:B5, MATCH("P002", A2:A5, 0))

• MATCH(“P002”, A2:A5, 0): A2:A5 범위에서 “P002“가 2번째 위치에 있음을 반환합니다.
• INDEX(B2:B5, 2): B2:B5 범위에서 2번째 위치에 있는 값인 ”마우스“를 반환합니다.

다음 표에서 특정 제품과 특정 월의 매출액을 조회해봅시다.

우리가 찾고자 하는 값은 ”마우스“의 ”2월” 매출액입니다.

=INDEX(B2:D4, MATCH("마우스", A2:A4, 0), MATCH("2월", B1:D1, 0))

• MATCH(“마우스”, A2:A4, 0): A2:A4 범위에서 “마우스“가 2번째 위치(행)에 있음을 반환합니다.
• MATCH(“2월”, B1:D1, 0): B1:D1 범위에서 ”2월“이 2번째 위치(열)에 있음을 반환합니다.
• INDEX(B2:D4, 2, 2): B2:D4 범위에서 2행 2열에 있는 값인 60을 반환합니다.

INDEX/MATCH 조합은 VLOOKUP 함수에 비해 여러 가지 중요한 장점을 제공합니다.

• VLOOKUP은 항상 조회하려는 값이 범위의 가장 왼쪽 열에 있어야 합니다.
• INDEX/MATCH는 MATCH 함수가 조회 값의 위치만 반환하므로, 조회하려는 열이 데이터 범위의 어느 곳에 있든 상관없이 값을 추출할 수 있습니다. 즉, 왼쪽에 있는 데이터도 조회 가능합니다.

• VLOOKUP은 수직 조회만 가능합니다.
• INDEX/MATCH는 행과 열의 MATCH 함수를 중첩하여 사용하여 수직 및 수평(양방향) 조회를 동시에 수행할 수 있습니다.

• VLOOKUP은 조회 범위 내에 새로운 열이 삽입되거나 삭제되면, 세 번째 인수인 col_index_num이 변경되어 수식이 깨질 위험이 있습니다.
• INDEX/MATCH는 MATCH 함수가 열의 이름을 기반으로 위치를 찾으므로, 중간에 열이 추가되거나 삭제되어도 수식에 영향을 주지 않습니다.

• 매우 큰 데이터셋을 다룰 때 INDEX/MATCH는 VLOOKUP보다 더 효율적인 계산 성능을 보일 수 있습니다. 이는 INDEX 함수가 전체 범위가 아닌 특정 셀만 참조하기 때문입니다.

• 배열 수식(Ctrl+Shift+Enter)과 함께 사용하거나, 추가적인 MATCH 함수를 중첩하여 여러 조건을 동시에 만족하는 데이터를 조회할 수 있습니다.

INDEX/MATCH를 사용하여 실제 시나리오에서 데이터를 조회하는 방법을 더 자세히 살펴봅니다.

고객명으로 고객 ID를 조회하는 경우처럼, 조회하려는 열이 조회 기준 열의 왼쪽에 있는 경우 INDEX/MATCH가 유용합니다.

고객명 “이영희”의 고객 ID를 찾고 싶다면:

=INDEX(A2:A4, MATCH("이영희", B2:B4, 0))

이 수식은 ”이영희“의 고객 ID인 C002를 반환합니다. VLOOKUP으로는 불가능한 조회입니다.

제품명과 색상 두 가지 조건을 만족하는 제품의 가격을 조회해봅시다.

“티셔츠”이면서 “파랑”인 제품의 가격을 찾고 싶다면, 배열 수식을 사용해야 합니다. (수식을 입력하고 Ctrl+Shift+Enter를 눌러야 합니다.)

=INDEX(C2:C5, MATCH(1, (A2:A5="티셔츠")*(B2:B5="파랑"), 0))

• (A2:A5=“티셔츠”)*(B2:B5=“파랑”): 이 부분은 배열을 생성합니다. 조건이 참이면 1, 거짓이면 0이 됩니다. 예를 들어, {1;0;0;0}과 같은 배열이 생성됩니다.
• MATCH(1, …, 0): 이 배열에서 첫 번째 1의 위치를 찾습니다.
• INDEX(C2:C5, …): 찾은 위치를 기반으로 C2:C5 범위에서 값을 반환합니다.

이 수식은 16000을 반환합니다.

MATCH 함수의 match_type을 1 또는 -1로 설정하여 근사치 일치를 수행할 수 있습니다. 예를 들어, 점수에 따른 등급을 조회할 때 유용합니다.

점수 75에 대한 등급을 조회하고 싶다면:

=INDEX(B2:B6, MATCH(75, A2:A6, 1))

• MATCH(75, A2:A6, 1): A2:A6에서 75보다 작거나 같은 값 중 가장 큰 값인 70의 위치(3번째)를 찾습니다.
• INDEX(B2:B6, 3): B2:B6에서 3번째 위치의 값인 C를 반환합니다.

INDEX 함수와 MATCH 함수는 엑셀에서 데이터를 조회하고 분석하는 데 있어 매우 강력하고 유연한 도구입니다. 이 두 함수를 함께 사용하면 VLOOKUP의 한계를 뛰어넘어 왼쪽 조회, 양방향 조회, 그리고 복잡한 다중 조건 조회까지 다양한 시나리오를 효과적으로 처리할 수 있습니다.

처음에는 구문이 다소 복잡하게 느껴질 수 있지만, 각 함수의 역할과 조합 원리를 이해하고 꾸준히 연습한다면 엑셀 데이터 처리 능력을 크게 향상시킬 수 있을 것입니다. 이 가이드에서 제시된 예제들을 직접 실습해보면서 INDEX/MATCH 조합의 진정한 힘을 경험해보시길 바랍니다.