엑셀 INDEX MATCH 함수 사용방법에 관해 알아보는 글입니다. VLOOKUP 함수 사용이 안 될 때 INDEX 함수 및 MATCH 함수를 조합해서 조건에 맞는 셀 값을 가져올 수 있습니다. 지금부터 INDEX MATCH 함수 사용법을 알아봅니다.
엑셀 연습 파일을 다운로드 받아서 연습을 해보면 이해가 훨씬 쉽습니다.
VLOOKUP 함수 한계
우리가 조건에 맞는 값을 데이터 범위에서 찾아올 때 VLOOKUP 이라는 함수를 사용합니다. 그런데 VLOOKUP 함수의 경우 우리가 찾으려는 값이 반드시 왼쪽의 첫 번째 열에 위치해 있어야 합니다.
위 사진처럼 ‘제품코드’를 기준으로 값을 찾는다고 해보겠습니다. 근데 ‘제품코드’ 열이 C열, 즉 세 번째 열에 위치해 있습니다. 따라서 이 경우 VLOOKUP 함수를 사용하지 못 합니다. 대신 INDEX MATCH 함수를 이용해서 값을 구할 수 있습니다.
따라서 이글에서는 INDEX MATCH 함수를 이용해서 원하는 값을 찾는 방법을 알아 보겠습니다.
엑셀 INDEX MATCH 함수 사용방법
1.INDEX 함수
INDEX MATCH 함수를 조합해서 사용하는 방법을 알아 보기 전에 우선 INDEX 함수에 대해서 간단히 알아 보겠습니다.
▶︎INDEX ( 값을 찾을 범위, 값이 위치한 행 )
INDEX 함수는 우리가 찾으려는 값의 범위 및 행 번호를 입력하면 해당되는 값이 나오도록 해 주는 함수입니다.
아래 사진을 보시면 제품명, 단가, 제품 코드가 나와있는 데이터가 있습니다. 여기서 ‘간장 덮밥’이라는 제품명을 INDEX 함수를 이용해서 찾아 보도록 하겠습니다.
▶︎입력해야 하는 함수 식: =INDEX(A2:A8,5)
우선 =INDEX 라고 입력합니다. 첫 번째 인수로 값을 찾을 범위를 입력해야 합니다. 우리는 간장덮밥을 찾을 것이므로 간장덮밥이 포함되어 있는 A2:A8 셀 범위를 모두 선택합니다. 그리고 콤마를 입력합니다.
간장덮밥은 우리가 지정한 범위에서 5번째 행에 위치하고 있습니다. 따라서 두 번째 인수로 5를 입력합니다. 그 후 엔터키를 누릅니다. 그럼 ‘간장덮밥’이라는 결과가 나오게 됩니다. 즉, 찾을 값의 범위와 행을 입력해서 해당 값을 바로 찾아온 것입니다.
근데 INDEX 함수의 단점이 한 가지 있습니다. 행의 위치를 우리가 직접 세서 입력해야 한다는 점입니다. 따라서 이러한 불편함을 없애기 위해서 MATCH 함수를 이용해서 행 위치를 구할 수 있습니다.
2.MATCH 함수
MATCH 함수는 우리가 찾으려는 값이 행에서 몇 번째에 위치하고 있는지를 알려주는 함수 입니다.
▶︎MATCH (찾을 값, 값을 찾을 범위, 0)
찾을 값 및 값을 찾을 범위를 이용해서 구할 수 있는데요. 위 MATCH 함수 내 세 번째 인수를 보면 0 이라고 적혀있습니다. 정확하게 말하면 1,0,-1 중 하나를 입력해야 합니다.
▶︎1: 보다작음, 0: 정확히일치, -1: 보다큼
우리는 간장덮밥의 정확인 행 위치를 찾아야 합니다. 따라서 0을 입력해주면 됩니다. 실무에서도 대부분 0을 입력해서 사용합니다. 따라서 MATCH 세 번째 인수에는 0을 입력하면 된다고 생각하면 되겠습니다.
아래 사진을 보겠습니다. 여기서 제품코드인 N0005가 몇 번째 행에 위치하고 있는지 MATCH 함수를 이용해서 찾아 보겠습니다. 우선 =MATCH( 라고 입력해 주세요. 그리고 큰따옴표를 입력하고 N0005라고 입력합니다.
그리고 콤마를 입력해주세요. 그 후 두 번째 인수로 찾을 값의 범위를 입력해야 하는데요. 우리가 찾을 값인N0005가 포함되어 있는 제품코드 전체 열을 선택해 주시면 됩니다. 콤마를 입력한 후 세 번째 인수로 0을 입력해주면 됩니다.
엔터키를 누르면 5라는 값이 나오게 되는데요. 이는 N0005라는 제품코드가 지정한 범위 중 5행에 위치하고 있다는 의미입니다.
3.엑셀 INDEX MATCH 함수
이번에는 엑셀 INDEX MATCH 함수를 동시에 조합해서 값을 구하는 방법을 알아 보겠습니다.
이번에는 N0005 에 해당 되는 제품명이 무엇인지 찾아 보도록 할게요. 우선 =INDEX 라고 입력합니다. 우리는 INDEX 함수로 간장덮밥이라는 결과값을 찾아올 것입니다. 따라서 찾을 값의 범위로 간장덮밥이 포함되어 있는 제품명 셀을 전체 선택합니다.
콤마를 입력한 후 이번에는 =MATCH 라고 입력합니다. 그 후 MATCH 함수 내 첫 번째 인수로 N0005가 입력되어 있는 E5셀을 클릭합니다. 콤마를 입력하고 제품코드 전체 열을 선택합니다. 콤마를 또 입력하고 0을 입력한 후 괄호를 2번 닫고 엔터키를 누릅니다.
그럼 간장덮밥이라는 결과값이 나오게 됩니다. 즉, 우리는 MATCH 함수를 이용해서 N0005의 행 위치를 구했습니다. 그 후 N0005 행 위치에서의 제품명을 INDEX 함수를 이용해서 구한 것입니다.
4.실무 엑셀 INDEX MATCH 함수 사용법
이번에는 INDEX MATCH 함수를 실무에서 활용하는 방법을 알아 보겠습니다.
아래 사진의 왼쪽을 보시면 위에서 봤던 것과 동일한 데이터 예시가 나와 있고요. 왼쪽에 나와 있는 데이터를 이용해서 오른쪽에 ‘제품코드’를 기준으로 제품 명과 단가를 찾아와 보도록 하겠습니다.
▶︎입력해야 하는 함수: =INDEX($A$2:$A$8,MATCH(F2,$C$2:$C$8,0))
우선 =INDEX 를 입력합니다. 우리는 제품코드를 기준으로 제품명을 찾을 것입니다. 따라서 제품명에 해당되는 전체 열을 드래그 해서 선택합니다.
여기서 중요한 점이 한 가지 있습니다. 우리는 첫 번째 셀에 값을 구한 후 아래 셀에도 자동채우기를 할 것입니다. 따라서 지정한 셀 범위가 움직이지 않도록 고정을 시켜줘야 합니다. 따라서 F4키를 한 번 눌러서 셀을 고정시켜주세요.
그 후 콤마를 입력하고 MATCH함수를 입력합니다. 우리는 MATCH 함수를 이용해서 N0001의 행 번호를 찾을 것입니다. 따라서 MATCH 함수 내 첫 번째 인수로 N0001이 입력되어 있는 F2 셀을 클릭합니다.
콤마를 한 번 더 입력하고 N0001이 나와 있는 ‘제품코드’ 전체 열 범위를 선택합니다. 이번에도 F4키 를 눌러서 셀을 고정시켜주세요. 콤마를 입력하고 0을 입력한 후 엔터키를 누르면 ‘치킨마요’ 라는 결과값이 나옵니다.
즉, N0001에 해당되는 제품명이 치킨마요인 것입니다.
그 후 셀 모서리에 커서를 두고 검은색 + 표시가 나오면 더블클릭을 해서 자동채우기를 해주면 됩니다.
제품코드를 기준으로 제품 명을 모두 찾아와 봤습니다. 이번에는 제품코드를 기준으로 단가를 찾아와 보도록 하겠습니다.
이번에도 동일한 방식으로 입력을 해 주면 됩니다.
▶︎입력해야 하는 함수식: =INDEX($B$2:&B$8,MATCH(F2,$C$2:$C$8,0))
위와 같이 함수 식을 입력해 줍니다. 그리고 범위 부분에는 반드시 F4키를 눌러서 절대 참조를 적용 해 주세요. 함수 식을 입력한 후 엔터키를 누르면 단가 값이 바로 찾아집니다.
아래 셀에도 자동 채우기를 해서 단가를 자동으로 완성 시킬 수 있습니다.
마지막으로 총 판매 금액까지 구해 볼게요. 총 판매 금액은 단가에 판매개수를 곱하면 나옵니다. 따라서 총 판매 금액 글자 아래 J2 셀에 커서를 두고 =을 입력합니다.
그리고 단가 셀을 클릭하고 * 기호를 입력한 후 판매개수 셀을 클릭합니다. 그 후 엔터키를 누르면 총판매금액이 나옵니다. 이후 아래 셀에도 자동채우기를 해서 완성을 시킬 수 있습니다.
<결론>
▶︎엑셀 INDEX MATCH 함수 사용방법
1.MATCH 함수를 이용해서 찾으려는 값의 행 위치를 구한다.
2.MATCH 함수를 이용해서 찾은 행에 해당되는 값을 INDEX 함수를 이용해서 구한다.
이상 엑셀 INDEX MATCH 함수 사용방법에 관한 글이었습니다.