INDEX 함수와 MATCH 함수 완벽 분석!

데이터를 효과적으로 조회하는 방법 : INDEX 함수와 MATCH 함수

엑셀에서 데이터를 조회할 때 가장 많이 사용되는 함수는 VLOOKUP과 HLOOKUP이지만 이 함수들은 왼쪽 또는 위쪽에 있는 값을 조회할 수 없는 단점이 있습니다.
이러한 단점을 보완할 수 있는 강력한 조합이 바로 INDEX + MATCH 함수입니다.
이 글에서는 INDEX 함수와 MATCH 함수의 정의, 기본 구문, 사용 방법, 예제, 주의사항등을 자세히 설명하겠습니다.

728x90

1. INDEX 함수란?

정의
지정한 데이터 범위 내에서 특정 행과 열의 위치에 있는 값을 반환하는 함수입니다.

 

기본 구문

=INDEX(array, row_num, [column_num])
array: 값을 찾을 범위
row_num: 값을 찾을 행 번호
[column_num]: (선택 사항) 값을 찾을 열 번호

특징
특정 범위에서 원하는 위치의 데이터를 찾을 수 있음
VLOOKUP과 달리 왼쪽 데이터를 조회 가능
행과 열을 조합하여 2차원 데이터에서도 검색 가능

 

사용 시 주의사항

1) 범위 지정 오류
INDEX 함수에 제공하는 범위가 실제 데이터 범위와 일치해야 합니다. 

잘못된 범위를 지정하면 오류가 발생하거나 잘못된 결과가 반환될 수 있습니다.
행 번호 또는 열 번호가 지정된 범위를 벗어나면 #REF! 오류가 발생합니다.
2) 배열 형태 이해
INDEX 함수는 배열 형태의 데이터를 처리하므로, 배열의 행과 열 인덱스를 정확히 이해해야 합니다.
특히, 다차원 배열을 다룰 때는 각 차원의 인덱스를 신중하게 지정해야 합니다.
3) 선택적 인수 활용
열 번호 인수는 선택 사항이므로, 단일 열 또는 행에서 데이터를 검색할 때는 생략할 수 있습니다.
그러나, 여러 열 또는 행이 있는 범위에서 데이터를 검색할 때는 열 번호 인수를 반드시 지정해야 합니다.

 

문제 : 사원번호 2503의 사원명을 찾으시오.

 

=INDEX(array, row_num, [column_num])에 맞게

범위(array)는 사원명을 찾아되기 때문에 C6:C11, 행 번호(row_num)는 사원번호 2503이 세번째 행이기 때문에 3

= INDEX(C6:C11,3)

=최지우

2. MATCH 함수란?

정의
지정한 값이 범위 내에서 몇 번째 위치에 있는지 반환하는 함수입니다.

 

기본 구문
=MATCH(lookup_value, lookup_array, [match_type])
lookup_value: 찾을 값
lookup_array: 찾을 값이 있는 범위
[match_type]: (선택 사항) 일치 유형 (0: 정확히 일치, 1: 작거나 같음, -1: 크거나 같음)

특징
VLOOKUP과 다르게 정확한 값뿐만 아니라 근사값도 찾을 수 있음
INDEX와 함께 사용하면 강력한 데이터 조회 기능 제공

 

사용 시 주의사항

1) 일치 유형 설정
MATCH 함수의 일치 유형 인수를 정확하게 설정해야 원하는 결과를 얻을 수 있습니다.
※ 0: 정확히 일치, 1: 작거나 같음 (오름차순 정렬 필요), -1: 크거나 같음 (내림차순 정렬 필요)
일치 유형을 잘못 설정하면 예상치 않은 결과가 반환될 수 있습니다.
2) 데이터 유형 일치
찾을 값과 찾을 범위의 데이터 유형이 일치해야 합니다. 

예를 들어, 숫자와 텍스트는 다르게 인식됩니다.
3) 정렬 상태
정확히 일치(0)를 사용하는 경우가 아니라면 데이타의 정렬상태를 확인해야 합니다. 

1과 -1 옵션은 데이타의 정렬이 전제 조건입니다.

 

문제 : 사원명 이병헌은 몇 번째 위치에 있는가?

 

=MATCH(lookup_value, lookup_array, [match_type])에 맞게

검색할 값(lookup_value)은 "이병헌", 검색할 범위(lookup_array)는 사원명내에서 찾아야 되기 때문에 C6:C11, 일치 유형([match_type])은 정확한 값을 찾아야 되기 때문에 0

=MATCH("이병헌",C6:C11,0)

=4

반응형

3. INDEX + MATCH 함수 조합

특정 조건에 따라 데이터를 동적으로 검색

MATCH로 행 또는 열 번호를 찾고 INDEX로 해당 값을 반환하는 방식입니다.

INDEX와 MATCH를 함께 사용하면 VLOOKUP보다 강력한 검색 기능을 제공합니다.

 

기본 구문
=INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), [column_num])

array: 값을 찾을 범위
lookup_value: 찾을 값
lookup_array: 찾을 값이 있는 범위
[match_type]: (선택 사항) 일치 유형 (0: 정확히 일치, 1: 작거나 같음, -1: 크거나 같음)
[column_num]: (선택 사항) 값을 찾을 열 번호

특징
1) 유연성
VLOOKUP, HLOOKUP 함수와 달리 찾을 값의 위치에 제약이 없습니다. 

즉, 찾을 값이 데이터 테이블의 가장 왼쪽 열에 있지 않아도 됩니다.
열의 삽입, 삭제가 이루어져도 수식의 변경없이 데이터가 변경된 위치에 따라 값을 정확히 찾아낼수 있습니다.
2) 양방향 검색
행과 열 모두에서 값을 찾아 데이터를 추출할 수 있습니다.
3) 다양한 조건 검색
복잡한 조건에 맞춰 데이터를 추출할 수 있습니다. 

다중 조건을 만족하는 데이터를 검색하거나 여러 조건 중 하나라도 만족하는 데이터를 검색하는 것이 가능합니다.
4) 성능
대용량 데이터에서 VLOOKUP, HLOOKUP 함수보다 성능이 뛰어납니다.
5) 오류 처리
MATCH 함수에서 찾을 값이 없을시 #N/A 오류가 발생할수 있지만 IFERROR함수를 사용하여 오류를 처리 할수 있습니다.
6) 동적 범위 설정
OFFSET 또는 COUNTA 함수와 함께 사용하여 데이터의 추가 또는 삭제에 따라 자동으로 범위를 조정할 수 있습니다.
7) 가독성
Vlookup 보다 수식이 직관적이고 이해하기 쉽습니다.

장점
데이터의 위치나 구조가 변경되더라도 수식을 수정할 필요가 거의 없습니다.
VLOOKUP, HLOOKUP 함수보다 더 다양한 상황에서 데이터를 검색하고 추출할 수 있습니다.
엑셀의 다른 기능들과 함께 사용하여 더욱 복잡하고 강력한 데이터 분석을 수행할 수 있습니다.

 

사용 시 주의사항

1) 범위 일치

INDEX 함수에서 지정한 범위와 MATCH 함수에서 기준에 맞는 위치를 찾기 위해 지정한 범위의 시작점이 동일해야 합니다.

그렇지 않으면 원하는 결과가 나오지 않을 수 있습니다.

MATCH 함수로 찾은 위치가 INDEX 함수의 범위 내에 있는지 반드시 확인해야 합니다.

범위를 벗어나면 #REF! 오류가 발생합니다.

2) 오류 처리

MATCH 함수에서 찾을 값을 찾지 못하면 #N/A 오류가 발생합니다.

IFERROR 함수를 사용하여 오류를 처리하는 것이 좋습니다.

예를 들어, =IFERROR(INDEX(범위, MATCH(찾을 값, 찾을 범위, 0)), "찾을 수 없음")과 같이 사용할 수 있습니다.

3) 성능 고려

대용량 데이터에서 MATCH 함수는 정확한 일치(0)를 사용할 때 가장 효율적입니다.

근사값을 찾는 옵션(1 또는 -1)은 데이터 정렬이 필요하며 성능이 저하될 수 있습니다.

INDEX와 MATCH함수를 배열수식으로 사용할 경우 계산속도에 영향을 줄수 있습니다.

4) 가독성

INDEX와 MATCH 함수를 중첩해서 사용하면 수식이 복잡해질 수 있습니다.

필요하다면 중간 단계를 나누거나 변수를 사용하여 수식을 단순화하는 것이 좋습니다.

특히 다른사람이 만든 엑셀파일에 적용된 INDEX MATCH 함수의 경우 수식의 작동 원리를 정확히 이해하고 사용해야 합니다.

5) 데이터 유형 일치

MATCH 함수에서 찾으려는 값과 찾으려는 범위의 데이터 형식은 일치해야 합니다.

 

문제 : D15 셀에 사원번호를 입력하면 부서를 찾아 D16에 표시하시오.

 

=INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), [column_num])에 맞게

범위(array)는 부서를 찾아야되기 때문에 D6:D11, 검색할 값(lookup_value)은 D15셀에 입력된 사원번호이기 때문에 D15, 검색할 범위(lookup_array)는 사원번호내에서 찾아야 되기 때문에 B6:B11, 일치 유형([match_type])은 정확한 값을 찾아야 되기 때문에 0

=INDEX(D6:D11,MATCH(D15,B6:B11,0))

=INDEX(D6:D11,4)

=회계팀

 

위의 문제는 VLOOKUP 함수를 사용하여도 값을 구할 수 있습니다.

=VLOOKUP(D15,B6:D11,3,FALSE)

=회계팀

 

이번에는 VLOOKUP 함수로는 구할 수 없는 문제를 풀어 보겠습니다.

 

문제 : D14 셀에 사원명를 입력하면 사원번호를 찾아 D15에 표시하시오.

 

=INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), [column_num])에 맞게

범위(array)는 사원번호 찾아야되기 때문에 B6:B11, 검색할 값(lookup_value)은 D14셀에 입력된 사원명이기 때문에 D14, 검색할 범위(lookup_array)는 사원명내에서 찾아야 되기 때문에 C6:C11, 일치 유형([match_type])은 정확한 값을 찾아야 되기 때문에 0

=INDEX(B6:B11,MATCH(D14,B6:B11,0))

=INDEX(B6:B11,2)

=2502

 

VLOOKUP 함수는 구하고자 하는 값이 오른쪽에 있을 경우에만 사용이 가능하여 위의 문제를 풀 수 없지만 INDEX+MATCH 조합 함수는 구하고자 하는 값이 왼쪽에 있을 경우에도 사용이 가능합니다.

 

4. 결론

VLOOKUP은 정렬된 데이터에서만 제대로 작동하지만 INDEX + MATCH는 정렬 여부와 무관하게 작동합니다.

INDEX와 MATCH 함수는 엑셀에서 데이터 검색을 보다 유연하고 강력하게 만들어주는 도구입니다.

이 두 함수를 잘 활용하면 복잡한 데이터 세트에서도 원하는 정보를 쉽게 찾을 수 있습니다.

실무에서 자주 사용되는 이 조합을 익히면 데이터 분석 및 관리에 큰 도움이 될 것입니다.

이제 INDEX와 MATCH 함수를 활용하여 더 효율적인 데이터 검색 기능을 사용해 보세요!