엑셀 VLOOKUP 함수 여러개 출력 공식 동작원리 - 단계별 정리

직장인이 꼭 알아야 할 필수 공식! VLOOKUP 함수 여러개 출력 공식의 사용법 및 동작원리를 단계별로 살펴봅니다.

홈페이지 » 엑셀 VLOOKUP 함수 여러개 출력 공식 - 단계별 정리

엑셀 VLOOKUP 함수 여러개 출력 공식 동작원리 - 단계별 정리

엑셀 VLOOKUP 여러개 출력 공식 목차 바로가기
영상강의

큰화면보기

예제파일 다운로드

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

  • [엑셀고급] VLOOKUP 함수 여러개 값 출력 공식
    예제파일
  • [엑셀고급] VLOOKUP 함수 여러개 값 출력 공식
    완성파일

VLOOKUP 여러개 값 불러오기 공식 사용법

이번 강의에서는 VLOOKUP 여러개 값 불러오기 공식의 기초 및 응용공식과 동작원리를 단계별로 살펴봅니다.

이번 포스트에서 소개해드린 모든 공식은 배열수식이므로
365 이전 버전 사용자는 반드시 CTRL + SHIFT + ENTER 로 입력해야 합니다.

365 버전 사용자는 이번 강의에서 소개해드린 공식 대신 FILTER 함수를 사용하면 더욱 빠르고 편리하게 작업할 수 있습니다. FITLER 함수에 대한 자세한 설명은 아래 FILTER 함수 실전예제 총정리 영상강의를 참고하세요.

VLOOKUP 여러개 값 불러오기 기본 공식 (조건이 1개인 경우)

=IFERROR(INDEX($출력범위, SMALL(IF(($찾을값=$찾을범위),MATCH(ROW($찾을범위), ROW($찾을범위)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1)),"")
VLOOKUP 함수 여러개 값 출력 인수 설명
엑셀 VLOOKUP 여러개 값 출력 기본 공식

VLOOKUP 여러개 값 불러오기 응용 공식 #1 (각 조건으로 여러 범위를 비교하는 경우)

=IFERROR(INDEX($출력범위, SMALL(IF(($찾을값1=$찾을범위1)*($찾을값2=$찾을범위2),MATCH(ROW($찾을범위), ROW($찾을범위)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1)),"")
엑셀 vlookup 여러개 다중 조건
엑셀 VLOOKUP 여러개 출력 - 여러 범위를 조건으로 비교하는 경우

VLOOKUP 여러개 값 불러오기 응용 공식 #2 (한 범위에서 여러 조건을 찾는 경우)

=IFERROR(INDEX($출력범위,SMALL(IF(ISNUMBER(MATCH($조건범위,$조건,0)),MATCH(ROW($출력범위), ROW($출력범위)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1)),"")
엑셀 VLOOKUP 여러 조건 여러 값 출력 공식 설명
엑셀 VLOOKUP 여러개 출력 - 한 범위에서 여러 조건을 찾는 경우

VLOOKUP 여러개 값 불러오기 응용 공식 #3 (여러 범위에서 여러 조건을 찾는 경우)

=IFERROR(INDEX($출력범위,SMALL(IF(ISNUMBER(MATCH($조건범위1,$조건1,0)*MATCH($조건범위2,$조건2,0)),MATCH(ROW($출력범위), ROW($출력범위)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1)),"")
엑셀 VLOOKUP 다중조건 다중범위 여러개 결과
엑셀 VLOOKUP 여러개 출력 - 여러 범위에서 여러 조건을 찾는 경우

VLOOKUP 함수 사용시 제한사항

VLOOKUP 함수는 SUMIF 함수와 함께 실무자가 가장 많이 사용하는 엑셀 10대 필수 함수 중 하나입니다. 가장 자주 사용되는 함수이지만, 실무에서 발생하는 다양한 상황에 적용할 때에는 몇가지 제한사항이 있어서 다른 함수나 공식으로 대체해서 사용해야 합니다.

  1. VLOOKUP 함수의 참조범위는 범위의 첫번째 열에 있어야 합니다.
    : 즉, 참조범위의 오른쪽 방향으로만 조회할 수 있는데요. 참조범위의 왼쪽방향으로 조회하려면 INDEX/MATCH공식 또는 XLOOKUP 함수를 사용합니다.

  2. VLOOKUP 함수는 위에서 첫번째 결과만 출력합니다.
    : 만약 마지막 결과를 출력하거나, n번째 결과를 출력하려면 아래 함수나 공식을 사용합니다.

  3. VLOOKUP 함수는 하나의 결과만 출력할 수 있습니다.
    : 만약 여러개의 결과를 반환하려면 FILTER 함수나 이번 강의에서 소개해드린 공식을 사용합니다.

MATCH+ROW 함수로 순번 만들기

VLOOKUP 여러개 값 불러오기 공식의 첫번째 계산은 MATCH+ROW 공식에서 시작됩니다. MATCH+ROW 공식을 사용하면 1부터 증가하는 연속되는 순번을 배열로 반환할 수 있습니다.

  1. MATCH+ROW 공식으로 순번 만들기 : 예제파일의 L8:L23 범위를 선택한 뒤, 아래 수식을 입력합니다. 이후 CTRL + SHIFT + ENTER로 수식을 입력하면 1부터 증가하는 연속된 순번이 반환됩니다.
    =MATCH(ROW($B$6:$B$21),ROW($B$6:$B$21))
    엑셀 VLOOKUP 여러 결과 순번
    MATCH + ROW 함수로 연속되는 순번을 출력합니다.
    오빠두Tip : L8:L23 보다 넓은 범위를 선택할 경우, 기존 범위의 개수인 16개를 넘어가는 셀에는 #N/A 오류가 반환됩니다. (이는 정상결과이므로 다음 단계로 넘어가도 괜찮습니다.)
  2. 만약 365 버전 사용자일 경우, L8셀에 수식을 입력한 뒤, ENTER 키로 수식을 입력하면 범위 위로 배열이 반환됩니다. (파란색 테두리 확인)

    엑셀 365 VLOOKUP 여러 결과 공식1
    365 버전에서는 배열이 범위 위로 반환됩니다.

IF 함수로 조건을 만족할 경우 순번 출력하기

이제 IF 함수를 사용해서, 특정 조건을 만족하는 경우에만 MATCH+ROW 함수로 계산된 순번이 반환되도록 공식을 입력합니다.

  1. IF 함수로 조건을 만족할 경우 순번 반환 : 예제파일의 N8:N23 범위를 선택한 뒤, 아래 수식을 입력합니다. 이후 CTRL + SHIFT + ENTER 로 수식을 입력하면 조건을 만족하는 경우의 순번만 범위로 반환됩니다.
    =IF(($F$8=$B$6:$B$21),MATCH(ROW($B$6:$B$21),ROW($B$6:$B$21)),"")

    엑셀 VLOOKUP 여러 결과 조건 만족
    IF 함수로 조건을 만족할 경우, 순번을 출력합니다.
  2. 만약 365 버전 사용자일 경우, N8셀에 수식을 입력한 뒤 ENTER 키로 수식을  입력하면 범위 위로 배열이 반환됩니다. (파란색 테두리 확인)

    엑셀 365 여러결과 조건
    365 버전에서는 배열이 범위 위로 반환됩니다.

ROWS+확장범위로 증가하는 순번 만들기

ROWS 함수와 확장범위를 활용하면 아래로 자동채우기를 할 때마다 1씩 증가하는 연속된 순번을 만들 수 있습니다. ROWS+확장범위 공식은 다양한 자동화 배열수식에 활용할 수 있으므로 엑셀 고급단계로 넘어가기 위한 실무자라면 반드시 숙지하시길 권장드립니다.

  1. ROWS+확장범위로 순번 만들기 : 예제파일의 P8셀을 선택한 뒤, 아래 수식을 입력합니다. 이후 수식을 아래로 자동채우기 하면 1부터 증가하는 연속된 순번이 만들어집니다.
    =ROWS($A$1:A1)
    엑셀 순번 자동채우기 함수
    ROWS+확장범위는 자동채우기를 할 때 연속된 순번을 반환하는 공식입니다.
    오빠두Tip : 공식에 사용된 $A$1:A1 은 $B$10:B10 으로 입력해도 무방합니다. 또한 A1셀 선택 후, 콜론(:)을 입력하면 셀주소를 범위로 손쉽게 변경할 수 있습니다.
  2. 엑셀의 참조방식과 절대참조, 혼합참조에 대한 자세한 설명은 아래 기초입문강의를 참고하세요.

SMALL 함수로 값을 차례대로 불러오기

이제 SMALL 함수를 활용하여, 우리가 방금 전 IF 함수로 반환한 순번 범위의 값을 작은 값 부터 하나씩 차례대로 출력합니다.

  1. SMALL 함수로 1~nth 값 출력 : 예제파일의 R8셀을 선택한 뒤, 아래 수식을 CTRL + SHIFT + ENTER 키로 입력합니다. 이후 수식을 아래로 자동채우기 하면 IF 함수로 반환된 순번의 가장 작은값 부터 차례대로 하나씩 출력됩니다.
    =SMALL(IF(($F$8=$B$6:$B$21),MATCH(ROW($B$6:$B$21),ROW($B$6:$B$21)),""),ROWS($A$1:A1))
    엑셀 VLOOKUP SMALL 함수 순서대로 출력
    SMALL 함수로 순번범위의 가장 작은값 부터 차례대로 출력합니다.
    오빠두Tip : 365 버전 사용자는 수식을 ENTER 로 입력해도 괜찮습니다.

INDEX/MATCH 공식 완성하기

이제 INDEX 함수를 사용하여 VLOOKUP 여러개 값 출력하기 공식을 완성합니다.

  1. VLOOKUP 여러개 값 출력 공식 완성 : 예제파일 T8셀에 아래 수식을 CTRL + SHIFT + ENTER 로 입력합니다. 365 사용자는 ENTER 로 입력해도 괜찮습니다.
    =INDEX($C$6:$C$21,SMALL(IF(($F$8=$B$6:$B$21),MATCH(ROW($B$6:$B$21),ROW($B$6:$B$21)),""),ROWS($A$1:A1)))
    엑셀 VLOOKUP 여러개 출력 공식 완성
    INDEX 함수로 VLOOKUP 여러개 값 출력 공식을 완성합니다.
    오빠두Tip : 이번 포스트 첫번째 영역에서 소개해드린 공식을 사용하면 아래방향 뿐만 아니라 오른쪽 방향으로도 여러개 값을 동시에 불러올 수 있습니다.
  2. IFERROR 함수로 오류 처리하기 : IFERROR 함수로 #NUM! 오류 대신 빈칸이 반환해주면 VLOOKUP 여러개 값 출력하기 공식이 완성됩니다.
    =IFERROR(INDEX($C$6:$C$21,SMALL(IF(($F$8=$B$6:$B$21),MATCH(ROW($B$6:$B$21),ROW($B$6:$B$21)),""),ROWS($A$1:A1))),"")

    VLOOKUP 여러 결과 출력 공식 최종
    IFERROR 함수로 오류처리를 하면 공식이 마무리됩니다.
5 6 투표
게시글평점
guest
11 댓글
Inline Feedbacks
모든 댓글 보기
한동진 바다
한동진 바다
2021년 6월 9일 9:52 오후
게시글평점 :
     

실무에 많은 도움을 받습니다. 감사합니다.

해다시
해다시
2021년 6월 13일 3:37 오전
게시글평점 :
     

늘 고맙게 보고 있습니다.
source data table 및 결과table을 동적으로 만들려면 어떻게 해야 할까요?

해다시
해다시
2021년 6월 17일 7:51 오전
답글 남기기  오빠두엑셀

감사합니다. 열공 후 질문 있으면 또 드릴께요.
감사합니다.

won****
won****
2021년 6월 13일 7:34 오전
게시글평점 :
     

항상 감사합니다. 문의드릴게 있는데요.
조건이 여러개일때 두번째 공식(isnumber 포함된공식)으로 하라고 잘 설명해주셨는데요.
조건이 2개 이상일때 예를들어 ‘서울시’ + ‘이름’ 하고 싶을때는 공식을 어떻게 해야하는지 궁금합니다.
match함수로 조건범위와 조건을 추가해도 안되네요….ㅠㅠ

그리고
그리고
2021년 6월 16일 6:21 오전
게시글평점 :
     

공부해야겠어요

소천
소천
2021년 6월 17일 11:08 오후
게시글평점 :
     

덕분에 또 한가지 업데이트 됩니다.
감사합니다.

김민구
김민구
2021년 6월 20일 4:53 오전
게시글평점 :
     

=IFERROR(INDEX($H$2:$H$1048576,SMALL(IF(($AD$2=$AB$2:$AB$1048576),MATCH(ROW($AB$2:$AB$1048576),ROW($AB$2:$AB$1048576)),""),ROWS($A$1:A2))),"")

잘못된 수식이 있을까요? 계속 빈칸만 나와서요 ㅠㅠ 배열수식으로 붙여넣습니다.

yamp****
yamp****
2021년 6월 21일 10:29 오후
게시글평점 :
     

좋은 강의 감사합니다

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