엑셀 VLOOKUP 다중조건 공식 :: 여러 개 조건으로 값을 찾는 방법!
사용된 공식
= { INDEX(출력범위, MATCH(1, (조건1=조건범위1)*(조건2=조건범위2)*..., 0)) }
[! 주의] 해당 공식은 배열수식이므로 Ctrl + Shift + Enter 로 '꼭! 반드시!' 입력해야 합니다.
그렇지 않으면 #VALUE! 오류가 출력됩니다.
인수 설명
| 인수 | 설명 |
| 출력범위 | 출력될 값이 입력된 범위입니다. (*조건범위, 출력범위의 높이/너비는 반드시 동일해야 합니다.) |
| 조건범위1 | 첫번째 조건이 입력된 범위입니다. |
| 조건1 | 첫번째 조건입니다. |
| 조건범위2 | 두번째 조건이 입력된 범위입니다. |
| 조건2 | 두번째 조건입니다. |
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] VLOOKUP 함수 다중조건 검색예제파일
관련 강의
INDEX/MATCH 함수의 기본 동작원리가 궁금하시다면 아래 영상강의를 참고해주세요.
영상강의
공식에 대한 설명
INDEX / MATCH 함수를 응용한 공식입니다. VLOOKUP 함수를 '여러개의 조건'으로 검색하여 사용할 수 있습니다. INDEX/MATCH 공식의 기초 사용법은 관련포스트를 참고하세요.
해당 공식은 각 조건범위가 세로 또는 가로로 같은 방향일 경우에만 사용할 수 있습니다. 예를들어, 조건범위1는 가로방향이고 조건범위2가 세로방향일 경우 사용할 수 없습니다. 만약 가로/세로 다른 방향으로 여러개 조건을 만족하는 값을 찾아야 할 경우, INDEX함수의 3번째 인수를 추가하여 공식을 작성합니다. INDEX/MATCH 함수의 고급사용법은 관련 포스트에서 자세히 설명드렸습니다.
예제파일에 사용된 공식
= { INDEX($D$7:$D$18, MATCH(1, ($B$7:$B$18=H8)*($C$7:$C$18=H7), 0)) }
공식의 동작 원리
- 첫번째로, 여러개의 조건범위에서 각 조건을 모두 만족하는 행의 위치를 구합니다. MATCH 함수를 사용합니다.
조건1 : $B$7:$B$18 = '품목'
조건2 : $C$7:$C$18 = '주문처'
MATCH 함수는 조건1과 조건2를 동시에 만족하는 행의 번호를 반환합니다. - MATCH 함수에 입력되는 배열의 곱셈을 살펴보겠습니다.
= {저지방우유, 하루우유, 하루우유, 저지방우유, 딸기맛우유, ...} '// B7:B18에 입력된 품목입니다
= {오빠두식당, 오빠두식당, 엑셀천국, 엑셀천국, 엑셀천국, ...} '// C7:C18에 입력된 주문처입니다.'// 검색항목으로, '하루우유'와 '오빠두식당'을 검색할 경우, 두 배열의 곱셈은 아래와 같이 출력됩니다.
= {품목범위=하루우유} * {주문처범위=오빠두식당}
= {0, 1, 1, 0, 0, ..} * {1, 1, 0, 0, 0, ..}
= {0, 1, 0, 0, 0, ..} '// 두 배열의 곱셈을 계산합니다. - 이제 두 배열의 곱셈을 MATCH 함수에 넣어보겠습니다.
= MATCH(1, ('품목범위='품목')*('주문처범위'='주문처'), 0)
= MATCH(1, {0, 1, 0, 0, 0, ...}, 0)
= 2 '// MATCH 함수는 해당 범위에서 '1' 을 찾습니다. 배열에서 '1'이 두번째에 위치하므로 결과값으로 '2' 를 반환합니다. - 이제 INDEX 함수의 동작원리를 알아봅니다. INDEX 함수는 범위에서 '해당 순번'에 위치하는 값을 반환합니다.
= INDEX($D$7:$D$18, 2) '// MATCH 함수의 결과값으로 2가 반환되어 INDEX 함수에 입력됩니다.
= INDEX({1200, 1350, 1340, 1300, ...}, 2)
= 1350 '// 범위에서 2번쨰 위치한 1350이 반환됩니다. - 즉, 다시 정리하자면
첫번째 : 다중조건으로 검색할 '하루우유'와 '오빠두식당'이 모두 일치하는 행의 번호는 2번째이다. (MATCH 함수)
두번째 : 따라서 출력범위에서 2번째에 있는 항목인 1350을 출력한다. (INDEX 함수)
강의 잘보고, 잘듣고 있습니다.
감사합니다.
예제 파일 ppt 빼고 나머지 모두 다운로드가 안되네요.
첨부파일에 문제가 생겼었습니다 :) 확인 감사드려요.
문제 해결되었으니 다시 시도해주시겠어요? 감사합니다.
예전에 동적범위 강의를 보고, 이번 공식에 적용하고 싶어서 출력범위에 offset함수로 동적범위를 적용했습니다.
그런데 어떤건 옳은값이 나오고 어떤건 #REF 에러가 나오는데요 ㅠ.ㅠ 왜 그러는지 알 수 있을까요?
= { INDEX(출력범위, MATCH(1, (조건1=조건범위1)*(조건2=조건범위2)*…, 0)) }
공식에서 [출력범위]에 동적범위를 적용하셨다는 말씀이시지요?
출력범위말고도 각 [조건범위]에도 동적범위를 적용해보시겠어요?
출력범위는 늘어난반면 조건범위는 그대로일경우 MATCH 함수로 올지않은값이 반환되서 그럴수 있습니다.^_^
다만, 아이템 하나를 검색하는데 다중조건함수를 50개 이상 걸어 놓으니 속도 저하가 상당하네요.
품질 부서에 있다보니 아이템 하나에 측정해야할 치수가 50개 이상이라 어쩔수 없는것 같아요.
궁금한 점은 출력범위, 조건범위를 A:A 형태가 나은지, 동적 범위를 설정하는게 나은지.. 속도 측면에서 어떤게 유리한가요?
맞습니다. 해당 공식은 '배열수식'이므로, 보통 1만행이상 데이터에 적용시에는 상당한 속도저하가 발생합니다.
대안책으로 값1&값2&값3.. 을 연결하여 별도의 열에 출력한뒤, 값1&값2&값3을 vlookup 함수로 검색하는 방법도 고려하실 수 있습니다 ^^
배열수식을 사용할경우, 전체열(A:A) 대신 동적범위로 사용하시는게 속도측면에서 유리합니다.
제 답변이 도움이 되셨길 바랍니다 ^^
감사합니다!
1개의 행과 열에 데이타를 가져오기를 한는데
어떤 함수조합을 사용해야하나요
A B C D E
1 순번 공사명 착공일 감독자 총공사비
2 공사번호 준공일 감리자 정산금액
3 1 OO공사 2019-05-20 홍길동 100,000,000
4 가20-01 2020-01-31 김갑동 95,000,000
5 2 AA공사 ............. ........ .................
6 가20-02 .............. ....... .................
A B C D E F G H
1 공사명 공사번호 착공일 준공일 감독자 감리자 총공사비 정산금액
다른 F1셀과 F2셀에 각각 아래 두 공식을 입력한 뒤 아래로 자동채우기 해 보시겠어요?^^
=INDIRECT("A"&ROW()) | =INDIRECT("A"&ROW()*2)
그럼 한줄로 데이터가 불러와집니다 ^^
위 공식을 적절히 응용하셔서 데이터를 정규화하신 뒤, INDEX MATCH 함수를 적용해보시면 될듯합니다.
제 답변이 도움이 되셨길 바랍니다.
감사합니다.
너무 감사합니다.
위 공식을 응용하니 쉽게 빠르게 되네요!
이번에 배운것을 적용 중 궁금한 점이 있어 질문남깁니다!!
배열 곱하는 부분에서 $D$2:$D$10="2017-01-01"이런식으로 남기면
날짜를 읽지 못하더라구요!! 그런데 일반서식입히면 나타나는 넘버 43647을 입력하면, 또 되구요
만약에 모든 함수에서 "2019-01-01"같은 날짜를 인식하지 못하는 것이면 이해가 가겠지만, COUNTIF같은 다른함수에서는 다 가능하더라구요.. 그런데 왜 배열곱붑분에서만 날짜를 인식하지 못하는 것인지 궁금합니다 ㅜ
항상 감사드립니다.
"2017-01-01"은 텍스트입니다. 따라서, D2:D10=DATE(2017,1,1)로 입력하시면 잘 동작하게 됩니다.
IF함수나 SUMPRODUCT 함수, MATCH 함수는 조건으로 '배열' (=논리식)을 받아옵니다.
반면 COUNTIF, SUMIF 함수는 조건으로 '범위'를 받아옵니다. (예: COUNTIF(조건범위,조건))
즉 COUNTIF 함수의 조건으로 "2017-01-01"를 입력하면, 기존값은 텍스트가 맞으나 함수 자체적으로 '날짜형식의 텍스트'는 날짜로 처리하므로 "2017-01-01"로 입력해도 잘 동작하게 됩니다.
조건으로 배열을 받아올 경우, 조건안에 다른 함수를 적용할 수 있는 장점이 있습니다.
예를들어, MONTH(D2:D10)=1 을 조건으로 넣으면 1월달인 값만 확인할 수가 있습니다.
하지만 SUMIF함수나 COUNTIF 함수는 조건으로 범위가 들어가므로, 조건범위안에 다른 함수를 동시에 사용할 수 없는 단점이 있습니다.
제 답변이 도움이 되셨길 바랍니다.
감사합니다.^^