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

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

2022-12-31 / 40 댓글

다음 그림과 같이 상품정보에서 단가를 연도별로 관리한다고 할때 단가를 찾으려면 상품코드에 연도 조건을 추가해서 찾아야 합니다.

이 경우에 VLOOKUP 함수나 INDEX, MATCH 함수를 이용한 단일 조건으로는 값을 찾을 수 없고 INDEX, MATCH 함수와 배열 수식을 조합하면 값을 찾을 수 있습니다.

INDEX, MATCH 함수를 이용하여 연도별 단가 찾기

실습용 엑셀파일 다운로드 : 엑셀-INDEX-MATCH-함수를-이용한-다중조건으로-값찾기.xlsx

※ 예제 이해에 필요한 내용은 다음 링크를 참고하세요.
[ VLOOKUP으로 안될때 INDEX, MATCH 함수 사용하기 ] - INDEX, MATCH를 이용한 단일 조건으로 값찾기
[ 배열수식 제대로 이해하기 ] - 배열 수식 기초
[ INDEX 함수 - 범위에서 행과 열을 이용하여 값 찾기 ]
[ MATCH 함수 - 범위에서 값의 위치 찾기 ]

 

1. 찾는 조건을 TRUE, FALSE 바꾸어서 찾기

찾고자 하는 값을 배열과 비교하여 맞으면 TRUE, 아니면 FALSE로 바꾸어서 찾는 방법입니다.

이 방법은 배열수식, INDEX, MATCH 함수 등 사전에 알아야 할 것이 많지만 알아 두면 복잡한 조건으로 값을 찾을 때 많은 도움이 됩니다.

INDEX, MATCH 함수를 이용한 다중조건으로 값찾기 - TRUE, FALSE 바꾸어서 찾기 수식입력

판매 연도, 상품코드에 해당하는 단가를 찾기 위해 [E19] 셀에 다음 수식을 입력합니다.

=INDEX($E$6:$E$14,MATCH(1,($B$6:$B$14=C19)*($D$6:$D$14=B19),0))

배열 수식이므로 수식 입력 후 [Enter] 대신에 [Ctrl+Shift+Enter]를 눌러야 합니다.
* Microsoft 365, 엑셀 2021 이상 버전에선 [Enter]만 눌러도 됩니다.
수식이 제대로 입력되면 배열수식을 의미하는 중괄호가 수식 양쪽에 붙어 있습니다.
{=INDEX($E$6:$E$14,MATCH(1,($B$6:$B$14=C19)*($D$6:$D$14=B19),0))}

수식이 입력되면 상품코드가 'A002'이고 적용연도가 2020년인 단가 35,000원을 가져옵니다.

INDEX, MATCH 함수를 이용한 다중조건으로 값찾기 - TRUE, FALSE 바꾸어서 찾기 결과

 

배열수식을 이용해서 값을 가져왔는데 이 복잡한 수식이 어떻게 작동되는지 확인해보겠습니다.

INDEX 함수안에 MATCH 함수가 포함되어 있으므로 MATCH 함수가 먼저 실행됩니다.
=INDEX($E$6:$E$14,MATCH(1,($B$6:$B$14=C19)*($D$6:$D$14=B19),0))

위의 수식에서 찾는 조건을 값으로 바꾸면 다음과 같이 됩니다.
=INDEX($E$6:$E$14,MATCH(1,($B$6:$B$14="A002")*($D$6:$D$14="2020"),0))

아래와 같이 수식의 파란색 부분은 서로의 결과를 곱하는 것입니다.
($B$6:$B$14="A002")*($D$6:$D$14="2020")

 

곱하기 전에 앞 부분과 뒷 부분을 떼어서 그림으로 표현하면 다음과 같습니다.
($B$6:$B$14="A002") => 상품코드가 'A002'이면 TRUE로 바뀌고 아니면 FALSE로 바뀝니다.
($D$6:$D$14="2020") => 적용연도가 2020년이면 TRUE로 바뀌고 아니면 FALSE로 바뀝니다.

 

INDEX, MATCH 함수를 이용한 다중조건으로 값찾기 - TRUE, FALSE 바꾸어서 찾기 작동 순서

 

상품코드와 적용연도 조건을 비교해서 TRUE, FALSE로 바꾸고 서로를 곱하면 결과는 다음과 같습니다.
엑셀에서 TRUE는 1, FALSE는 0으로 인식합니다.

($B$6:$B$14="A002")*($D$6:$D$14="2020")

INDEX, MATCH 함수를 이용한 다중조건으로 값찾기 - TRUE, FALSE 바꾸어서 찾기 작동 순서

결과적으로 상품코드가 'A002'이고 적용연도가 2020년인 것을 만족하는 것은 4번째 행이 됩니다.

 

위의 결과를 배열로 표현하면 {0;0;0;1;0;0;0;0;0} 와 동일합니다.

INDEX, MATCH 함수를 이용한 다중조건으로 값찾기 - TRUE, FALSE 바꾸어서 찾기 작동 순서

 

따라서 이 수식은
=INDEX($E$6:$E$14,MATCH(1,($B$6:$B$14="A002")*($D$6:$D$14="2020"),0))

다음과 같이 표현할 수 있고,
=INDEX($E$6:$E$14,MATCH(1,{0;0;0;1;0;0;0;0;0},0))

INDEX 함수 안의 MATCH 함수를 해석해 보면 1이라는 값을 {0;0;0;1;0;0;0;0;0} 범위에서 몇 번째 있는지 찾는데 4번째 행에 있으므로 4를 반환합니다.
MATCH(1,{0;0;0;1;0;0;0;0;0},0) => 4

MATCH 함수의 결과 4를 받아서 수식은 다음과 같이 바뀝니다.
=INDEX($E$6:$E$14, 4)

최종적으로 INDEX 함수는 단가 범위 [$E$6:$E$14]의 4번째 행의 35,000원을 구해줍니다.

INDEX, MATCH 함수를 이용한 다중조건으로 값찾기 - TRUE, FALSE 바꾸어서 찾기 최종결과

 

 

2. & 기호(결합연산자)로 묶어서 찾기

앞에서 살펴본 TRUE, FALSE로 바꾸는 방식보다는 간단하고 이해하기 쉬운 방식입니다.

INDEX, MATCH 함수를 이용한 다중조건으로 값찾기 - 결합연산자로 묶어서 찾기

판매 연도, 상품코드에 해당하는 단가를 찾기 위해 [E28] 셀에 다음 수식을 입력합니다.
=INDEX($E$6:$E$14,MATCH(C28&B28,$B$6:$B$14&$D$6:$D$14,0))

배열 수식이므로 수식 입력 후 [Enter] 대신에 [Ctrl+Shift+Enter]를 눌러야 합니다.
* Microsoft 365, 엑셀 2021 이상 버전에선 [Enter]만 눌러도 됩니다.
수식이 제대로 입력되면 배열수식을 의미하는 중괄호가 수식 양쪽에 붙어 있습니다.
{=INDEX($E$6:$E$14,MATCH(C28&B28,$B$6:$B$14&$D$6:$D$14,0))}

수식이 입력되면 상품코드가 'A002'이고 적용연도가 2020년인 단가 35,000원을 가져옵니다.

INDEX, MATCH 함수를 이용한 다중조건으로 값찾기 - 결합연산자로 묶어서 찾기 결과

 

배열수식을 이용해서 값을 가져왔는데 수식이 어떻게 작동되는지 확인해보겠습니다.

이 방식은 '단일 조건으로 찾기'와 같은 방식으로 이해할 수 있습니다.

차이점은 다음과 같이 상품코드만 가지고 비교하던 것을
"A002"

상품코드에 '&' 기호로 연도를  묶어서 비교하는 것만 차이가 있을 뿐 기본 개념이 동일합니다.
"A002"&2020 => 'A0022020'

따라서 최초 입력된 다음 수식은
=INDEX($E$6:$E$14,MATCH(C28&B28,$B$6:$B$14&$D$6:$D$14,0))

다음과 같이 표현할 수 있습니다.
=INDEX($E$6:$E$14,MATCH("A0022020",$B$6:$B$14&$D$6:$D$14,0))

INDEX 함수안에 MATCH 함수가 포함되어 있으므로 MATCH 함수가 먼저 실행됩니다.
MATCH("A0022020",$B$6:$B$14&$D$6:$D$14,0)

위 수식의 $B$6:$B$14&$D$6:$D$14부분을 그림으로 표현하면 다음과 같이 내부적으로 2개의 범위가 합쳐져서

INDEX, MATCH 함수를 이용한 다중조건으로 값찾기 - 결합연산자로 묶어서 찾기 작동순서

 

아래와 같이 배열이 만들어 집니다.

INDEX, MATCH 함수를 이용한 다중조건으로 값찾기 - 결합연산자로 묶어서 찾기 작동순서

 

이제 MATCH 함수에서 'A0022020' 라는 값을 아래 그림의 배열에서 찾으면
4번째 행에 있으므로 4를 반환합니다.
MATCH("A0022020",$B$6:$B$14&$D$6:$D$14,0) => 4

INDEX, MATCH 함수를 이용한 다중조건으로 값찾기 - 결합연산자로 묶어서 찾기 작동순서

결과적으로 상품코드가 'A002'이고 적용연도가 2020년인 것을 만족하는 것은 4번째 행이 됩니다.

 

따라서 다음 수식에서
=INDEX($E$6:$E$14,MATCH("A0022020",$B$6:$B$14&$D$6:$D$14,0))

MATCH 함수의 결과 4를 받으면 수식은 다음과 같이 바뀝니다.
=INDEX($E$6:$E$14, 4)

최종적으로 INDEX 함수는 단가 범위 [$E$6:$E$14]의 4번째 행의 35,000원을 구해줍니다.

INDEX, MATCH 함수를 이용한 다중조건으로 값찾기 - 결합연산자로 묶어서 찾기 최종결과

 

 

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

 

 

엑셀웍스 책 출간 안내

그동안 엑셀웍스에 올린 '엑셀 강좌', '함수 사용법', 여러 '실무 예제'에서 핵심만 뽑아 '된다! 엑셀 수식 & 함수' 책으로 출간하였습니다.

 자세히 보기

온라인 구매처: 예스24   교보문고   알라딘 

“INDEX, MATCH 함수를 이용한 다중조건으로 값찾기”의 40개의 댓글

  2. 안녕별

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

    답글
  3. 정영철

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

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

    답글
    1. Admin

      안녕하세요.
      =MAX(IF(A2:A5="가스",C2:C5))를 입력하고 Ctrl+Shift+Enter를 누르세요(배열수식입니다). 전기도 이런식으로 합계를 구한 다음 전체를 더하면 수당합계를 구할 수 있습니다.
      엑셀 2019 버전부터는 MAXIFS 함수를 사용하면 됩니다.
      =MAXIFS(C2:C5,A2:A5,"가스")
      감사합니다.

      답글
    1. Admin

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

      답글
  6. 파라

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

    답글
    1. Admin

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

      답글
  7. 익명

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

    답글
  8. 익명

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

    답글
  9. 익명

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

    답글
  10. 익명

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

    답글
    1. Admin

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

      답글
  12. 익명

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

    답글
  13. 익명

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

    답글
  14. 마성주

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

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

    답글
    1. Admin

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

      답글
  15. chsoooo

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

    답글
    1. Admin

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

      답글
  16. 써니

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

    답글
    1. Admin

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

      답글
  17. 영블리

    ㅜㅜㅜㅜ다중조건함수 몇날며칠을 검색했는지 모르겠어요...
    진짜진짜 감사합니다 복받으실거에요ㅜ.ㅜ.ㅜ.ㅜ.ㅜ.ㅜ.ㅜ.ㅜ

    답글
  18. 익명

    감사합니다!! 오래전부터 찾고 있던 건데 이해가 쉽게 올려주셔서 한 번에 처리됐어요!

    답글
  19. 나미

    왜 1이 다중조건에서 제일 앞에 붙는지 궁금했는데 완벽하게 이해했어요 감사합니다 !

    답글
  21. 남영희

    안녕하세요 글 잘 봤습니다~!!
    저도 적용을 해 보았는데요... 모든 값이 0으로 나오는데 왜그러는 걸까요??ㅠ

    답글
  22. ㅇㅇ

    어느순간부터 이 함수를 이용하면 엑셀에 렉이 심하게걸리네요 ㅠㅠ

    답글
    1. Admin

      안녕하세요. 지정하는 범위가 너무 넓으면 속도가 느릴 수 있습니다. Data가 많아서 지정하는 범위가 넓다면 어쩔 수 없는 문제일것 같고요, 필요한 범위내로 줄일 수 있다면 속도가 빨라질것 같습니다.

      답글
  23. 고차장

    안녕하세요..많은 도움 받고 있습니다.
    궁금한 사항은...위 내용은 다중조건에 맞는 값 한개만 찾는건데요..
    만약 다중 조건에 맞는 값이 여러개일때..그 각각의 셀값을
    가져오게 하려면 어떻게 하면 될까요?
    예를 들어 조건1, 조건2 에 만족하는 각 4개의 셀의 값이 A,B,C,D일 때 ,,
    A값이 나온 셀을 밑으로 복사하면 자동으로 B,C,D의 값이 나오게요...

    답글
  24. ddd

    두 조건을 모두 만족하는게 아니라 두 조건중 하나라도 만족하면 출력되게하려면 어떻게 해야할까요?

    답글
    1. Admin

      조건을 모두 만족
      =INDEX($E$6:$E$14,MATCH(1,($B$6:$B$14=C19)*($D$6:$D$14=B19),0))

      두 조건 중 하나라도 만족
      =INDEX($E$6:$E$14,MATCH(1,(($B$6:$B$14=C19)+($D$6:$D$14=B19)>0)*1,0))

      첫번째 수식은 비교값을 곱해서 모두 만족하면 결과를 구하지만
      두번째 수식은 비교값을 더해서 하나라도 만족하면 TRUE를 반환하므로 원하는 결과가 구해집니다.
      수식의 계산 과정이 복잡합니다.
      리본 메뉴 > 수식 > 수식 분석 그룹 > 수식 계산 을 클릭해서 계산단계를 확인할 수 있습니다.

      답글
  27. 익명

    결합연산자 묶어서 찾기의 경우 값 표시가 행열로 되어있으면 사용이 안될까요?

    답글
  28. 익명

    2. & 기호(결합연산자)로 묶어서 찾기
    의경우 찾는 값이 예로들면 코드는행으로 연도는 열로 표시 되어 있고 교차지점에 값이 들어있으면
    적용 방법이 없을까요? 에러가 나오내요

    답글
  29. 익명

    INDEX 내부에 MATCH,MATCH 로 넣은면 문제가 없는데 &기호 결합연사자 묶기로는 안되네요 ㅠㅠ

    답글

댓글 남기기

Scroll to Top