엑셀 INDEX MATCH 함수 원하는 값 찾기에 대해서 알아보겠습니다. 엑셀을 사용하다 보면 조건을 비교하여 일치하는 값을 찾아 작업해야 할 때가 있습니다. 오늘은 INDEX MATCH 함수를 이용하여 단일 및 다중 조건에 일치하는 값을 찾는 방법에 대해서 자세히 알아보겠습니다.
1. INDEX 함수
INDEX 함수는 지정한 범위에서 상대적인 행 번호와 열 번호에 해당하는 데이터를 호출합니다.
=INDEX ( 범위, 행 번호, [열 번호] )
아래 표에서 지정한 범위에서 열은 목록에 해당하며 행은 목록에 따른 데이터가 입력되어 있습니다. 지정한 범위 내에서 상대적인 위치이므로 행, 열 번호를 입력하는데 주의해야 합니다. 범위는 표 전체를 선택하여 $B$1:$F$20에 해당하며 절대 참조를 사용하여 움직이지 않게 고정해야 합니다. 아래 그림에서 INDEX 함수를 이용하여 품명이 “사과”인 데이터를 호출하기 위해서는 “=INDEX( $B$1:$F$20, 3, 1)” 수식을 입력합니다. 절대 참조 범위로 선택하기 위해서는 범위를 선택하고 F4 키를 눌러서 열과 행을 나타내는 문자와 숫자 앞에 $표시가 붙어있으면 절대 참조로 범위가 선택된 것입니다.
2. INDEX MATCH 함수
INDEX MATCH 함수는 앞서 살펴본 INDEX 함수에 행 번호, 열 번호에 해당하는 값을 MATCH 함수로 호출하게 됩니다.
MATCH 함수는 찾을 값을 지정하고 찾을 값이 위치한 범위를 지정합니다. 그다음 일치 옵션을 선택하면 범위 내에서 상대적인 위치를 반환하는 함수입니다. 이때 반환하는 값은 행, 또는 열이 되는데 그 이유는 범위를 행으로 지정하면 상대적인 행의 위치를 알려주고, 범위를 열로 지정하면 상대적인 열의 위치를 알려주기 때문입니다. 이렇게 상대적인 열과 행 위치를 INDEX 함수와 결합하면 조건에 맞는 데이터를 찾을 수 있습니다. VLOOKUP 함수보다 데이터의 형태에서 자유롭고 조건에 맞는 데이터를 찾기 쉬워 데이터 작업에 유용하게 사용되는 함수입니다. MATCH 함수에 대한 자세한 내용은 아래 포스팅을 참고하시기 바랍니다.
=MATCH ( 찾을 값, 범위, [일치 옵션] )
1) INDEX MATCH – 단일 조건에 맞는 데이터 찾기
찾고자 하는 데이터의 유형에서 조건에 해당하는 부분이 하나일 때 사용할 수 있는 수식입니다. 수식의 구성은 다음과 같으며 수식 작성 후에는 CTRL + SHIFT + ENTER를 입력하여 배열 수식으로 입력해주어야 합니다. 만약 조건에 해당하는 값이 결괏값이 중복될 경우 가장 가까운 위치에 값을 호출하게 됩니다. 중복된 값을 모두 표기하는 방법은 아래 INDEX SMALL 함수를 참고하시기 바랍니다.
{=INDEX( $범위, MATCH( 찾을 값, $찾을 값의 범위, 일치옵션), 열 번호)}
1) 범위 : 데이터 행, 열의 전체 범위, *절대 참조 사용*
2) 찾을 값 : MATCH 함수를 이용하여 찾을 값
3) 찾을 값의 범위 : 찾을 값이 위치한 데이터 범위이며 절대 참조로 지정합니다.
4) 일치 옵션 :
① -1 = 크거나 같은 값 중에서 가장 가까운 위칫값
② 0 = 정확하게 일치한 값
③ 1 = 작거나 같은 값 중에서 가장 가까운 위칫값
5) 열 번호 : INDEX 함수에 사용된 범위의 상대적 열 번호
2) INDEX MATCH – 다중 조건에 맞는 데이터 찾기
다중 조건일 때 데이터를 찾기 위한 INDEX MATCH 함수입니다. 수식의 구성은 다음과 같습니다.
{=INDEX($범위,MATCH(1,(조건1=$조건 범위1)*(조건2=$조건 범위2)*
(조건3=$조건 범위3),0), 열 번호)}
다중 조건에 해당하는 값을 찾기 위해 MATCH 들어가는 데이터가 복잡하게 바뀌었습니다. INDEX 행에 필요한 데이터가 MATCH 함수를 통해 호출됩니다. MATCH 함수의 조건과 조건 범위가 일치하면 1을 반환하게 되며, 이때 1을 반환한 데이터의 상대적인 위치를 호출합니다. 조건을 처리하는 부분이 추가되어 복잡해 보이지만 수식이 동작하는 방식은 같습니다.
아래 예시는 좌측에 “범위”에 해당하는 표가 있으며 우측은 조건에 해당하는 브랜드, 메뉴, 사이즈가 있고 해당하는 조건에 일치하는 “가격”을 찾습니다. 수식을 작성하고 CTRL + SHIFT + ENTER를 눌러 배열 수식으로 입력해야 정상 작동합니다.
3) INDEX SMALL – 조건에 맞는 모든 결괏값 찾기
조건에 맞는 모든 결괏값을 찾기 위한 방법으로 INDEX & SMALL 함수를 이용하여 결괏값을 찾아보겠습니다. 아래 표와 같이 브랜드에 따른 커피 메뉴의 값을 비교하는 데이터에서 메뉴와 사이즈가 일치하는 조건에 결괏값이 2개가 존재합니다. 이렇게 조건이 같은 모든 결괏값을 나열할 때 유용하게 사용할 수 있습니다. 수식 구성은 아래와 같습니다.
{=INDEX($범위,SMALL(IF((조건1=조건 범위1)*(조건2=조건 범위2),ROW(결과값 범위)),ROWS($A$1:A1)),열 번호)}
마치며
오늘은 INDEX MATCH 함수를 이용하여 단일, 다중 조건에 해당하는 값과 중복된 결괏값을 찾는 방법을 알아보았습니다. INDEX 함수의 상대적 위치에 해당하는 값을 불러오기 위한 조건으로 MATCH, SMALL 함수를 사용했습니다.
① INDEX 함수는 지정 범위의 행, 열 번호를 입력하면 상대적 위치의 데이터를 호출한다.
② INDEX 함수의 행, 열 번호에 찾는 조건을 입력하기 위해 MATCH, SMALL 함수를 사용한다.
③ 범위를 지정할 때는 절대 참조를 사용한다.
④ 수식 입력을 마치고 CTRL + SHIFT + ENTER를 입력한다.