엑셀 INDEX MATCH 함수 정의 및 활용 (VLOOKUP 대체 함수)

엑셀 INDEX MATCH 함수는 2개의 별개 함수이지만, 늘 VLOOKUP 대체 함수를 대표하는 함수로 함께 불려지고 있습니다. LOOKUP 함수 중 활용도와 응용도는 INDEX MATCH 함수가 가장 효율적이라고 할 수 있습니다. 기능적인 활용도가 높은 INDEX MATCH의 함수인만큼 기본 정의뿐만 아니라 활용하는 방법에 대해 알아보도록 하겠습니다. 

INDEX MATCH 함수 정의

INDEX와 MATCH함수는 별개인 만큼 각각의 정의를 알아보도록 하겠습니다.

INDEX 함수 정의

INDEX 함수란 지정한 데이터 범위내의 행, 열의 위치를 입력하였을 때 해당 값을 보여주는 함수입니다.

 

=INDEX(데이터범위, 행위치, 열위치)

 

INDEX 함수를 사용하다 보면 행, 열 위치번호에 대해 순서가 헷갈릴 때가 있습니다. 그럴 땐 "니은"을 기억해 주시면 쉽게 순서를 기억하실 수 있을 겁니다.

 

아래 데이터의 F2열에 INDEX 함수를 활용하여 판매수량 1,026을 출력하였습니다.

 

=INDEX(A2:D11,2,3)

 

INDEX 함수를 활용하여 지정한 위치의 값을 산출 하였습니다.

MATCH 함수 정의

MATCH 함수는 VLOOKUP과도 함께 사용되는 함수로, 기준값에 대한 지정한 범위 내에서의 위치를 숫자로 보여주는 함수입니다. 

 

=MATCH(기준값, 범위, 0)

 

행과 열에, 즉 가로값, 세로값에 상관없이 사용 가능한 함수입니다. 

 

아래 데이터의 F4열에 MATCH 함수를 활용하여 2의 숫자를 출력하였습니다.

 

=MATCH(F2, C2:C11,0)

 

MATCH 함수를 사용하여 해당값의 위치를 숫자로 출력하였습니다.

MATCH함수와 VLOOKUP의 조합은 아래 링크에서 확인 가능하십니다.

엑셀 VLOOKUP 다중결과 다중조건 해결방법

 

INDEX MATCH 함수 활용

INDEX MATCH의 함수는 INDEX와 MATCH의 기능을 아는 것만으로 활용하기 어렵습니다.

활용의 기본적인 함수 구문은 아래와 같습니다.

 

=INDEX(데이터범위, MATCH(기준값, 범위, 0), MATCH(기준값, 범위, 0))

 

INDEX 함수의 행과 열의 위치 값을 가져오는 함수로 MATCH 함수를 사용하였습니다.

INDEX MATCH 함수의 의미

INDEX MATCH 함수의 의미는 LOOKUP 함수의 기능을 최대한 사용할 수 있다는 것입니다. 그 의미의 함수가 실무에서 어떻게 쓰일 수 있는지 확인해 본다면, "원본 데이터의 형태를 행과 열의 한계 없이 변경하여 새로운 데이터를 만들 수 있다"입니다.

INDEX MATCH 함수의 활용

INDEX MATCH 함수의 실제 활용을 살펴보도록 하겠습니다. 아래의 그림과 같이 원본데이터를 새로운 형태로 변경하여 원하는 데이터를 얻을 수 있습니다.

A열부터 D열까지는 데이터의 정리가 세로로 되어 있는 원본데이터입니다.

해당 데이터를 홍길동이라는 판매자를 기준으로 날짜별로 판매한 상품과 판매수량에 대해 새로운 형태의 데이터 테이블을 만들고자 한다면 G5 셀에 사용되는 함수는 아래와 같습니다. 

 

=INDEX($A$2:$D$11, MATCH(G$4,$D$2:$D$11,0), MATCH($F5,$A$1:$D$1,0))

 

절대참조를 활용하여 한 번의 수식 생성으로 원하는 데이터 전체를 만들 수 있도록 하였습니다. 

INDEX MATCH 함수를 사용하여 원본데이터에서 새로운 데이터를 출력하였습니다.

VLOOKUP의 업그레이드 버전 혹은 대체 함수로 잘 알려져 있는 INDEX MATCH 함수는 위에서 본 것과 같이, 원본데이터의 변경을 자유롭게 할 수 있다는 장점을 갖고 있습니다. 그뿐만 아니라 다중조건, 다중결과에서도 INDEX MATCH 함수를 많이 활용하고 있습니다.