(엑셀) MATCH, INDEX 함수 사용법 - 기본편

지난 주말에 한 독자분께서 질문을 주신 부분이 있어서 그 부분에 관련된 포스팅을 하려 합니다.

바로 MATCH와 INDEX 함수에 관해서 인데요.

굳이 이 두 함수를 한 번에 설명드리는 이유는 각자의 역할로는 그 능력(?)이 부족하기 때문입니다.

간단하게 말씀드리면 MATCH라는 함수는 지정한 테이블에서 만족하는 값의 위치(행의 상대위치)를 찾는 함수이구요.

INDEX라는 함수는 지정한 테이블에서 행과 열의 상대위치를 입력하면 값을 찾아주는 함수입니다.

두 함수의 차이는 『위치』를 찾느냐 셀 『값』을 찾느냐로 구분 됩니다.

언제나처럼 예를들어 설명드리겠습니다.
(아래 캡쳐 이미지는 포스팅 제일 아래 엑셀 파일을 첨부하였으니 참고하시면 됩니다.)

※ 참고로 VLOOKUP함수는 이전에 포스팅을 했었는데요. 혹시나 잘 모르시는 분들은 보고 오시면 비교하시는데 도움이 될 것 같습니다.

datawithnosense.tistory.com/11

(엑셀) VLOOKUP 함수와 상대참조, 절대참조

아래처럼 제품명에 해당되는 판매가격을 궁극적으로 불러오고 싶은데요. 이 경우 VLOOKUP 함수를 쓰면 쉽게 찾을 수 있습니다. 그런데 MATCH와 INDEX 함수에 관련된 포스팅이니 조금 어렵게 찾아보도록 하겠습니다. 먼저 MATCH함수를 이용해 판매가격의 행 상대위치를 찾아보겠습니다.

 

D16셀에서 함수 삽입을 통해 MATCH 함수를 삽입합니다.

 

그러면 아래와 같이 함수 인수 창이 뜨는데요.
Lookup_value는 비교할 대상인자 입니다. 여기선 다시멸치와 쥐포가 해당되겠습니다.
Lookup_array는 비교할 대상을 찾을 범위입니다. 이 범위는 사용자가 지정해야하는데요. 여기선 테이블에서 제품명 열 부분(B5:B12, 빨강 테두리로 표시)이 되겠습니다. 입력은 $(F4키)를 이용해 절대위치로 입력해주세요.
Match_type의 경우는 1, 0, -1이 존재하는데,
1은 찾을값보다 작거나 같은 값 중에서 가장 큰 값을 찾고,
0은 찾을값과 첫 번째로 정확하게 일치하는 값을 찾고, 
-1은 찾을값보다 크거나 같은 값 중 가장 작은 값을 찾습니다.
목적에 따라 다르겠지만 아무래도 0을 사용할 경우가 가장 많으며 여기서도 0을 입력합니다. (값을 입력하지 않으면 기본값이 0입니다.)

 

그러면 아래와 같이 다시멸치를 만족하는 행의 상대위치 값이 5로 찾아진 것을 확인하실 수 있습니다.
실제 제품명 열에서 B5:B12 범위에 5번째 행에 다시멸치가 위치해 있기 때문에 5가 맞습니다. 이제 이 수식을 그대로 복사해서 쥐포의 행 위치값도 찾을텐데요. 쥐포의 경우는 행 위치값이 1입니다. 그렇죠? 맞게 수식이 적용되는지 확인해보겠습니다. 여기선 D16셀을 선택한 상태에서 마우스 오른쪽키를 누른 뒤 아래로 드래그하면 아래와 같이 메뉴가 나오는데요. 여기서 『서식 없이 채우기』를 선택해주시면 됩니다. 일반적으로 마우스 왼쪽키로 드래그를 하면 서식까지 복사가 되기 때문에 아래와 같은 경우는 마우스 오른쪽키로 드래그하는게 편리합니다.

 

그러면 아래와 같이 쥐포의 상대 위치값이 1로 찾아졌음을 확인 가능합니다. MATCH가 어떤 함수인지 이해되시죠? (다만 이걸 왜 써야하는지 잘 이해가 안 갈뿐.. ← 아마도 이렇게 생각하실듯...)

 

이젠 INDEX 함수에 대해서 알아보겠습니다. 아래와 같이 E16, E17에 다시멸치, 쥐포에 해당되는 판매가격을 불러올 겁니다. 먼저 E16셀을 선택해주시고, 함수 삽입에서 INDEX를 삽입해주세요. 그러면 아래와 같이 인수 선택이라는 창이 뜹니다. 여기선 일반적으로 첫 번째 항목을 사용하는데, 두 번째 항목을 제가 다음에 정말 필요하다고 생각되면 설명드리겠습니다. 일단 여기선 첫 번째 항목을 선택하고 확인해주세요.

 

이제 함수의 인수를 입력해줘야하는데요.
Array 부분은 원하는 값이 포함된 범위를 지정합니다. 여기선 B5:F12로 아래 그림을 참고하시면 됩니다.
Row_num 부분은 말 그대로 Array에서 지정한 범위에서 원하는 값의 행 위치를 입력합니다. 여기선 다시멸치의 상대 위치를 가져올 텐데요. 다시멸치가 Array에서 지정한 범위로 보면 5번째 행에 위치하기 때문에 5를 입력합니다.
Column_num 부분도 Row_num과 마찬가지로 Array에서 지정한 범위에서 원하는 값의 행이 아닌 열 위치를 입력하면 됩니다. 여기선 다시멸치의 판매가격이 5번째 열에 위치하므로 5를 입력합니다.
그러면 어떤 값이 찾아지는지 결과값이 먼저 빨강색 밑줄처럼 보여지는데요. 16,000원이 정상적으로 찾아지는 것을 확인하실 수 있습니다.

 

다시멸치에서 썼던 방법과 동일하게 쥐포가 위치한 E17셀에도 수식을 입력하면 아래과 같습니다. 쥐포의 경우 행의 위치가 1이기 때문에 5에서 1로만 바꿔주면 됩니다. 11,000원 똑바로 출력됨을 알 수 있습니다.

 

아래가 최종 결과물입니다. 정상적으로 출력되었죠? 헌데 이 수식의 문제점은 찾고자하는 값의 행, 열 위치를 입력해줘야하기 때문에 단독 사용되기엔 큰 의미가 없습니다. 눈으로 보고 입력하는 것과 동일한 결과를 나타내기 때문이지요. 그래서 INDEX 함수는 주로 MATCH 함수와 함께 사용됩니다. MATCH 함수는 행의 위치를 찾고, INDEX 함수는 행에 위치한 값을 찾기 때문이지요. 이해가 다소 어려우실 것 같아 혼합 사용 예를 설명드립니다.

 

자 그러면 이제 MATCH와 INDEX 함수를 함께 써서 판매가격을 찾아보겠습니다. F16셀에 INDEX 함수를 삽입해주세요.

 

그리고 아래와 같이 수식을 입력합니다.
Array 부분은 B5:F12까지 절대위치로,
Row_num 부분은 원하는 대상에 따라 행의 상대위치가 변경되어야하니깐 여기서 MATCH 함수를 이용해 제품명에 해당되는 행의 상대위치를 가져옵니다. (여기선 D16에 입력한 수식을 넣으시면 됩니다.)
Column_num 부분은 판매가격 부분이기에 열의 위치 5를 입력하면 되겠습니다. 그리고 확인을 누르시면 됩니다.

 

그러면 아래 보시는 것처럼 다시멸치, 쥐포에 해당되는 판매가격이 정확하게 찾아졌음을 확인 가능 합니다. (쥐포의 판매가격인 F17셀 값의 경우 F16셀 값을 마우스 오른쪽키 드래그를 이용해 『서식 없이 채우기』로 하시면 됩니다.)

 

아마도 많은 분들께서 왜 VLOOKUP 함수를 쓰면 간단하게 해결될 문제를 MATCH랑 INDEX 함수를 써서 힘들게 해결하나 싶으실 겁니다. 이번 포스팅은 MATCH와 INDEX함수의 기본편으로 기본 기능만 말씀드렸고, 고급편을 통해서 왜 이 함수들을 쓸 수밖에 없는지 추가적으로 설명드리도록 하겠습니다.

강의자료를 첨부하였으니 참고하시고 이해가 안가는 부분은 댓글 남겨주시기 바랍니다.

 

감사합니다.

 

170619_노센스의_엑셀강의_MATCH_INDEX_함수.xlsx

0.07MB