엑셀 실무] VLOOKUP으로는 절대 가져올 수 없는 INDEX, MATCH 함수를 배열 수식으로 작성하여 두 조건에 일치하는 값 찾아오기

오늘 소개할 내용은 두 조건에 일치하는 값을 찾아오는 방법입니다.

 

데이터 테이블에서 조건에 일치하는 값을 찾아올 때 일반적으로 VLOOKUP 함수를 떠올립니다.

하지만 VLOOKUP 함수 외에도 INDEX와 MATCH 함수 조합을 사용할 수도 있습니다.

 

VOOKUP 함수와 INDEX+MATCH 함수를 구분해서 사용하는 방법을 강좌로 올린 적이 있는데 참고하면 도움이 많이 될 겁니다.

 

VLOOKUP 함수는 테이블 첫 열에서 일치하는 값을 검색하기 때문에 조건이 두 개인 경우 일 때 두 번째 조건은 테이블의 첫 열에 해당되지 않습니다.

이런 경우 VLOOKUP 함수를 사용할 수 없고 INDEX, MATCH 함수 조합을 사용해야 합니다.

 

정**님께서 제공하신 데이터를 보겠습니다.

왼쪽과 오른쪽이 테이블은 원래 시트가 다르게 작성되어 있었지만 쉬운 이해를 위해 하나의 시트에 임의 작성했습니다.

 

실습 파일 다운로드

2가지조건에일치하는값찾기.xlsx

0.07MB

완성 파일 다운로드

2가지조건에일치하는값찾기_완성.xlsx

0.07MB

 

오른쪽 표에서 펀드코드와 판매사명이 일치하는 세부유형을 왼쪽 테이블에서 찾아오려고 하는 겁니다.

 

수식을 작성해 보겠습니다.

수식을 작성할 때 직접 입력해도 되지만 함수 인수 대화상자를 사용해서 작성할 수도 있습니다.

여기서는 함수 인수 대화상자를 사용하여 작성하는 방법을 소개하겠습니다.

 

결과를 구할 [L2] 셀을 선택하고 =INDEX()를 입력한 후 함수 삽입[fx] 버튼을 누릅니다.

아래와 같이 [인수 선택] 대화상자가 열리면 array,row_num,column_num을 선택하고 [확인] 버튼을 클릭합니다.

 

 

2. Array 인수는 실제 찾아올 값이 있는 왼쪽 테이블의 세부유형 범위를 참조하고 <F4>를 눌러 절대 참조합니다.

(수식을 복사해서 나머지 조건에 맞는 결과를 구할 때 참조 셀이 변하면 안되겠죠?)

 

 

3. INDEX 함수는 Array 인수로 지정한 세부유형 범위에서 행과 열이 일치하는 값을 찾아오는 역할을 하는데 그 두 번째 인수가 바로 두 조건에 일치하는 행 번호를 찾는 인수가 됩니다.

행 번호를 구하기 위해 Row_num 인수 입력창에 match() 함수를 중첩한 다음 수식 입력줄에서 match()함수를 선택합니다.

그럼 함수 인수 대화상자는 Match 함수를 작성할 상태로 바뀝니다.

 

 

4. Lookup_value 인수에 1을 입력합니다.

Lookup_value 인수는 찾으려는 값입니다.

찾을 값이 펀드코드 한 개라면 펀드코드를 왼쪽 테이블의 펀드코드 범위에서 찾아 행 번호를 구하면 되지만 조건이 두 개 이상일 경우에는 간단하게 처리되지 않습니다.

MATCH 함수가 찾을 값은 펀드코드와 판매사명이 모두 일치하는 True(참)입니다. (True는 숫자 1로 표현합니다)

 

두 번째 Lookup_array 인수에 먼저 아래와 같이 작성합니다.

($A$2:$A$1097=H2)

첫 번째 조건이죠? 왼쪽 테이블의 펀드코드 범위에서 왼쪽 테이블의 펀드코드와 비교합니다.

 

 

두 번째 조건은 왼쪽 테이블의 판매사명 범위와 오른쪽 테이블의 판매사명이 같은지 비교하는 수식을 작성합니다.

 

5. ($C$2:$C$1097=J2) 작성한 후 두 조건식을 곱하기 합니다.

 

 

6. MATCH 함수 마지막 인수인 Match_type은 0을 입력합니다.

 

 

자~ 그럼 MATCH 함수식을 다시 보겠습니다.

첫 번째 인수 Lookup_value 인수는 두 조건이 모두 만족하는 1입니다. 그리고 두 번째 인수 Lookup_array는 두 조건을 곱하는 수식을 작성합니다.

이때 두 조건을 비교하여 모두 일치하는 경우(True * True =1)에 해당하는 행 번호를 찾습니다.

 

7. 세 번째 인수는 Match_type은 정확하게 일치하는 값을 찾는 경우 0을 입력합니다.

 

 

8. 다시 수식 입력줄에서 INDEX 함수를 선택하면 함수 인수 대화상자는 INDEX 함수로 변경됩니다.

INDEX 함수의 세 번째 인수 Column_num 인수는 1을 입력하거나 생략합니다.

Array 인수로 지정한 범위가 왼쪽 테이블의 세부유형 범위죠?

열이 한 개 뿐이기 때문에 Column_num은 1이 될 수밖에 없습니다.

함수식을 완성했다면 <Ctrl + Shift + Enter>를 누릅니다.

 

MATCH 함수 두 번째 인수로 작성한 방식이 일반적인 인수 작성법이 아닙니다.

배열 수식으로만 작성이 가능한 상태입니다.

배열 수식을 작성한 후 <Enter> 또는 [확인] 대신 <Ctrl + Shift+ Enter>를 눌러야지만 정확한 배열 수식의 결과를 얻을 수 있습니다.

 

 

수식을 작성할 때 주의해야할 점은 다음과 같습니다.

MATCH 함수 두 번째 인수를 보면 각각의 조건식이 괄호 속에 작성되어 있습니다.

곱하기 연산자가 =(같다) 연산자보다 연산 우선순위를 가지기 때문에 먼저 조건을 비교할 수 있도록 괄호를 넣어야 합니다.

=INDEX($F$2:$F$1097,MATCH(1,($A$2:$A$1097=H2)*($C$2:$C$1097=J2),0),1)

 

말로 설명하면 좀 더 간단할 것 같은데 글로 적으니 복잡해지네요.

 

 

 

2019/08/02 - 엑셀 실무] 배열 수식 기본 사용법

엑셀 실무] 배열 수식 기본 사용법

2017/01/07 - 엑셀 45강] index, Match 함수의 기본 사용법

엑셀 45강] index, Match 함수의 기본 사용법

2017/01/08 - 엑셀 46강] 예제를 통한 index와 Match함수 익히기

엑셀 46강] 예제를 통한 index와 Match함수 익히기