엑셀 다중 조건으로 검색하여 값을 참조하기 - INDEX, MATCH

이전 글에서 VLOOKUP 함수 대신 INDEX 함수와 MATCH 함수를 사용하는 방법에 대해 알아보았습니다. 이번에는 INDEX 함수와 MATCH 함수를 이용하여 검색 조건이 두 개 이상인 경우 원하는 값을 참조하는 방법에 댛 알아보겠습니다. 이를 알아보기 위해서는 이전 글에서 알아본 배열 수식에 대한 내용도 필요하므로 미리 확인해 보시기 바랍니다.

 

엑셀 INDEX 함수와 MATCH 함수로 VLOOKUP 함수 기능 발휘하기

엑셀 INDEX 함수와 MATCH 함수로 VLOOKUP 함수 기능 발휘하기

 

엑셀 배열 수식을 이용하여 결과값 얻기

엑셀 배열 수식을 이용하여 결과값 얻기

 

 

 

 

 

1. 다중 조건일 때 참조 방법

  데이터를 검색할 조건이 두 개 이상인 경우 VLOOKUP 함수를 이용할 수 없습니다. 이럴 경우 INDEX 함수와 MATCH 함수를 이용하면 다중 조건일 때 데이터 검색이 가능합니다. 사용 방법은 다음과 같습니다.

 

   = INDEX (데이터 범위, MATCH (1, 다중 조건, 0), 데이터 참조할 열 번호))

 

데이터 범위 : 찾으려는 데이터가 입력되어 있는 일련의 데이터 표

 

다중 조건 : 검색 조건으로 조건과 조건을 곱하기(*) 형태로 입력

 

데이터 참조할 열 번호 : 다중 조건을 만족하는 행에서 몇 번째 열의 값을 가져올지 입력

 

주의하실 점은 다중 조건으로 INDEX 함수와 MATCH 함수를 사용하시면 배열 수식을 이용하여 데이터를 찾게 되므로 수식을 입력하고 Ctrl + Shift + Enter 키를 눌러야 원하는 값을 얻을 수 있습니다.

 

 

 

 

2. 예제로 확인

  [그림 1]과 같은 케이블 단가표가 있을 때 품명과 크기 두 가지의 조건을 만족하는 m당 가격을 찾아 원하는 가격을 계산하는 예제를 풀어보겠습니다.

 

[그림 1] 케이블 단가표

위의 단가표에서 2P Cable 중에서 크기가 3 mm인 케이블 3.5 m의 가격이 얼마인지 알려고 합니다.

 

우선, 품명을 입력하는 셀로 G3 셀을, 크기를 입력하는 셀은 H3, 그리고 케이블의 길이를 입력하는 셀로 I3 셀을 선택합니다. ([그림 2] 참조)

 

[그림 2] 검색 조건 입력

 

가격을 알고싶은 케이블의 품명과 크기, 그리고 사용할 길이를 입력하도록 하였습니다. 이제 다중 조건을 검색하여 단가를 찾은 후 해당 길이의 가격이 얼마인지 알기 위해서 K3 셀에 다음과 같이 수식을 입력합니다. 앞에서도 말씀드렸지만 수식을 입력하신 후 반드시 Ctrl + Shift + Enter 키를 누르셔야 합니다.

 

  =INDEX($B:$D,MATCH(1,(G3=$B:$B)*(H3=$C:$C),0),3)*I3

 

데이터 범위는 B열부터 D열을 지정하였습니다.

MATCH 함수 인수중 다중 조건을 입력한 내용을 보시면 다음과 같습니다.

 

(G3=$B:$B)*(H3=$C:$C)

: G3 셀의 값 즉, 품명을 데이터 범위의 B열에서 찾고, H3 셀의 값 즉, 크기를 데이터 범위의 C열에서 찾아서 그 값을 곱하라는 의미입니다. 두 조건이 각각 참인 경우 숫자로는 1을 나타내므로 두 값의 곱이 1일 때 조건이 일치한다는 의미입니다. 그래서, 다중 조건 검색인 경우 MATCH 함수 첫 번째 인수는 반드시 1로 입력하셔야 합니다.

 

MATCH 함수 마지막 인수 0은 앞의 글에서도 설명드렸듯이 찾는 값이 일치하는 경우 값을 참조하라는 의미입니다.

 

INDEX 함수의 마지막 인수인 3은 데이터 범위인 B열부터 D열에서 3번째 열인 D열의 값을 참조하라는 의미입니다.

 

이 다중 조건에 부합하는 값을 찾고 그 값(m당 가격)을 I3 셀에 입력되어 있는 케이블의 길이를 곱하면 케이블의 가격을 알 수 있게 됩니다.

 

위의 조건에 해당하는 m당 가격은 6,000 원이므로 사용하려는 케이블 길이 3.5의 경우 6000 곱하기 3.5가 되어 21,000 원을 결괏값으로 얻을 수 있습니다.