엑셀 VLOOKUP 함수 다중조건 검색 :: 공식 사용법 총정리

VLOOKUP 함수 다중조건 검색 공식의 동작원리와 실전 응용예제를 단계별로 알아봅니다.

홈페이지 » 엑셀 VLOOKUP 함수 다중조건 검색 :: 간단 공식

엑셀 VLOOKUP 다중조건 공식 :: 여러 개 조건으로 값을 찾는 방법! 

사용된 공식
= { INDEX(출력범위, MATCH(1, (조건1=조건범위1)*(조건2=조건범위2)*..., 0)) }

[! 주의] 해당 공식은 배열수식이므로 Ctrl + Shift + Enter 로 '꼭! 반드시!' 입력해야 합니다.
그렇지 않으면 #VALUE! 오류가 출력됩니다.

인수 설명
엑셀 vlookup 다중조건 인수 설명
엑셀 VLOOKUP 다중조건 공식에 사용된 인수
인수 설명
출력범위 출력될 값이 입력된 범위입니다. (*조건범위, 출력범위의 높이/너비는 반드시 동일해야 합니다.)
조건범위1 첫번째 조건이 입력된 범위입니다.
조건1 첫번째 조건입니다.
조건범위2 두번째 조건이 입력된 범위입니다.
조건2 두번째 조건입니다.

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [실무기초] VLOOKUP 함수 다중조건 검색
    예제파일

관련 강의

INDEX/MATCH 함수의 기본 동작원리가 궁금하시다면 아래 영상강의를 참고해주세요.

영상강의
큰 화면으로 보기

공식에 대한 설명

INDEX / MATCH 함수를 응용한 공식입니다. VLOOKUP 함수를 '여러개의 조건'으로 검색하여 사용할 수 있습니다. INDEX/MATCH 공식의 기초 사용법관련포스트를 참고하세요.

해당 공식은 각 조건범위가 세로 또는 가로로 같은 방향일 경우에만 사용할 수 있습니다. 예를들어, 조건범위1는 가로방향이고 조건범위2가 세로방향일 경우 사용할 수 없습니다. 만약 가로/세로 다른 방향으로 여러개 조건을 만족하는 값을 찾아야 할 경우, INDEX함수의 3번째 인수를 추가하여 공식을 작성합니다. INDEX/MATCH 함수의 고급사용법관련 포스트에서 자세히 설명드렸습니다.

VLOOKUP 함수 다중조건 공식 단계별 사용법

예제파일에 사용된 공식
= { INDEX($D$7:$D$18, MATCH(1, ($B$7:$B$18=H8)*($C$7:$C$18=H7), 0)) }
공식의 동작 원리
  1. 첫번째로, 여러개의 조건범위에서 각 조건을 모두 만족하는 행의 위치를 구합니다. MATCH 함수를 사용합니다.
    조건1 : $B$7:$B$18 = '품목'
    조건2 : $C$7:$C$18 = '주문처'
    MATCH 함수는 조건1과 조건2를 동시에 만족하는 행의 번호를 반환합니다.
  2. MATCH 함수에 입력되는 배열의 곱셈을 살펴보겠습니다.
    = {저지방우유, 하루우유, 하루우유, 저지방우유, 딸기맛우유, ...}  '// B7:B18에 입력된 품목입니다
    = {오빠두식당, 오빠두식당, 엑셀천국, 엑셀천국, 엑셀천국, ...}  '// C7:C18에 입력된 주문처입니다.
    '// 검색항목으로, '하루우유'와 '오빠두식당'을 검색할 경우, 두 배열의 곱셈은 아래와 같이 출력됩니다.
    = {품목범위=하루우유} * {주문처범위=오빠두식당}
    = {0, 1, 1, 0, 0, ..} * {1, 1, 0, 0, 0, ..}
    = {0, 1, 0, 0, 0, ..}  '// 두 배열의 곱셈을 계산합니다.
  3. 이제 두 배열의 곱셈을 MATCH 함수에 넣어보겠습니다.
    = MATCH(1, ('품목범위='품목')*('주문처범위'='주문처'), 0)
    = MATCH(1, {0, 1, 0, 0, 0, ...}, 0)
    = 2  '// MATCH 함수는 해당 범위에서 '1' 을 찾습니다. 배열에서 '1'이 두번째에 위치하므로 결과값으로 '2' 를 반환합니다.
  4. 이제 INDEX 함수의 동작원리를 알아봅니다. INDEX 함수는 범위에서 '해당 순번'에 위치하는 값을 반환합니다.
    = INDEX($D$7:$D$18, 2'// MATCH 함수의 결과값으로 2가 반환되어 INDEX 함수에 입력됩니다.
    = INDEX({1200, 1350, 1340, 1300, ...}, 2)
    = 1350   '// 범위에서 2번쨰 위치한 1350이 반환됩니다.
  5. 즉, 다시 정리하자면
    첫번째 : 다중조건으로 검색할 '하루우유'와 '오빠두식당'이 모두 일치하는 행의 번호는 2번째이다. (MATCH 함수)
    두번째 : 따라서 출력범위에서 2번째에 있는 항목인 1350을 출력한다. (INDEX 함수)

링크] MS 홈페이지 VLOOKUP 함수 사용법 상세설명

4.9 26 투표
게시글평점
guest
59 댓글
Inline Feedbacks
모든 댓글 보기
오세창
오세창
2019년 9월 15일 12:35 오후

안녕하세요.
강의 잘보고, 잘듣고 있습니다.
감사합니다.
예제 파일 ppt 빼고 나머지 모두 다운로드가 안되네요.

오빠두엑셀
2019년 9월 22일 5:55 오후
답글 남기기  오세창

안녕하세요? 오빠두엑셀입니다.
첨부파일에 문제가 생겼었습니다 🙂 확인 감사드려요.
문제 해결되었으니 다시 시도해주시겠어요? 감사합니다.

김진현
김진현
2019년 12월 27일 2:56 오전
게시글평점 :
     

안녕하세요. 강의 잘 보고 있습니다. 질문이 하나 있어서요.. 예전에 동적범위 강의를 보고, 이번 공식에 적용하고 싶어서 출력범위에 offset함수로 동적범위를 적용했습니다. 그런데 어떤건 옳은값이 나오고 어떤건 #REF 에러가 나오는데요 ㅠ.ㅠ 왜… 더보기 »

ㅇㅇㅇ
ㅇㅇㅇ
2020년 1월 22일 3:46 오후
게시글평점 :
     

감사합니다!

충
2020년 2월 19일 10:26 오후
게시글평점 :
     

감사합니다. 많이 배우고 있습니다. 오늘도 또 많이 배우고 갑니다.

우후대박
우후대박
2020년 3월 3일 11:33 오전
게시글평점 :
     

다중 조건 찾고 있었는데, 실마리가 보입니다. 아래 분처럼 동적범위 설정은 다음 스텝으로 진행해봐야겠습니다. 다만, 아이템 하나를 검색하는데 다중조건함수를 50개 이상 걸어 놓으니 속도 저하가 상당하네요. 품질 부서에 있다보니 아이템 하나에… 더보기 »

조인환
조인환
2020년 3월 4일 10:38 오전
답글 남기기  오빠두엑셀

추가 작업을 망설이고 있었는데, 눈앞이 환해지는것 같습니다. 감사합니다 ^^

사랑하는님
사랑하는님
2020년 3월 4일 4:15 오후
게시글평점 :
     

2개의 행과 여러개의 열에 한건의 데이타를 1개의 행과 열에 데이타를 가져오기를 한는데 어떤 함수조합을 사용해야하나요 A B C D E 1 순번 공사명 착공일 감독자 총공사비 2 공사번호 준공일 감리자… 더보기 »

사랑하는님
사랑하는님
2020년 3월 6일 11:08 오전
답글 남기기  오빠두엑셀

댓글 추가하기...
너무 감사합니다.
위 공식을 응용하니 쉽게 빠르게 되네요!

초코
초코
2020년 3월 4일 10:07 오후
게시글평점 :
     

감사합니다 잘보겠습니다

김기태
김기태
2020년 3월 18일 10:07 오전
게시글평점 :
     

다중서식 왜 안되나 끙끙 앓다가, 이 서식 보고 문제를 해결하여 암이 나았습니다

호야
호야
2020년 3월 18일 3:48 오후
게시글평점 :
     

잘보고 갑니다요

엑마
엑마
2020년 3월 19일 1:23 오후
게시글평점 :
     

덕분에 엄청나게 많은 엑셀작업을 효율화할 수 있게 되었습니다! 이번에 배운것을 적용 중 궁금한 점이 있어 질문남깁니다!! 배열 곱하는 부분에서 $D$2:$D$10="2017-01-01"이런식으로 남기면 날짜를 읽지 못하더라구요!! 그런데 일반서식입히면 나타나는 넘버 43647을 입력하면,… 더보기 »

강슬비
강슬비
2020년 3월 25일 1:35 오후
게시글평점 :
     

너무유용하게 활용하다가 막힌부분이있는데 직급과 호봉에따른 본봉을 엑셀로 정리하려는데 어렵네요 =INDEX($C$2:$C$11,MATCH(,($A$2:$A$11=F3)*($B$2:$B$11=G3),0),1) => 이공식중에 혹시 잘못된부분있을까요??a가직금b가 호봉c가본봉이에요 #N/A가 계속뜨네요

안태현
안태현
2020년 3월 31일 7:19 오후
게시글평점 :
     

강의 잘보고, 들었습니다.
저는 함수를 해도 모르겠는데 강의보니까 공부가 잘되는거같네요
도움이 되는거같아요 감사합니다. 함수공부도해야되는데 회사에서도 사무보조만 하고있어요
회사에서는 함수는하지않지만 그래도 공부를해놔야될거같아 공부했어요. 도움주셔서 감사드립니다.

민찬이아빠
민찬이아빠
2020년 4월 10일 1:01 오후
게시글평점 :
     

이걸로 vlookup의 한계를 넘어셨습니다. 감사합니다.

ruy91
ruy91
2020년 4월 16일 10:46 오전
게시글평점 :
     

문의드립니다 우선 index match 이용해서 다중조건으로 함수 적용중에 n/a 오류가 있어 확인해보니 배열수식 문제인걸로 사료되어 수식입력 후 ctrl shift enter 를 적용해야 된다고 하는데 교육자료엔 해당내용이 없는데 확인부탁드리며 조건값에 절대침조… 더보기 »

jetpi****
jetpi****
2020년 5월 19일 3:49 오후
게시글평점 :
     

안녕하세요. 좋은 강의 감사드립니다. 해당 다중조건으로 응용하는 연습 중에 혹시 행/열 둘다 다중조건으로 아래와 같이 시도 해봤는데 안되는데 혹시 행/열 둘다 다중조건으로 할 수 없나요? = { INDEX(출력범위, MATCH(1, (행조건1=조건범위1)*(행조건2=조건범위2),0)… 더보기 »

김민수
김민수
2020년 6월 3일 10:53 오전
게시글평점 :
     

=(INDEX($D$7:$D$18,MATCH(1,(G8=B7:$B$18)*(H8=C7:$C$18),0)))
판매가 공식을 이렇게 적용하였는데, #N/A오류가 납니다.. 뭐가 문제일까요?

권아랑
권아랑
2020년 7월 3일 9:59 오전
답글 남기기  오빠두엑셀

만들어주신 예시파일에서 희안하게도.... 다시계산만 누르면 값이없음이라고 뜹니다. 거기서 다시 수식창에서 F9누르면 원래대로 서초동 나오고 수식은 사라지구요..ㅜㅜ

권아랑
권아랑
2020년 7월 27일 8:37 오전
답글 남기기  오빠두엑셀

아앗 첫줄에 나와있는데도 무슨말인지 몰랐었네요...ㅠㅠㅋㅋㅋㅋ 감사합니다!!

엑셀늅늅
엑셀늅늅
2020년 7월 24일 6:21 오후
게시글평점 :
     

혹시 Index, Match로 찾은 값의 오른쪽 셀의 값을 찾으려면 어떻게 해야 하나요?
추가로 알려주실 수 있으실까요?

Last edited 1 년 전 by 엑셀늅늅이
박상용
박상용
2020년 8월 29일 6:26 오후
게시글평점 :
     

혹시 조건이 4개거나 하면 안되는건지요?, 혹시 열에 다른 정보들이 있거나, 중간에 다른 정보들이 있으면 위의 함수가 안먹히는 건지요

강병준
강병준
2020년 9월 11일 7:28 오후
게시글평점 :
     

잘 봤습니다! 그런데 여러 개의 조건으로 검색하여서 부합한 값을 한 개만 출력하고 있는데, 여러 개의 값을 출력하게 할 수는 없을까요? 된다면 어떻게 해야 할까요?

바키z
바키z
2020년 10월 20일 1:21 오전
답글 남기기  오빠두엑셀

안녕하세요~! 덕분에 정말 엑셀 함수 잘 활용하고 있습니다!! 정말 감사합니다. 그런데 위 링크와 인덱스매칭 함수를 둘 적용해서 어떻게 해야할지 해서 문의드립니다...ㅠㅠ 만약 조건을 "회사명, 입사년도"로 걸고, 해당하는 "사람이름"들을 모두 출력하려는… 더보기 »

미성
미성
2020년 10월 15일 11:24 오전
게시글평점 :
     

잘 이해는 못 했지만 감사합니다
다시 여러번 반복하여 내것으로 만들겠습니다

이주원
이주원
2020년 10월 19일 4:32 오후
게시글평점 :
     

안녕하세요!

=INDEX($C$4:$JU$18,match($B29,$B$4:$B$18,0), MATCH(1,ARRAYFORMULA(($C$1:$JU$1=C$26)*($C$2:$JU$2=C$27)*($C$3:$JU$3=C$28)),0))

이렇게 식을 입력했는데 첫번째 조건에 부합하지 않는 값을 불러오거나, #N/A 값이 뜹니다! 어떻게 해결할 수 있을까요??

이주원
이주원
2020년 10월 20일 11:30 오전
답글 남기기  오빠두엑셀

도움주셔서 정말 감사합니다! 말씀주신대로 첫 번째 인수를 C4:JU4로 변경하였으나, "Function INDEX parameter 2 value 2 is out of range." 라는 오류가 나옵니다! *(아래 행들은 Function INDEX parameter 2 value 3… 더보기 »

이주원
이주원
2020년 10월 20일 1:48 오후
답글 남기기  이주원

혹시 조건 셀이 셀병합된 셀이면 인식오류?가 생기거나 할 수도 있을까요?

Fainter
2020년 10월 21일 8:53 오전
게시글평점 :
     

늦은 새벽에 강의만드시느라 목소리가 차분하시네요. 이번 강의로 index/match 함수 정확히 이해했습니다. 다만 응용을 잘 할수 있을까? 인데...잘 해봐야겠죠 ^^ 밑에 댓글들의 질문 답글을 보고 여러가지로 더 응용해서 배우고 있습니다. 감사합니다… 더보기 »

너구리
너구리
2020년 11월 7일 2:10 오후
게시글평점 :
     

대단히 많은 도움이 되었습니다..

김종우
김종우
2020년 11월 25일 9:29 오전
게시글평점 :
     

excel2013을 쓰고있는 직장인입니다. 중복값제거후 한번에 정렬이 되길 원하여 index/match함수를 꼭 쓰고싶은데 예제파일에서는 잘 작동하는것을 확인했지만 막상 제가 하려고하는 엑셀파일에서 적용시킬려고하면 반환값이 다 0으로 고정되어 나옵니다. 머리가 너무 아파 지푸라기 잡는심정으로… 더보기 »

박원규
박원규
2020년 12월 30일 5:40 오후
게시글평점 :
     

감사합니다/.

chang
chang
2021년 2월 8일 2:57 오전
게시글평점 :
     

Ctrl+Shitfe+Enter로 입력해도 배열 수식이 입력이 안되는 것 같아요.

calroid
calroid
2021년 2월 11일 3:19 오후
게시글평점 :
     

예재파일 받아서 보고있습니다.
주문처와 품목을 바꾸면 판매가와 배송지역은 잘 바뀌는데
판매가와 배송지역 서식에 클릭하고 나면 값이 없음으로 바뀌어버립니다.
왜이럴까요?
엑셀 버전이 달라서 그런지..궁금합니다.
엑셀은 2019버전이거든요

고이자
고이자
2021년 4월 9일 5:29 오후
게시글평점 :
     

좋은 강의 글 감사드립니다. 수식중에 match(1, ~~ ) <- 요 부분이 이해가 잘 안되는데, 해당 범위에 1이 없는데 어떻게 2라는 숫자가 반환되는건가요?

반33
반33
2021년 4월 21일 3:38 오후
게시글평점 :
     

감사합니다

basetouch
basetouch
2021년 4월 23일 9:58 오전
게시글평점 :
     

좋은 정보 감사해요!
혹시 게시글 스크랩 기능 있을까요? 저장해놓고 나중에 보려구요

nkloverani
nkloverani
2021년 5월 26일 2:18 오후
게시글평점 :
     

1000명정도 되는 기업의 직원 비상연락망 통합파일에서 교육을 신청한 직원들의 연락처를 추출하고자 할 경우 동명이이인 있기 때문에 부서명과 성명의 일치조건을 사용하여 데이터를 추출 하는데 잘 안됩니다.. 함수를 제대로 사용했는지 여부 도움부탁드립니다.… 더보기 »

월화수목금퇼
월화수목금퇼
2021년 10월 25일 9:57 오전
게시글평점 :
     

감사합니다!!

59
0
여러분의 생각을 댓글로 남겨주세요.x