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

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

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

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

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

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

공식에 대한 설명

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

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

VLOOKUP 함수 다중조건
여러개의 조건을 만족하는 VLOOKUP 함수 공식을 알아봅니다.
예제파일 다운로드
영상강의

공식에 사용된 기초함수
선택한 범위에서 원하는 위치의 값을 반환합니다.
범위 내 찾는값의 위치(순번)을 반환합니다.

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 함수)

관련 함수 공식 살펴보기

VLOOKUP 참조 범위의 마지막 값을 반환합니다.
지정한 범위에서 찾는값과 일치하는 그림을 출력합니다.
범위에서 값을 찾아 같은 행의 다른 값을 반환합니다.
INDEX / MATCH 함수의 다양한 실전 응용방법을 알아봅니다.
2중 조건을 만족하는 데이터유효성 목록상자를 생성합니다.

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

4.9 18 votes
게시글평점
현재 페이지 댓글알림 신청
알림 설정
guest
37 Comments
Inline Feedbacks
View all comments
오세창
오세창
2019년 9월 15일 12:35 오후

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

오빠두엑셀
2019년 9월 22일 5:55 오후
Reply to  오세창

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

김진현
김진현
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****
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 2 월 전 by 엑셀늅늅이
박상용
박상용
2020년 8월 29일 6:26 오후
게시글평점 :
     

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

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

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

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