[엑셀함수] INDEX-MATCH : 표에서 원하는 값 찾기 함수

표에서 원하는 값을 찾는 함수는 이전에 소개해드렸던 각종 룩업 함수가 있었죠.

VLOOKUP, HLOOKUP, LOOKUP, XLOOKUP 등 표의 모양에 따라 다양하게 사용하는 Lookup함수입니다.

 

https://real-excel.tistory.com/3

[엑셀함수] 찾고자 하는 값을 불러오는 VLOOKUP함수(HLOOKUP함수)

https://real-excel.tistory.com/27

[엑셀함수] V/Hlookup의 Next Level, XLOOKUP함수

 

 

다만, 이 함수는 한 가지 문제점이 있습니다.

 

그것은 바로, 내가 알고 있는 값! 'Lookup_Value'의 값이 무조건 표의 왼쪽에 있어야 하며, 그 값을 이용해 찾고자 하는 값(반환 값)이 무조건 같은 표에서 Lookup_value의 오른쪽에 있어야만 한다는 것입니다.

 

하지만 실무에서는 결코 데이터가 호락호락하게 되어있지는 않죠^^심지어는 찾는 값(Lookup_value)이 표 안에 없기도 합니다. 너무 당황스럽죠.

 

이럴 때, 룩업 함수 대신 사용할 수 있는 INDEX-MATCH함수입니다.

 

---

우선 INDEX라는 함수부터 살펴봅니다.

 

'인덱스'라는 단어로 알 수 있다시피 이 함수는 조회 함수입니다.우리가 책에서 원하는 내용을 찾고 싶을 때 앞 장의 목차(Index)를 보고 제목을 보고 해당하는 페이지로 바로 넘기잖아요? 그것과 같은 맥락의 함수입니다.

 

 

[A1:B20]에 있는 표를 제가 알아보기 쉽게 [국가번호]라는 이름으로 표 이름을 설정해놨습니다.

여기서 대한민국의 국가번호를 INDEX 함수를 이용하여 찾아보겠습니다.

 

함수 인수는 3가지입니다.

Array	필수값	내가 조회하고자 하는 전체를 선택합니다. '표' 일수도, 아닐 수도 있겠죠?
Row_num	필수 or 선택	행 번호를 뜻합니다. Array로 지정한 범위 내에서의 행 번호입니다.	행이 1개 뿐일때는 Column_num가 필수 열이 1개 뿐일때는 Row_num가 필수가 됩니다.
Column_num	선택 or 필수	열 번호를 뜻합니다. Array로 지정한 범위 내에서의 열 번호입니다.

 

우리가 보고 있는 표 [국가번호]는 행이 20개, 열이 2개이기 때문에 Row_num, Column_num 인수를 모두 지정해줘야 하겠죠. 그래서 숫자로 지정을 해줬습니다.

 

=INDEX(국가번호[#모두],14,1) [국가번호]라는 이름을 가진 표에서 14번째 행, 1번째 열에 있는 데이터를 반환하시오.

라는 뜻의 명령이 되겠습니다.

 

그래서 대한민국의 국가번호를 찾긴 찾았는데... 이렇게 일일이 표에서 숫자 세고 적어주는 건 무리가 있겠죠. 이러고 있으면 부장님께 혼날 겁니다. (경험담 아닙니다.)

 

'표에서 인덱스를 하는데, 행 번호/열 번호 모르겠고 그냥 대한민국의 국가번호를 찾을 순 없나요?'

 

이래서 INDEX 함수는 별개로 사용하지 않고 대부분 MATCH함수와 항상 짝을 이뤄 사용이 됩니다. 그러니 그냥 세트라고 생각하세요!

 

---

일단 MATCH함수가 어떤 역할을 하는지 살펴볼게요.

 

 

함수 인수들이 익숙하네요. 룩업 함수에서 보던 인수들이라 어렵지 않게 사용하실 수 있을 거예요.

 

Lookup_value	필수	찾고자 하는 값, 그러니까 내가 지금 알고 있는 값을 넣어줍니다.
Lookup_array	필수	위의 값을 찾을 표, 혹은 배열입니다.
Match_type	선택	정확한 값을 찾을 것인지, 혹시 그게 숫자라면 근사치를 찾을 것인지를 설정합니다. 기본값은 '유사한 값'을 찾는 1입니다. *웬만하면 정확한 값을 찾는 0을 지정하세요*

 

 

룩업 밸류를 저는 "대한민국"이라고 타이핑을 했지만 대한민국이 써져있는 셀 번호를 넣으셔도 됩니다.

 

=MATCH("대한민국",B1:B20,0) "대한민국"이라는 정확한 단어가 B1:B20의 범위에서 어디에 위치해있는지 찾으시오.

 

여기서 한 가지 주의할 점은 3번째 인수인 Match_type이 선택 값이긴 하나 웬만하면 0으로 지정하시는 게 좋습니다.

 

 

왜냐하면, 우리가 보기에는 "대한민국"과 비슷한 단어는 눈 씻고 찾아봐도 없지만 컴퓨터의 언어로는 "대한민국"과 "뉴질랜드"가 비슷해 보이나 봅니다...(?)

 

이런 종류의 엑셀 오류에 대해서는 나중에 설명하는 시간을 갖도록 하겠습니다.

 

---

 

자, 다시 본론으로 돌아가서 매치 함수를 쓰면 결괏값이 숫자로 나온다는 특징을 발견하셨나요?그리고 인덱스 함수에서 2번째, 3번째 인수에 숫자를 써야 한다는 사실도 기억나시나요?

 

그래서 Index와 Match함수가 세트로 쓰일 수 있는 겁니다.

 

 

주요 국가번호가 나열되어있는 표를 이용해서 대한민국, 필리핀, 홍콩의 국가번호만을 추출해볼게요.

 

 

*[국가번호]라는 표의 이름이 헷갈릴 것 같아 셀 주소로 풀어서 작성했어요*

=INDEX(A1:B20, MATCH(E5,B1:B20,0),1) 1. A1:B20의 범위에서 몇 번째 열?? 과 1번째 행에 있는 값을 반환하시오.

MATCH(E5,B1:B20,0) 2. 열번호는 E5셀에 적혀있는 정확한 단어를 B1:B20의 범위에서 몇번째 위치에 있는지 번호로 반환하시오.

 

이렇게 복잡해 보이지만 해석해보면 생각보다 간단한 수식이 완성이 되었습니다.아까처럼 "대한민국"을 직접 쓰지 않고 셀을 선택했고, A1:B20과 같은 범위들을 절대 참조로 고정시키면 필리핀과 홍콩의 국가번호는 마우스 클릭만으로도 구할 수 있겠죠?

 

이 절대 참조와 상대 참조를 잘 활용하면 같은 수식을 여러 번 쓰지 않고 자유자재로 복사를 할 수 있죠.

 

여러분들도 필리핀과 홍콩의 국가번호를 Index-match함수를 이용해 구해보시고, 혹시 vlookup함수로도 구해보려고 애써보세요! 그러면 왜 이런 형식의 표에서 룩업 함수가 아닌 인덱스 함수를 대신 사용하였는지를 단박에 알게 되실 겁니다.

 

질문, 궁금한 점, 수식을 짜다가 막히는 점은 언제든 댓글 주세요!

 

이상, 엑셀 잘하는 김쌤이었습니다^^