엑셀 INDEX MATCH 함수 쓰는 방법

실무에서 가장 많이 쓰이는 함수를 하나 꼽으라면 단연코 vlookup 함수가 1등일 것 입니다. 그런데 vlookup 함수는 한계가 있습니다. 조건이 두개 일 경우 임의로 자료를 가공해야하는 불편함이 있습니다. 이와 관련된 내용은 링크를 참조하시기 바랍니다. 이런 불편함을 해결할 수 있는 방법이 index, match 함수를 쓰는 것 입니다. 엑셀 초보분들은 다소 생소한 함수 일수도 있는데요. 엑셀 중급 이상 실력이 되려면 반드시 알아야하는 함수이고, 잘 쓰면 실무에 상당한 도움이 되는 함수이기 때문에 꼭 알아두시기 바랍니다.

 

그럼 본격적으로 INDEX MATCH 함수에 대해 알아보겠습니다. INDEX MATCH 함수는 하나의 함수가 아니라 INDEX 함수와 MATCH 함수를 중첩해서 사용하는 것입니다. INDEX MATCH 함수라고 말하는 이유는 두 함수는 같이 쓰였을 때 시너지 효과가 나기 때문입니다. 그럼 각각 함수에 대해 먼저 알아본 후 두 함수를 중첩해서 사용하는 법을 알아보겠습니다.

 

먼저 INDEX 함수를 알아보겠습니다.. INDEX 함수는 내가 지정한 범위 내에서 지정한 위치에 있는 값을 가져오는 함수입니다. 말로만 들어서는 이해가 쉽지 않기 때문에 예시를 통해 보겠습니다.

INDEX 함수 예시

위 예시에서 INDEX 함수를 활용해 A아파트의 가격인 5억을 가져와보겠습니다.

INDEX 함수 마법사

INDEX 함수는 총 세 개 인수를 입력해야 합니다.

 

1. array : 셀 범위입니다. $C$8:$D$9로 지정했습니다. 범위이므로 절댓값($)을 넣어줘야 합니다.

2. row_num : 셀 범위에서 행 위치. A아파트 행에 있어야 하므로 1 입력.

3. colomn_num : 셀 범위에서 열 위치. 5억이 범위 내에서 두 번째 열에 있으므로 2 입력.

 

즉, C8:D9 범위 내에서 첫 번째 행의 두 번째 열에 위치해 있는 값을 가져온다는 것입니다.

이해를 돕기 위해 영상으로 한번 더 보시겠습니다.

INDEX 함수 동영상

 

이제 INDEX 함수가 이해되셨나요? 이해하셨다면 한 가지 의문이 들어야합니다. 지금 예시는 데이터 양이 적어 위치를 금방 찾아낼 수 있고 시간이 많이 소요되지 않지만 데이터 양이 많을 경우 일일이 다 위치를 지정하는 것이 굉장히 비효율적이다는 것입니다.

 

그래서 필요한 함수가 MATCH 함수입니다. 왜냐하면 MATCH 함수는 범위 내에 내가 원하는 값이 몇 번째에 위치해 있는지 알려주는 함수이기 때문입니다. 이제 왜 두 함수가 찰떡궁합인지 조금 아시겠나요?

 

먼저 MATCH 함수를 알아보고 두 함수를 중첩해서 사용해 보겠습니다.

 

아래 예시는 재질, 두께별 단가 자료입니다.

MATCH 함수 예시

 

MATCH 함수를 이용해 오른쪽 주황색 음영 부분에 재질별 두께별 행/열 번호를 구해보겠습니다.

MATCH 함수 인수

 

MATCH 함수는 3가지 인수를 입력해야 합니다.

 

1. lookup_value : 찾으려는 값입니다. G9로 잡아줍니다.

2. lookup_array : 찾으려는 값의 셀 범위입니다. $B$4:$B$6으로 잡아줍니다. 범위이므로 절댓값($)을 적용합니다.

3. match_type : 값을 표시하는 방법입니다. 1,0,-1 중 입력합니다. 정확히 일치하는 값을 찾아야 하므로 0을 입력합니다.

 

위와 같이 내가 원하는 값이 지정한 범위에서 몇 번째 위치에 있는지 숫자로 나타내 줍니다.

 

이해를 돕기 위해 영상으로 보여드리겠습니다.

 

MATCH 함수 동영상

INDEX와 MATCH 함수 각각에 대해 알아보았습니다. 그럼 이제 이 둘을 어떻게 조합해야 할까요? 두 함수를 제대로 이해하셨다면 바로 적용이 가능합니다.

 

INDEX 함수의 두 번째, 세 번째 인수인 row_num와 colomn_num에 직접 숫자를 입력하는 대신 MATCH 함수를 넣어주면 됩니다. MATCH 함수가 행/열의 위치를 산출해주고 그 위치에 있는 값을 INDEX 함수가 추출하는 것입니다.

 

실제 어떻게 쓰는지 보도록 하겠습니다.

방금 전 예시에서 재질과 두께별 단가를 왼쪽 표에서 가져와보겠습니다.

INDEX MATCH 중첩

각 함수에 대한 설명은 위 내용과 중복되므로 하지 않겠습니다. 다만 유의하실 점이 위 수식에서 절댓값 고정 처리된 것들이 있는데 범위를 지정해줄 때는 반드시 절댓값을 걸어줘야 합니다.

 

이해를 돕기 위해 영상으로 한번 더 보여드리겠습니다.

 

INDEX MATCH 함수 동영상

 

오늘은 INDEX MATCH 함수에 대해 알아보았습니다. 엑셀 함수 중 난이도가 높은 쪽에 속하므로 처음에 이해하시기 어려울 수도 있습니다. 각각의 함수의 개념을 먼저 이해하시고 합쳐서 쓰는 방법만 익히시면 현업에서도 충분히 응용해서 활용이 가능할 것입니다.

 

이해가 안 되시는 부분은 질문해주시면 성심성의 껏 답변드리겠습니다.

 

감사합니다.