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

목차 바로가기

공식이 길어서 어려워보일 수 있으나, 전혀 어렵지 않습니다!
딱 3가지, "찾을값, 찾을범위, 출력범위" 만 공식에 대입해주면 끝! 같이 알아볼까요?

함수 공식

  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 두 날짜 사이 만족 여러값 출력

    두 날짜사이 조건을 만족하는 여러값을 출력합니다.

 해당 포스트에 적어드린 공식은 모두 배열수식이므로 반드시 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 여러개 값 가져오기 공식의 사용예제입니다.

예제파일 다운로드

공식설명파일구분마지막수정일파일형식파일다운로드
35497구분열 없이 SUMIF 함수 월별 합계를 구하는 공식예제파일2020-02-11XLSX엑셀 함수 활용예제 파일 다운로드
35309한셀에 입력된 여러줄의 텍스트를 나눠서 출력합니다.예제파일2020-02-08XLSX엑셀 함수 활용예제 파일 다운로드
35303두줄로 입력된 텍스트를 나눠서 출력합니다.예제파일2020-02-08XLSX엑셀 함수 활용예제 파일 다운로드
35243엑셀 주민번호 뒷자리를 다양한 방법으로 숨기는 실전 공식예제파일2020-02-08XLSX엑셀 함수 활용예제 파일 다운로드
35213VLOOKUP 함수로 여러개의 값을 출력하는 공식을 알아봅니다.예제파일2020-02-06XLSX엑셀 함수 활용예제 파일 다운로드
34600필터/정렬 기능을 사용하지 않고 항목별 순번을 셉니다.예제파일2020-01-18XLSX엑셀 함수 활용예제 파일 다운로드
34413조건을 만족하는 값만 불러오는 드롭다운 목록상자를 만듭니다.예제파일2020-01-10XLSX엑셀 함수 활용예제 파일 다운로드
33767INDEX/MATCH 함수를 응용하여 그림을 불러오는 공식입니다.예제파일2019-12-19XLSX엑셀 함수 활용예제 파일 다운로드
33329INDEX/MATCH 함수 기초 사용법예제파일2019-12-18XLSX엑셀 함수 활용예제 파일 다운로드
33272기존범위의 행열을 바꿔 출력합니다.예제파일2019-12-07XLSX엑셀 함수 활용예제 파일 다운로드
32910VLOOKUP 함수 마지막값 찾기예제파일2019-11-22XLSX엑셀 함수 활용예제 파일 다운로드
31201INDEX MATCH 함수 고급 사용법예제파일2019-12-10XLSX엑셀 함수 활용예제 파일 다운로드
31001중복값 제거, 고유값 반환 공식예제파일2019-11-22XLSX엑셀 함수 활용예제 파일 다운로드
30721엑셀 데이터 정규화 공식예제파일2019-11-22XLSX엑셀 함수 활용예제 파일 다운로드
30323조건별 보이는 셀 개수 구하기예제파일2019-11-22XLSX엑셀 함수 활용예제 파일 다운로드
관련 기초함수
선택한 범위에서 원하는 위치의 값을 반환합니다.
선택한 범위에서 찾는값의 위치 순번을 반환합니다.

예제파일 살펴보기

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

본 예제에서는 해당 지역에 거주하는 여러명의 직원을 출력하는 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 함수에 대한 자세한 설명은 관련포스트를 참고하세요.

  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 함수 사용법 상세설명

별점주기 (5 / 10)

16
댓글 남기기

avatar
9 Comment Thereads
7 Thread replies
7 팔로워
 
가장 좋아요가 많은 댓글
가장 인기많은 댓글
10 작성자
김지웅봉도리하늬돌이박원조차트윈 최근 작성자
  현재 페이지 댓글알림 신청  
알림 설정
최지오
손님
최지오
별점주기 :
     

천재신가요?….
감탄만 나오네

이도경
손님
이도경
별점주기 :
     

와 ㅜㅜ 출근해서 꼭해봐야겠어요 진짜 ㅠㅠ 오빠두님최고

유정천사
Member
유정천사
별점주기 :
     

왕짱~너무 멋집니다~^^

Ireer
손님
Ireer
별점주기 :
     

와 늘 유투브 강의만 보다가 너무 알고싶었던 내용이라 덧글 남기러 왔습니다! 정말 대단하세요 ㅠ 그런데 혹시 여러개의 결과를 각 결과 사이에 ", "를 삽입해 1개의 셀에 표기하는게 가능할까요? 가능하다면 어떤… 더보기 »

차트윈
손님
차트윈
별점주기 :
     

잘보구 갑니다. 그런데 왠지 영상보다는 눈에들어오질 않네요^^ 항상 고급정보 공유 감사드립니다. 올한해도 준비하고 계획하는일 잘 되시길 기도합니다. 수고하세요

박원조
손님
박원조
별점주기 :
     

최곱니다 혹시 지금은 필드가 두 개(지역,이름)만 있는데 3~6(지역,이름,주소, 전화번호)개 확장되었을 때 리턴 값이 다 나오게 할수는 없는지요 예제는 서울 지역 선택시 해당지역에사는 사람만 나오는데 더 확장해서 그 사람의 주소와 이름까지… 더보기 »

하늬돌이
Member
하늬돌이
별점주기 :
     

항상 필요한 기능인데 어려워서 잘 사용을 못하고 있었는데 자세하게 설명해 주셔서 이해하는데 도움이 많이 되었습니다. 감사해요

봉도리
손님
봉도리
별점주기 :
     

항상 강의 잘 보고 있습니다
위의 식 적용했을때 출력값이 두개 이상 일경우 그중 하나만 나오게 할수는 없을까요??

예제파일에서 서울 박지훈이 두개 일경우 하나만 나오게요

김지웅
손님
김지웅
별점주기 :
     

이해 안갔던 부분마저 하나씩 설명이 차근차근 잘 되어있어서 결국 이해하는데 성공하게 만들어주셨네요.
이제 회사가서 적용하는 것 만 남았는데 노력해 보겠습니다.