메뉴

VLOOKUP 함수 여러개 값 가져오기 공식 총정리 :: 엑셀 함수 공식

VLOOKUP 함수 결과값으로 여러개의 값을 세로/가로 방향으로 출력하는 엑셀 함수 공식의 동작원리와 실전 예제를 알아봅니다.

작성자 :
오빠두엑셀
최종 수정일 : 2021. 09. 03. 11:00
URL 복사
메모 남기기 : (239)
VLOOKUP 함수 여러개 값 가져오기 공식 썸네일크기
작성자:

VLOOKUP 함수 여러개 값 출력 공식 총정리 (세로/가로 방향 모두!)

목차 바로가기
  1. 예제파일 다운로드
  2. VLOOKUP 여러개 값을 세로로 반환하는 공식
  3. VLOOKUP 여러개 값을 가로로 반환하는 공식
  4. VLOOKUP 여러값/여러필드를 동시에 반환하는 공식
  5. VLOOKUP 여러값/여러필드를 동적으로 반환하는 공식
  6. VLOOKUP 여러값 중 '고유값'만 출력하는 공식 (간단수식 or 배열수식)
  7. VLOOKUP 두 날짜 사이를 만족하는 여러값 불러오기 공식
  8. VLOOKUP 다중 조건을 만족하는 여러개 값 반환하는 공식
  9. 각 공식에 사용된 인수 설명
  10. VLOOKUP 여러개 값 출력 공식의 동작 원리
  11. 링크 : VLOOKUP 다중조건 검색하기 공식

현재 오피스 365 최신버전을 사용중이시라면, FILTER 함수를 사용해보세요!
더욱 편리하게 VLOOKUP 함수 여러 값 불러오기를 구현할 수 있습니다.

FILTER 함수 사용법 바로가기

영상 강의 업데이트!

공식의 동작원리 및 실전 사용예제를 영상강의로 준비해드렸습니다! 🙂

새로운 공식 업데이트!

여러 조건을 동시에 검색해야 할 경우, 각각의 조건을 만족하는 모든 결과를 동시에 출력하는 방법에 대해 많은 분께서 질문해주셨습니다. 여러 조건을 동시에 만족하는 여러개 값을 한 번에 출력하는 공식은 아래 공식을 참고해주세요.

엑셀 여러 조건 여러 값 출력 GIF
엑셀 VLOOKUP 여러 조건 여러개 값 출력하기 공식 예제

함수 공식

  1. VLOOKUP 여러개 값을 세로로 반환하는 공식
    = INDEX($출력범위, SMALL(IF(($찾을값=$찾을범위),MATCH(ROW($찾을범위), ROW($찾을범위)), ""), ROWS($A$1:A1)))

    VLOOKUP 여러개 값 가져오기 세로방향
    VLOOKUP 여러개 값을 세로로 반환하는 공식입니다.
  2. VLOOKUP 여러개 값을 가로로 반환하는 공식
    = INDEX($출력범위,SMALL(IF($찾을값=$찾을범위,ROW($찾을범위)-MIN(ROW($찾을범위))+1, ""),COLUMN(A1)))

    VLOOKUP 여러개 가져오기 가로방향
    VLOOKUP 여러개 값을 가로 방향으로 반환하는 공식입니다.
  3. VLOOKUP 여러 필드의 값을 동시에 반환하는 공식 [COLUMNS 함수 이용]
    COLUMNS 함수를 이용하여 첫번째, 두번째, 세번째.. 열을 순서대로 반환합니다.

    = INDEX($출력범위, SMALL(IF(($찾을값=$찾을범위),MATCH(ROW($찾을범위), ROW($찾을범위)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))

    VLOOKUP 여러값 가져오기 여러필드 동시출력
    여러필드의 값을 동시에 출력할 수도 있습니다
  4. VLOOKUP 여러개 필드값을 동적으로 반환하는 공식 [MATCH 함수 이용]
    MATCH 함수를 이용합니다. 필드 머릿글을 바꾸면 출력범위가 동적으로 변하는 공식입니다. %머릿글은 행고정참조(예: A$10 형태)로 입력하는것에 주의합니다.

    = INDEX($출력범위, SMALL(IF(($찾을값=$찾을범위),MATCH(ROW($찾을범위), ROW($찾을범위)), ""), ROWS($A$1:A1)), MATCH($머릿글, $찾을머릿글범위, 0))

    VLOOKUP 여러개값 여러필드 동적출력

  5. VLOOKUP 여러개 값 중 '고유값'만 출력하는 공식 (간단수식 or 배열수식)
    중복값 제거 공식을 응용하여 VLOOKUP 고유값 출력 공식을 작성할 수 있습니다. 중복값 제거 공식에 대한 자세한 설명은 관련 포스트를 참고하세요.

    엑셀 중복값제거(고유값추출) 함수공식 설명 바로가기

    아래 수식은 간단수식이으로 ENTER로만 입력합니다. 고유값을 '역방향'으로 출력합니다.

    =LOOKUP(2, 1/((COUNTIF($머릿글:머릿글, $출력범위)=0)*($찾을값=$찾을범위)), $출력범위)

    만약 고유값을 정방향으로 출력해야 할 경우, 아래 배열수식을 사용합니다.

    = INDEX($출력범위,MATCH(1,(COUNTIF($머릿글:머릿글,$출력범위)=0)*($찾을값=$찾을범위),0))

    VLOOKUP 여러 값 중 고유값만 출력 설명
    여러값 중 고유값만 반환할 수도 있습니다.
  6. VLOOKUP 두 날짜 사이를 만족하는 여러값 불러오기 공식
    = INDEX($출력범위, SMALL(IF(($시작일<=$날짜범위)*($종료일>=$날짜범위),MATCH(ROW($찾을범위), ROW($찾을범위)), ""), ROWS($A$1:A1)))

    VLOOKUP 두 날짜 사이 만족 여러값 출력
    두 날짜사이 조건을 만족하는 여러값을 출력합니다.
  7. VLOOKUP 여러 조건을 만족하는 여러개 값 반환하는 공식
    = INDEX($출력범위, SMALL(IF(($찾을값1=$찾을범위1)*($찾을값2=$찾을범위2),MATCH(ROW($찾을범위), ROW($찾을범위)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))

    엑셀 vlookup 여러개 다중 조건
    여러 조건을 만족하는 VLOOKUP 여러개 값 반환 공식

 해당 포스트에 적어드린 공식은 모두 배열수식이므로 반드시 CTRL + SHIFT + ENTER 로 입력하는 것을 주의하세요!

인수 설명

VLOOKUP 여러개 값 출력 : 세로/가로, 1개 필드만 출력하는 경우
VLOOKUP 함수 여러개 값 출력 인수 설명
VLOOKUP 여러개 값 가져오기 공식에 들어갈 인수입니다.
  • $찾을범위 : 찾을값을 조회할 범위입니다. (  VLOOKUP 범위의 맨 좌측열)
  • $출력범위 : 출력할 값이 나열된 범위입니다. (  VLOOKUP 범위 중 출력할 열)
  • $찾을값 : 찾을범위에서 찾을 값입니다. (  VLOOKUP 함수의 첫번째 인수)
VLOOKUP 여러개 값 출력 : 여러 필드를 동시에 출력하는 경우
VLOOKUP 여러값 출력 여러필드 인수설명_2
여러 필드를 동시에 출력하는 공식에 사용된 인수입니다
  • $찾을범위 : 찾을값을 조회할 범위입니다.
  • $출력범위 : 출력할 값이 나열된 범위입니다.
  • $찾을값 : 찾을 범위에서 찾을 값입니다.
  • $머릿글 : 출력할 범위의 머릿글입니다. (행절대참조로 입력합니다. (예: A$10 형태))
  • $찾을머릿글범위 : 출력범위의 머릿글입니다.
VLOOKUP 여러개 값 중 '고유값'만 출력
VLOOKUP 여러 값 중 고유값만 출력 인수
고유값만 추출하는 공식에 사용된 인수입니다.
  • $찾을범위 : '찾을값'을 조회할 범위입니다.( VLOOKUP 범위 중 맨 좌측 열)
  • $찾을값 : '찾을범위'에서 찾을 값입니다. ( VLOOKUP 함수의 조건)
  • $출력범위 : 출력할 값이 나열된 범위입니다. ( VLOOKUP 범위 중 출력할 열)
  • $머릿글 : 값을 출력할 범위의 머릿글입니다. 범위형태로 입력하되 동적확장범위(혼합참조형태)로 입력하는 것에 주의합니다. (예: $A$1:A1)
VLOOKUP 두 날짜 사이를 만족하는 여러값 출력
두 날짜 사이의 여러값 불러오기 인수
두 날짜 사이 여러값을 구하는 공식에서 사용된 인수입니다.
  • $날짜범위 : 날짜가 입력된 범위입니다.
  • $출력범위 : 출력할 값이 나열된 범위입니다.
  • $시작일/종료일 : '날짜범위'에서 조회할 시작일과 종료일입니다.

사용 예제

VLOOKUP 함수 여러개 값 출력 사용예제 GIF
VLOOKUP 여러개 값 가져오기 공식의 사용예제입니다.

예제파일 다운로드

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

  • [엑셀공식] VLOOKUP 여러개 값 출력 공식 (세로/가로)
    예제파일
    다운로드
관련 기초함수

예제파일 살펴보기

예제파일에서 사용된 함수 공식

본 예제에서는 해당 지역에 거주하는 여러명의 직원을 출력하는 VLOOKUP 함수 여러개 값 가져오기 공식을 알아보겠습니다. 공식에 들어가는 인수는 아래와 같습니다.

{ =IFERROR(INDEX($C$3:$C$10,SMALL(IF(($F$3=$B$3:$B$10),MATCH(ROW($B$3:$B$10), ROW($B$3:$B$10)), ""), ROWS($A$1:A1))),"") }
VLOOKUP 여러개 값 가져오기 사용공식
예제파일에서는 위 공식이 사용되었습니다.
  1. $찾을범위 : 지역 (=$B$3:$B$10)
  2. $출력범위 : 이름 (=$C$3:$C$10)
  3. $찾을값 : 찾을지역 (=$F$3)
VLOOKUP 함수 여러개 값을 동시에 출력하는 공식의 동작원리

예제에서 사용된 공식을 바탕으로 VLOOKUP 여러개 값을 동시에 출력하는 공식의 동작원리를 단계별로 알아보겠습니다. 본 포스트에서는 값을 세로로 출력하는 공식의 동작원리만 적어드렸습니다.

가로로 출력하는 공식은 세로로 출력하는 공식과 동일한 원리로 동작합니다. 여러 필드의 값을 동적으로 가져오는 공식은 INDEX/MATCH 함수를 응용합니다. INDEX/MATCH 함수에 대한 자세한 설명은 관련포스트를 참고하세요.

INDEX/MATCH 함수 기초사용법 설명 보러가기
  1. 지역범위에서 선택한 지역과 동일한 값을 찾아 배열로 반환합니다.
    =IFERROR(INDEX($C$3:$C$10,SMALL(IF(($F$3=$B$3:$B$10),MATCH(ROW($B$3:$B$10), ROW($B$3:$B$10)), ""), ROWS($A$1:A1))),"")

    지역에서 선택한 지역(예: 서울)을 만족하는 값을 찾습니다.

    = $F$3 = $B$3:$B$10
    = "서울" = ("서울","경기","서울","경기","서울","인천",...)
    = { TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, ... }
  2. 전체 지역의 개수를 1부터 증가하는 순번의 배열로 반환합니다.
    =IFERROR(INDEX($C$3:$C$10,SMALL(IF(($F$3=$B$3:$B$10),MATCH(ROW($B$3:$B$10), ROW($B$3:$B$10)), ""), ROWS($A$1:A1))),"")

    ROW 함수는 각 셀의 행번호를 반환합니다. 따라서 MATCH 함수는 아래와 같이 입력됩니다.

    = MATCH ( ROW($B$3:$B$10), ROW($B$3:$B$10) )
    = MATCH ( {3,4,5,6,7,8,9,10}, {3,4,5,6,7,8,9,10} )
    = {1,2,3,4,5,6,7,8 }
  3. 지역 범위에서 선택한 지역과 일치하는 항목의 순번(=n)을 반환합니다.
    =IFERROR(INDEX($C$3:$C$10,SMALL(IF(($F$3=$B$3:$B$10),MATCH(ROW($B$3:$B$10), ROW($B$3:$B$10)), ""), ROWS($A$1:A1))),"")

    IF 함수로 TRUE 와 FALSE 일때의 값을 구분하여 결과값을 출력합니다.

    = IF(($F$3=$B$3:$B$10),MATCH(ROW($B$3:$B$10), ROW($B$3:$B$10)), "")
    = IF ( { TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, ... } , {1,2,3,4,5,6,7,8 }, "" )
    = { 1,"" ,3 ,"" ,5 ,"" , ... }
  4. 출력된 순번(=n) 중 x번째로 작은 값을 반환합니다.
    =IFERROR(INDEX($C$3:$C$10,SMALL({ 1,"" ,3 ,"" ,5 ,"" , ... }, ROWS($A$1:A1))),"")

    ROWS 함수는 입력한 범위의 행개수를 반환합니다. 동적확장범위를 사용하여 아래로 자동채우기를 할 때마다 1,2,3..의 값이 순차적으로 증가합니다.

    = ROWS($A$1:A1) '// -> 1
    = ROWS($A$1:A2) '// -> 2
    = ROWS($A$1:a3) '// -> 3... 이런 식으로 증가합니다.

    이후 SMALL 함수를 사용하여 순번(=n) 중 x 번째로 작은 값을 출력합니다.

    = SMALL({ 1,"" ,3 ,"" ,5 ,"" , ... }, ROWS($A$1:A1))  '// 가장 작은값을 출력할 경우
    = SMALL( { 1,"" ,3 ,"" ,5 ,"" , ... } , 1 )
    = 1

    또는 두번째로 작은 값을 출력할 경우는 아래와 같습니다.

    = SMALL({ 1,"" ,3 ,"" ,5 ,"" , ... }, ROWS($A$1:A2))  '// 두번째로 작은값을 출력할 경우
    = SMALL( { 1,"" ,3 ,"" ,5 ,"" , ... } , 2 )
    = 3
  5. 출력범위에서 각 n번째 위치하는 값을 반환합니다.
    =IFERROR(INDEX($C$3:$C$10, { 1, 3, 5 } ),"")

    INDEX 함수는 입력한 범위에서 n번째 위치하는 값을 반환합니다. 따라서 이름범위에서 1, 3, 5번째에 위치하는 값을 출력합니다.

    = INDEX($C$3:$C$10, { 1, 3, 5 } )
    = INDEX( { 박지훈, 김진아, 박시형, 유매력, 황석훈, 박나연 , ... } , { 1, 3, 5 } )
    = { 박지훈, 박시형, 황석훈 }
  6. IFERROR 함수로 오류처리를하여 공식을 마무리합니다.
    =IFERROR(INDEX($C$3:$C$10, #NUM ),"")

    출력할 개수보다 넓은 범위에 공식이 입력될 경우 SMALL 함수는 오류를 반환합니다. 즉, 입력된 개수보다 높은 값이 입력될경우 #NUM 오류를 반환하게됩니다. (예: 3개의 값중 4번째로 작은값을 출력하시오? -> #NUM!오류)
    따라서 IFERROR 함수로 오류일경우 빈칸을 출력하는 방식으로 공식을 마무리합니다.

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

댓글 239
5 (150개 평가)
최지오
최지오 2020.02.06 19:28
천재신가요?....
감탄만 나오네
오빠두엑셀
오빠두엑셀 작성자 2020.02.07 01:58
가..감사합니다!!❤️
이도경
이도경 2020.02.07 07:16
와 ㅜㅜ 출근해서 꼭해봐야겠어요 진짜 ㅠㅠ 오빠두님최고
유정천사
유정천사 2020.02.07 09:27
왕짱~너무 멋집니다~^^
Ireer
Ireer 2020.02.07 09:41
와 늘 유투브 강의만 보다가 너무 알고싶었던 내용이라 덧글 남기러 왔습니다! 정말 대단하세요 ㅠ 그런데 혹시 여러개의 결과를 각 결과 사이에 ", "를 삽입해 1개의 셀에 표기하는게 가능할까요? 가능하다면 어떤 함수들을 병행해서 사용하면 될까요?
오빠두엑셀
오빠두엑셀 작성자 2020.02.07 18:46
Ireer 님 안녕하세요!^^*
네 물론 가능합니다. 단, 오피스 365 또는 최신버전의 엑셀을 사용중이실경우에만 TEXTJOIN 함수를 응용할 수 있는데요.
아래 공식을 이용해보시겠어요?
=TEXTJOIN(", ",TRUE,IF($찾는값=$찾을범위,$출력범위,""))

만약 엑셀 2016 이전버전을 사용중이시라면 VBA로 사용자지정함수를 작성하셔야 합니다.
관련 내용은 다른 포스트로 작성해드리겠습니다
제 답변이 도움이 되셨길 바랍니다.^-^ 감사합니다.
차트윈
차트윈 2020.02.07 15:26
잘보구 갑니다. 그런데 왠지 영상보다는 눈에들어오질 않네요^^ 항상 고급정보 공유 감사드립니다. 올한해도 준비하고 계획하는일 잘 되시길 기도합니다. 수고하세요
오빠두엑셀
오빠두엑셀 작성자 2020.02.07 18:48
안녕하세요~^^* 좋은 의견 감사드립니다.
이후 짧은 영상강의로 간단한 공식 사용법 영상을 올려드리겠습니다.
차트윈님도 올 한해 건승하시고, 준비하신 일 잘 이루시길 기원하겠습니다^-^*
감사합니다.
박원조
박원조 2020.02.08 10:21
최곱니다
혹시 지금은 필드가 두 개(지역,이름)만 있는데 3~6(지역,이름,주소, 전화번호)개 확장되었을 때 리턴 값이 다 나오게 할수는 없는지요

예제는 서울 지역 선택시 해당지역에사는 사람만 나오는데 더 확장해서 그 사람의 주소와 이름까지 옆 필드에 나왔으면 좋겠습니다 부탁드려요
오빠두엑셀
오빠두엑셀 작성자 2020.02.08 17:18
안녕하세요~^^*
해당 공식을 응용하면 됩니다.
아래처럼 수식을 변경해보시겠어요?
=IFERROR(INDEX(전체범위,SMALL(IF(($G$3=$B$3:$B$10),MATCH(ROW($B$3:$B$10), ROW($B$3:$B$10)), ""), ROWS($A$1:A1)),COLUMNS($A$1:A1)),"")

마찬가지로 배열수식으로 입력 후 자동채우기 하시면 됩니다.^^
문의주신 내용 반영해서 이번주안으로 포스트내용 업데이트 해드릴테니 다시 들려주시겠어요?
제 답변이 도움이 되셨길 바랍니다.
좋은 의견 감사드립니다.
박원조
박원조 2020.02.09 10:42
감사합니다 제가 여기저기 홍보도 많이 하고 있습니다
늘 감탄의 연속입니다~~~더 많은 복 받은실겁니다
보이지 않는 누군가에게 선을 쌓은 집은 분명 선의 열매가 열린다고 했어요
하늬돌이
하늬돌이 2020.02.09 11:07
항상 필요한 기능인데 어려워서 잘 사용을 못하고 있었는데 자세하게 설명해 주셔서 이해하는데 도움이 많이 되었습니다. 감사해요
봉도리
봉도리 2020.02.09 15:40
항상 강의 잘 보고 있습니다
위의 식 적용했을때 출력값이 두개 이상 일경우 그중 하나만 나오게 할수는 없을까요??

예제파일에서 서울 박지훈이 두개 일경우 하나만 나오게요
오빠두엑셀
오빠두엑셀 작성자 2020.02.11 00:19
안녕하세요?^^ 해당 내용은 중복값제거 함수공식을 응용하시면 됩니다.
https://www.oppadu.com/엑셀-중복값-제거-함수-공식/
=LOOKUP(2, 1/((COUNTIF($머릿글:머릿글, $출력범위)=0)*($찾을값=$찾을범위)), $출력범위)

공식을 이용해보시겠어요?
오빠두엑셀
오빠두엑셀 작성자 2020.02.11 01:17
함수공식의 5번째 예제로 자세한 인수설명과 예제파일 같이 업데이트 해드렸으니 확인해보시겠어요?^^
소중한 의견 다시한번 감사드립니다.
제 답변이 도움이 되셨길 바랍니다.^^*
김지웅
김지웅 2020.02.19 19:48
이해 안갔던 부분마저 하나씩 설명이 차근차근 잘 되어있어서 결국 이해하는데 성공하게 만들어주셨네요.
이제 회사가서 적용하는 것 만 남았는데 노력해 보겠습니다.
레전드21
레전드21 2020.02.21 10:48
데이터가 계속 추가될경우 방법에 대해 매우 궁금합니다. 데이터값을 "표"로 지정해서 응용해보았는데 찾을값이 2개까지만 불러오네요~혹시 추가로 설명가능할까요? 감사합니다.
레전드21
레전드21 2020.02.21 20:15
정말 제 업무에 꼭 필요한 함수를 찾은것 같습니다^^
vba코딩으로 데이터 입력시 sheet2(DB)로 자동입력되는 방식인데 출력범위를 '표'로 지정하고 match함수포함된 함수로 응용하면 자동채우기 하지않더라도 추가입력된 자료가 자동으로 출력이 되는데, 문제는 찾을 머리글이 6개인데 "환자명"만 나옵니다(설명이 길어져 죄송합니다)
예를들어 머리글이 "환자명","등록번호","성별"등등등인데 환자명은 정상적으로 나오는데 등록번호가 나오지 않아 "등록번호"를 "환자명"으로 바꾸면 "환자명"은 출력이 됩니다.
(혹시, 제 질문과 글이 취지에 맞지않는다면 죄송합니다)
오빠두엑셀
오빠두엑셀 작성자 2020.02.22 06:44
안녕하세요?^^
해당공식은 찾을범위가 표 형식이여도 정상동작합니다.
3. VLOOKUP 여러값/여러필드를 동시에 반환하는 공식
4. VLOOKUP 여러값/여러필드를 동적으로 반환하는 공식

두 공식을 확인해보시겠어요?^^
여러 필드를 동시에 반환할 수 있습니다.
감사합니다.
레전드21
레전드21 2020.02.22 15:26
감사합니다^^우연히 유튜브 보고 들어왔다가 제게 꼭 필요한 함수를 배울수 있었습니다.
친절한 댓글도 감사하며, 요즘 완전 초보가 엑셀배우는 재미에 시간가는줄 모르고 삽니다^^