INDEX MATCH 함수 다중조건 |사용법

INDEX MATCH 함수 다중조건 사용방법에 대해 알아보는 글입니다. MATCH INDEX 함수 다중조건 및 배열수식을 이용해서 VLOOKUP 함수 대신 여러개 조건을 만족하는 값을 찾는 방법을 알아보겠습니다.

엑셀 연습 파일을 다운로드 받아서 연습을 해보면 이해가 훨씬 쉽습니다.

INDEX MATCH 함수 다중조건 기본 사용법

우리가 특정 조건에 맞는 모든 값을 찾아오려는 경우 VLOOKUP 함수 또는 INDEX MATCH 단일 함수를 사용하곤 합니다.

하지만 단일 함수만으로 값을 구할 수 없는 경우가 있습니다. 이러한 경우 INDEX MATCH 함수 다중조건을 사용해야 합니다. 따라서 이 글에서는 INDEX MATCH 함수 다중조건 사용 방법에 대해 자세히 알아보겠습니다.

INDEX MATCH 함수 다중조건을 사용하기 위해선 INDEX MATCH 단일 함수 사용 방법 및 배열 수식 사용 방법에 대해 알고 있어야 합니다. 따라서 아래 링크를 통해 각 내용을 미리 이해하고 오면 좋습니다.

▶︎엑셀 배열수식 기초 내용 | 총정리

▶︎엑셀 INDEX MATCH 함수 사용방법

우선 INDEX MATCH 함수 내 인수를 간단히 살펴보고 함수를 사용해 보도록 하겠습니다. INDEX MATCH 함수 내 인수는 아래와 같이 구성됩니다.

▶︎INDEX(범위, 행번호)

▶︎MATCH (찾을값, 범위, [일치여부])

아래 사진을 보시면 제품코드, 생산연도, 단가가 나와 있는 데이터가 있습니다. 여기서 제품코드 및 생산연도에 맞는 단가를 찾아와 보도록 하겠습니다. 그러기 위해 단가 아래 G2 셀에 함수식을 입력하겠습니다.

▶︎입력해야 하는 함수 식: =INDEX($C$2:$C$8,MATCH(1,($A$2:$A$8=E2)*($B$2:$B$8=F2),0))

위와 같이 함수 식을 입력한 후 엔터키를 누르면 제품코드 n001이면서 동시에 생산연도가 2021년인 값이 바로 찾아와집니다.

그런데 어떻게 위와 같은 함수식이 입력이 되는 것인지 자세히 알아보겠습니다.

▶︎=INDEX($C$2:$C$8,MATCH(1,($A$2:$A$8=E2)*($B$2:$B$8=F2),0))

INDEX 함수 내 첫 번째 인수로 우리가 구할 값의 범위가 들어가야 합니다. 우리는 단가를 구할 것이므로 단가에 해당되는 전체 셀(C2:C8)을 선택합니다. 그리고 콤마를 입력합니다.

▶︎=INDEX($C$2:$C$8,MATCH(1,($A$2:$A$8=E2)*($B$2:$B$8=F2),0))

INDEX 함수 2번째 인수로 찾을 값이 위치해있는 행을 입력해야 하는데요. 행의 위치를 찾기 위해서 MATCH 함수를 입력합니다. 그리고 MATCH 함수 내 첫 번째 인수로 1을 입력합니다. 그리고 콤마를 입력합니다.

1을 입력하는 이유는 아래에서 자세히 알아보겠습니다.

▶︎=INDEX($C$2:$C$8,MATCH(1,($A$2:$A$8=E2)*($B$2:$B$8=F2),0))

이번에는 조건 2가지를 입력해 줄 것입니다. 우리는 제품코드가 n001이고, 생산연도가 2021인 조건 2가지를 충족하는 값을 찾으려고 하고 있습니다.

n001에 해당되는 조건의 전체 범위는 A2:A8입니다. 따라서 괄호를 연 후 A2:A8 범위를 선택합니다.

참고로 배열수식에서 조건을 입력할 때는 반드시 각 조건마다 괄호를 입력해주셔야 합니다. 따라서 괄호를 꼭 입력해주세요.

근데 우리는 n001과 2021에 해당되는 값 뿐만 아니라 n002와 2022, n003과 2023에 해당되는 값도 자동채우기를 통해 찾을 것입니다.

그런데 셀이 이동될 때 지정한 범위가 바뀌면 안되겠죠. 따라서 범위가 이동하지 않도록 셀 범위를 딱 고정시켜줘야 합니다.

즉, 절대참조를 해주면 됩니다. 따라서 A2:A8 범위를 선택한 후 F4 키(맥북은 Fn+F4 키)를 눌러서 반드시 절대참조를 해주세요.

▶︎=INDEX($C$2:$C$8,MATCH(1,($A$2:$A$8=E2)*($B$2:$B$8=F2),0))

그리고 =을 입력하고 우리가 찾을 값인 n001이 나와있는 E2셀을 선택해주세요.

▶︎=INDEX($C$2:$C$8,MATCH(1,($A$2:$A$8=E2)*($B$2:$B$8=F2),0))

그 후 곱하기 기호(*)를 입력합니다. 2가지 조건을 동시에 만족하는 값을 찾는 경우 일반 함수에서는 AND 함수를 사용합니다. 하지만 배열수식에서는 AND 함수의 역할을 하는 것이 곱하기 기호(*)입니다.

따라서 2가지 조건을 동시에 만족하는 값을 찾기 위해서 곱하기 기호를 입력합니다.

▶︎=INDEX($C$2:$C$8,MATCH(1,($A$2:$A$8=E2)*($B$2:$B$8=F2),0))

그 후 2 번째 조건을 입력하기 위해서 다시 괄호를 엽니다. 2 번째 조건은 생산연도가 2021인 값이었습니다. 따라서 생산연도가 나와 있는 B2:B8 범위를 선택합니다. 그리고 이번에도 F4 키를 눌러서 절대참조를 해주세요.

그리고 =을 입력하고 우리가 찾을 값인 2021이 나와 있는 F2 셀을 선택해주세요. 그 후 괄호를 닫습니다.

▶︎=INDEX($C$2:$C$8,MATCH(1,($A$2:$A$8=E2)*($B$2:$B$8=F2),0))

그 후 콤마를 입력하고 MATCH 함수 마지막 인수로 0을 입력합니다. 0을 입력할 경우 정확한 값을 찾게 됩니다. 따라서 0을 입력해 주면 됩니다.

그리고 엔터키를 누르면 6000원이라는 값이 나오게 되고요. 여기서 아래 셀에 자동채우기를 해주면 나머지 셀도 자동으로 완성을 시킬 수 있습니다.

그런데 위 함수식이 어떻게 작동하는 것인지 이해가 잘 안 갈 수 있습니다. 따라서 어떤 과정을 거쳐서 함수식이 작동을 하게 된 것인지 그 세부적인 내용을 확인해보도록 하겠습니다.

아래 사진을 봅시다. 우리는 제품코드가 n001이면서 동시에 생산연도가 2021인 값의 단가를 찾았습니다. 따라서 MATCH 함수 내 조건 2가지를 아래와 같이 괄호 내에 입력을 했었습니다.

▶︎=INDEX($C$2:$C$8,MATCH(1,($A$2:$A$8=E2)*($B$2:$B$8=F2),0))

함수 식에서 ($A$2:$A$8=E2)이 무엇을 의미하는지 알아볼게요. A2:A8 범위에서 A2부터 A8까지 각각의 값을 제품코드 n001과 대칭을 시켜봅니다.

예를들어 A2 값(n001)과 E2 값(n001)을 대칭을 시켰더니 값이 동일합니다. 그럼 TRUE 라는 값으로 바뀌게 됩니다. 그리고 A3 값(n002)와 E2 값(n001)을 대칭을 시켰더니 값이 다릅니다. 그럼 FALSE로 바뀌게 됩니다.

이런식으로 모두 대칭을 시켜서 값이 동일하면 TRUE로 바뀌고, 다르면 FALSE로 바뀌게 됩니다.

따라서 아래 사진처럼 바뀌게 됩니다. 엑셀은 TRUE를 1로 인식하고 FALSE를 0으로 인식합니다. 따라서 각 TRUE 값은 1로 바뀌고, FALSE 값은 0으로 바뀝니다. 그래서 아래 사진처럼 1과 0으로 값이 각각 바뀌게 됩니다.

우리가 함수식에서 곱하기 기호 (*)를 입력했었습니다.

▶︎=INDEX($C$2:$C$8,MATCH(1,($A$2:$A$8=E2)*($B$2:$B$8=F2),0))

즉, 2개의 조건을 곱하라는 의미입니다. 따라서 제품코드와 생산연도 값을 0, 1로 바꾼 값을 곱하면 아래와 같이 결과 값이 나옵니다.

• 1 ×0=0
• 0×0=0
• 1×1=1
• 0×1=0
• 0×0=0
• 0×0=0
• 0×0=0

그리고 위 결과 값을 다시 배열수식으로 입력을 해보면 아래와 같습니다.

▶︎={0;0;1;0;0;0;0}

그리고 이를 함수식에 대입해보면 아래와 같습니다.

▶︎=INDEX($C$2:$C$8,MATCH(1,($A$2:$A$8=E2)*($B$2:$B$8=F2),0))

→ =INDEX($C$2:$C$8,MATCH(1,{0;0;1;0;0;0;0},0))

→ =INDEX($C$2:$C$8,3)

→ 6000

우리가 위에서 MATCH 함수 내 첫 번째 찾을 값을 1으로 입력하는 이유를 아래에서 알아본다고 했었는데요. 그 이유가 바로 여기에 있습니다.

즉, 배열 수식 {0;0;1;0;0;0;0} 에서 1이라는 값을 찾아 달라 라는 의미입니다. 그래서 찾을 값을 1이라고 입력을 하는 것입니다.

{0;0;1;0;0;0;0}에서 1은 3번째 행에 위치하고 있죠. 참고로 배열수식에서 ;이라는 기호는 세로로 셀이 연결이 되어 있다는 의미입니다. 아래 사진처럼 말이죠.

즉, 현재 1이 3번 째 행에 위치하고 있는 것을 알 수 있습니다. 따라서 함수식은 =INDEX($C$2:$C$8,3)으로 바뀌게 됩니다. 그리고 C2:C8의 3번째 행의 값은 6000입니다. 따라서 6000이라는 값이 찾아와진 것입니다.

이렇게 INDEX MATCH 함수 다중조건을 이용해서 값을 구해볼 수 있습니다.

INDEX MATCH 함수 다중조건 결합연산자(&) 사용법

와 같은 방식으로 값을 찾아올 수도 있지만 조금 더 간단하게 INDEX MATCH 함수 다중조건 결합연산자(&)를 이용해서 동일한 값을 구할 수도 있습니다.

아래 사진을 보시면 같은 데이터 값이 나와 있고요. 여기서 제품코드가 n001이고 생산연도가 2021인 값의 단가를 찾아와 볼 것입니다. 그러기 위해 아래와 같이 함수식을 입력합니다.

▶︎입력해야 하는 함수 식: =INDEX($C$2:$C$8,MATCH(E2&F2,$A$2:$A$8&$B$2:$B$8,0))

즉, 이번에는 결합연산자(&)를 사용해서 값을 구하는 것입니다. 어떻게 위와 같은 함수식이 입력이 되는지 확인해보겠습니다.

=INDEX($C$2:$C$8,MATCH(E2&F2,$A$2:$A$8&$B$2:$B$8,0))

우선 =INDEX를 입력합니다. 그 후 우리가 찾아올 값에 해당되는 단가의 전체 범위인 C2:C8를 선택합니다. 그리고 이번에도 셀을 고정하기 위해서 F4 키를 눌러서 범위를 고정시킵니다. 그리고 콤마를 입력합니다.

=INDEX($C$2:$C$8,MATCH(E2&F2,$A$2:$A$8&$B$2:$B$8,0))

이번에는 우리가 찾을 n001 및 2021에 해당되는 단가의 행 번호를 찾아야 합니다.

행을 찾기 위해 MATCH 함수를 입력합니다. 첫 번째로 찾을 값을 입력 하는데요. 우리가 찾을 값은 n001, 2021입니다. 따라서 E2&F2라고 입력을 합니다.

결합연산자(&)를 이용해서 찾을 값을 한번에 입력해준 것입니다.

=INDEX($C$2:$C$8,MATCH(E2&F2,$A$2:$A$8&$B$2:$B$8,0))

이번에는 찾을 값이 나와 있는 범위를 선택해야 합니다. n001이 나와 있는 제품코드 전체 범위를 선택한 후 F4키를 눌러서 절대참조를 합니다. 그리고 결합연산자 기호(&)를 입력합니다.

그 후 두 번째 조건인 2021에 해당되는 생산연도 전체 범위를 선택합니다. 그리고 F4 키를 눌러서 절대참조를 합니다.

즉, 결합연산자(&)를 이용해서 2개의 조건을 결합을 시켜 입력을 한 것입니다.

=INDEX($C$2:$C$8,MATCH(E2&F2,$A$2:$A$8&$B$2:$B$8,0))

그 후 콤마를 입력합니다. 그리고 0을 입력하는데요. 0을 입력하면 정확한 값을 찾아옵니다. 따라서 MATCH 마지막 인수로 0을 입력합니다.

그리고 괄호를 2번 닫습니다. MATCH에 해당되는 괄호를 한 번 닫고 INDEX에 해당되는 괄호를 한 번 더 닫는 것입니다.

그 후 엔터키를 누르면 6000이라는 값이 나오게 됩니다.

이 후 아래 셀에도 자동채우기를 하면 아래 사진처럼 아래 셀도 자동으로 완성이 됩니다.

그런데 어떻게 해서 위와 같은 계산 과정이 나오게 되었는지 궁금할 수 있습니다. 따라서 상세한 계산 과정을 알아보겠습니다.

=INDEX($C$2:$C$8,MATCH(E2&F2,$A$2:$A$8&$B$2:$B$8,0))

우리는 위와 같이 함수식을 입력 했었는데요. 여기서 E2&F2를 다시 바꿔서 표현을 해보면 아래와 같습니다.

▶︎E2&F2 → n001&2021 → n0012021

그리고 범위에 해당되는 부분인 $A$2:$A$8&$B$2:$B$8를 다시 바꿔서 표현 해보면 아래와 같습니다.

▶︎$A$2:$A$8&$B$2:$B$8 → {n0012020;n0022020;n0012021;n0022021;n0022022;n0032022;n0032023}

위 내용을 아래 사진으로 다시 확인해보겠습니다. 제품코드 범위와 생산연도 범위를 결합연산자(&)로 연결을 해보면 아래와 같이 나오게 됩니다. 여기서 찾을 값 이었던 n0012021은 3번째 행에 위치하고 있습니다.

따라서 함수식은 아래와 같이 바뀌게 됩니다.

▶︎=INDEX($C$2:$C$8,MATCH(E2&F2,$A$2:$A$8&$B$2:$B$8,0))

→=INDEX($C$2:$C$8,MATCH(n0012021,$A$2:$A$8&$B$2:$B$8,0))

→=INDEX($C$2:$C$8,3)

→6000

위 내용을 정리하자면 $A$2:$A$8&$B$2:$B$8 범위에서 n0012021에 해당되는 값이 현재 3번째 행에 위치하고 있다는 의미입니다.

따라서 함수 식은 =INDEX($C$2:$C$8,3)으로 바뀌게 됩니다. 그리고 C2:C8 범위에서 3 번째 행에 해당되는 값이 6000입니다. 따라서 결론적으로 6000이라는 값이 나오게 되는 것입니다.

즉, 결합연산자(&)를 이용해서 같은 결과 값을 낼 수도 있습니다.

<결론>

▶︎INDEX MATCH 함수 다중조건 사용법

1.INDEX MATCH 함수 다중조건 및 배열수식을 이용해서 복잡한 조건에 해당되는 모든 값을 찾아올 수 있음.

2.결합연산자(&)를 이용해서 동일한 값을 구할 수도 있음.

이상 INDEX MATCH 함수 다중조건 사용법에 관한 글이었습니다.