Home > 엑셀 함수 > 자주 사용하는 함수 > INDEX, MATCH를 이용한 다중조건으로 값찾기

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

엑셀에서 특정조건에 해당하는 값을 찾을때 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를 이용한 다중조건으로 값찾기”의 25개의 댓글

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

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

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

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

  3. 핑백: 엑셀함수 LOOKUP - 행 또는 열의 같은 위치에 있는 값 찾기 - XLWorks

  4. 핑백: 엑셀함수 INDEX - 특정 범위에서 행과 열을 이용하여 값 찾기 - XLWorks - XLWorks

    1. 안녕하세요. 정확히 어떻게 입력했는지 알려주셔야 도움을 드릴 수 있을 것 같습니다.
      감사합니다.

  5. 안녕하세요..! 제가 9-10시, 10-11시 처럼 구간별로 걸려온 전화 수 데이터를 일별로 가지고 있는데 각 일별 전화가 많이 걸려운 구간 상위 3개를 선정해서 표기하고 싶거든요! 예: 1위: 10-11시, 2위: 3-4시, 근데 이걸 match/index를 사용해서 가능할까요? =index (구간들, large(전화수, 1)) 이런 식으로 했는데 에러가 나서요 ㅠ_ㅠ 고견 부탁드립니다!

    1. 안녕하세요. 댓글의 내용만으로는 제가 자료의 구조를 이해하기 어렵습니다. 가능하시다면 엑셀파일을 admin@xlworks.net으로 보내주시면 확인하고 답변드리겠습니다.
      감사합니다.

  6. 덕분에 업무에 큰 도움 됐습니다. 설명은 조금 이해하기 어려웠는데 첨부파일을 확인하니 쉽게 알 수 있었습니다. 감사합니다.

  7. 계산은 정확하게 잘 되는것을 확인했는데,
    3000셀 이상을 자동채우기 실시하니까 엑셀이 다중스레드 계산에 들어가면서 상당히 느려지네요.
    한번 연산할때마다 5분가까이 소모되는 것 같스빈다.
    이 방식은 다수의 셀을 찾을땐, 특히 RANDBETWEEN 등과 함께 사용할 땐 적합하지 않을 것 같습니다.
    저랑 같은 문제를 겪으시는 분들이 생길 수 있어서 글을 남깁니다.

  8. 올려주신 엑셀파일에
    똑같은 수식을 입력했는데
    #VALUE! 오류가 뜹니다…
    무슨 이유일까요..?

  9. & 기호가 텍스트를 연결하는 기호로만 작동하는것같습니다.
    다중 조건의 기능이 맞는것일까요?

    1. 배열수식으로 입력해야 합니다. 수식 입력 후 ctrl+Shift+Enter 하셨는지 확인해 보시기 바랍니다. 감사합니다.

  10. 아 혹시나 제대로 했는데도 #value!나 #N/A 오류가 발생하면 수식 입력 후에 그냥 엔터키 말고, ctrl + shift + enter를 눌러보시기 바래요. 저도 다 해놓고 저걸 몰라서 계속 해맸어요ㅎㅎ

  11. 감사합니다 다중조건 설명중 제일쉬운수식으로 한방에 이해했어요 ㅎㅎ

  12. 과태료 계산에 관한 건데요,,
    총액을 알고있구요, 전체 위반 갯수와 위반 내용의 건당 금액을 아는데,
    위반 내용 건별 적용 금액을 기록하지 않아서,

    엑셀을 통해 찾고 싶은데, 무슨 위반 몇 건에 대해 금액이 산출 되어 총액과 맞출수 있는지요?
    엑셀 요청 사항 : 총액(6,240,000원)
    회사 위반시 과태료 (건당 20만원, 건당 10만원)
    개인 위반시 과태료 (건당 5만원, 건당 10만원)
    위반 내용 건수 39건(가항 1건, 나항 25건, 다항 6건, 라항 1건, 마항 1건, 바항 4건, 사항 1건)
    위반 내용 세부 건수에 대한 금액을 알고 싶습니다.
    엑셀로 어떻게 풀면 될까요?

    1. 안녕하세요. 위 내용대로 입력된 엑셀파일을 보내주셔야 답변이 가능할 것 같습니다. 제가 엑셀에 위 내용대로 입력한다고 해도 원래의 의도대로 정확히 입력을 못할 수도 있습니다.
      감사합니다.

  13. 안녕하세요,
    비슷한 수식인지 잘 모르겠는데 혹시 5자리숫자로 이루어진 데이터들 중에서 3번째 숫자가 1인 데이터의 갯수만 찾아내는 방법도 있을까요? 어떤 수식을 사용하면 될까요?

    1. 안녕하세요. 숫자값이 A1:A10범위에 있다고 할 때 다음 수식을 사용하시면 됩니다.
      =SUMPRODUCT(–(ISNUMBER(SEARCH(“??1??”,A1:A10))))
      물음표는 아무 글자 1자에 해당합니다.

  14. 숫자가 아니라 모든 조건들이 텍스트여도 가능한건가요?
    저는 #VALUE! 가 나오네요. ㅠㅠ

    1. 안녕하세요. 숫자, 텍스트 관계없이 모두 가능합니다. 아마 다른 이유때문에 오류가 나는 것 같습니다.

댓글 남기기

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