INDEX, MATCH를 이용한 다중조건으로 값찾기

공개됨 글쓴이 Admin댓글 6개

엑셀에서 특정조건에 해당하는 값을 찾을때 VLOOKUP이나 INDEX,MATCH 함수를 많이 이용하는데, 값을 찾는 조건이 여러 개(다중조건) 일 경우는 VLOOKUP으로는 불가능하다. 이때는 INDEX, MATCH함수를 사용하되 찾는 값을 “&”기호로 묶어서 사용하면 된다.

1) 단일 조건으로 값찾기

다중조건으로 찾는 것을 학습해 보기 전에 이해를 돕기 위해 조건이 하나만 있는(단일 조건) 것을 먼저 살펴보자.

아래 그림과 같이 어떤 문구회사의 거래처별 판매실적 자료의 품목코드에 해당하는 단가정보를 단가Sheet에서 가져올 때 INDEX/MATCH함수를 이용할 수 있다.

=INDEX(단가!D:D,MATCH(판매실적!C4,단가!A:A,0),1)

**INDEX/MATCH를 이용해서 단일조건을 값을 찾는 방법은 [VLOOKUP으로 안될때 INDEX,MATCH 사용하기] 를 참고하자.

물론 이 경우 다음 처럼 VLOOKUP함수를 이용할 수 있다. 그렇지만 VOOKUP함수는 찾기 위해서 사용하는 키값이 항상 첫번째 열에 있어야 하는 제약이 있으므로 INDEX/MATCH를 사용하면 키 값이 다른 열에 있어도 값을 찾을 수 있다.

=VLOOKUP(C4,단가!A2:D11,4,FALSE)

단가Sheet

 

판매실적자료(판매실적Sheet)

 

2) 다중 조건으로 값찾기

그런데 동일 품목의 단가가 년도별로 바뀐다면 단가를 어떻게 찾아야 할까? 품목코드만으로는 단가를 찾을 수 없고 품목코드와 적용년도를 이용하여 단가를 찾아야 한다. 찾는 조건이 한개가 아니라 여러개이다. 그래서 다중조건으로 값을 찾는다고 하는데 다음과 같이 따래해 보자.

아래와 같이 “년도별단가”Sheet에 품목코드별, 적용년도별로 단가정보가 있다. 판매실적Sheet에 품목코드와 적용년도에 해당하는 단가를 가져오기 위해 다음 수식을 F15셀에 입력하자

* 배열을 이용한 수식이므로 수식입력 후 Ctrl + Shift + Enter를 입력해야 한다. 제대로 입력되면 수식 양쪽에 { 수식…. }의 형태로 중괄호가 붙어 있다.

=INDEX(년도별단가!C:C,MATCH(판매실적!C15&판매실적!E15,년도별단가!A:A&년도별단가!B:B,0),1)

수식이 정상적으로 입력되었으면 F15셀이 품목이 A003이면서 적용년도가 2017년인 단가 80원이 구해진다.

년도별단가Sheet

 

판매실적Sheet

 

수식의 풀이
INDEX/MATCH 다중조건 찾기

 

입력된 수식은 INDEX 함수안에 MATCH가 들어가 있는 형태이므로

  • MATCH함수가 먼저 수행되는데
    – MATCH함수의 첫번째 인수는 찾는 키 값이다. 이 키값이 “판매실적!C15&판매실적!E15”의 형태로 되어 있고 중간에 “&”기호가 있다. 이 기호는 2개의 값을 결합하는 기능을 하는데 C15셀의 품목코드 값 A003이면서 E15셀의 적용년도가 2017년인것을 찾는다는 뜻이다.
    – 두번째 인수는 값을 찾는 범위인데 역시 “년도별단가!A:A&년도별단가!B:B”의 형태로 “&” 기호를 이용하여 결합되어 있다. 품목코드와 적용년도 범위에 동시에 값을 찾는다는 뜻이다.
  • 위의 MATCH함수가 수행되면 찾은 값의 행의 위치인 9가 구해지고 다음으로  “=INDEX(년도별단가!C:C,9,1)” 수식이 수행되어 “년도별단가!C:C” 범위에서 9행이면서 1열에 있는 값인 단가 80원이 구해진다.

이것으로 “INDEX, MATCH를 이용한 다중조건으로 값찾기”에 대한 설명을 마친다. 먼저 INDEX, MATCH함수에 대한 이해가 되어야 하므로 필요한 경우 아래의 함수목록을 참고하자.

[ 엑셀 찾기 및 참조영역 함수 목록 ]

 

 

INDEX, MATCH를 이용한 다중조건으로 값찾기에 1개의 응답

  1. index match 함수를 이용하여 2가지 조건 세로와 가로 조건으로 만족하는 값을 찾았으나,
    3가지 조건적용시 &을 이용하여 하는 법은 처음 알았습니다. 감사합니다.

  2. 다중조건으로 값찾기를 응용해서, 만일 자격수당에 적용할때

    종류 등급 수당
    가스 기사 3
    가스 기능사 1
    전기 기사 3
    전기 산업기사 2
    이렇게 수당을 구했다면, 종류가 같은 것 중에서, 수당이 큰 것을 선택해서, 수당합계를 구하고 싶은데 어떻게 하면 될까요, 즉, 가스 3 + 전기 3 = 수당합계 6

    • 안녕하세요.
      =LARGE(IF(A2:A5=”가스”,C2:C5),1)를 입력하고 Ctrl+Shift+Enter를 누르세요(배열수식입니다). 전기도 이런식으로 합계를 구한 다음 전체를 더하면 수당합계를 구할 수 있습니다.
      감사합니다.

댓글 남기기

이메일은 공개되지 않습니다.