VLOOKUP 함수 여러개 값 출력 공식 총정리 (세로/가로 방향 모두!)
목차 바로가기
- 예제파일 다운로드
- VLOOKUP 여러개 값을 세로로 반환하는 공식
- VLOOKUP 여러개 값을 가로로 반환하는 공식
- VLOOKUP 여러값/여러필드를 동시에 반환하는 공식
- VLOOKUP 여러값/여러필드를 동적으로 반환하는 공식
- VLOOKUP 여러값 중 '고유값'만 출력하는 공식 (간단수식 or 배열수식)
- VLOOKUP 두 날짜 사이를 만족하는 여러값 불러오기 공식
- VLOOKUP 다중 조건을 만족하는 여러개 값 반환하는 공식
- 각 공식에 사용된 인수 설명
- VLOOKUP 여러개 값 출력 공식의 동작 원리
- 링크 : VLOOKUP 다중조건 검색하기 공식
현재 오피스 365 최신버전을 사용중이시라면, FILTER 함수를 사용해보세요!
더욱 편리하게 VLOOKUP 함수 여러 값 불러오기를 구현할 수 있습니다.
영상 강의 업데이트!
공식의 동작원리 및 실전 사용예제를 영상강의로 준비해드렸습니다! 🙂
새로운 공식 업데이트!
여러 조건을 동시에 검색해야 할 경우, 각각의 조건을 만족하는 모든 결과를 동시에 출력하는 방법에 대해 많은 분께서 질문해주셨습니다. 여러 조건을 동시에 만족하는 여러개 값을 한 번에 출력하는 공식은 아래 공식을 참고해주세요.
함수 공식
- VLOOKUP 여러개 값을 세로로 반환하는 공식
= INDEX($출력범위, SMALL(IF(($찾을값=$찾을범위),MATCH(ROW($찾을범위), ROW($찾을범위)), ""), ROWS($A$1:A1)))
VLOOKUP 여러개 값을 세로로 반환하는 공식입니다. - VLOOKUP 여러개 값을 가로로 반환하는 공식
= INDEX($출력범위,SMALL(IF($찾을값=$찾을범위,ROW($찾을범위)-MIN(ROW($찾을범위))+1, ""),COLUMN(A1)))
VLOOKUP 여러개 값을 가로 방향으로 반환하는 공식입니다. - VLOOKUP 여러 필드의 값을 동시에 반환하는 공식 [COLUMNS 함수 이용]
COLUMNS 함수를 이용하여 첫번째, 두번째, 세번째.. 열을 순서대로 반환합니다.= INDEX($출력범위, SMALL(IF(($찾을값=$찾을범위),MATCH(ROW($찾을범위), ROW($찾을범위)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))
여러필드의 값을 동시에 출력할 수도 있습니다 - VLOOKUP 여러개 필드값을 동적으로 반환하는 공식 [MATCH 함수 이용]
MATCH 함수를 이용합니다. 필드 머릿글을 바꾸면 출력범위가 동적으로 변하는 공식입니다. %머릿글은 행고정참조(예: A$10 형태)로 입력하는것에 주의합니다.= INDEX($출력범위, SMALL(IF(($찾을값=$찾을범위),MATCH(ROW($찾을범위), ROW($찾을범위)), ""), ROWS($A$1:A1)), MATCH($머릿글, $찾을머릿글범위, 0))
- VLOOKUP 여러개 값 중 '고유값'만 출력하는 공식 (간단수식 or 배열수식)
중복값 제거 공식을 응용하여 VLOOKUP 고유값 출력 공식을 작성할 수 있습니다. 중복값 제거 공식에 대한 자세한 설명은 관련 포스트를 참고하세요.아래 수식은 간단수식이으로 ENTER로만 입력합니다. 고유값을 '역방향'으로 출력합니다.
=LOOKUP(2, 1/((COUNTIF($머릿글:머릿글, $출력범위)=0)*($찾을값=$찾을범위)), $출력범위)만약 고유값을 정방향으로 출력해야 할 경우, 아래 배열수식을 사용합니다.
= INDEX($출력범위,MATCH(1,(COUNTIF($머릿글:머릿글,$출력범위)=0)*($찾을값=$찾을범위),0))
여러값 중 고유값만 반환할 수도 있습니다. - VLOOKUP 두 날짜 사이를 만족하는 여러값 불러오기 공식
= INDEX($출력범위, SMALL(IF(($시작일<=$날짜범위)*($종료일>=$날짜범위),MATCH(ROW($찾을범위), ROW($찾을범위)), ""), ROWS($A$1:A1)))
두 날짜사이 조건을 만족하는 여러값을 출력합니다. - VLOOKUP 여러 조건을 만족하는 여러개 값 반환하는 공식
= INDEX($출력범위, SMALL(IF(($찾을값1=$찾을범위1)*($찾을값2=$찾을범위2),MATCH(ROW($찾을범위), ROW($찾을범위)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))
여러 조건을 만족하는 VLOOKUP 여러개 값 반환 공식
해당 포스트에 적어드린 공식은 모두 배열수식이므로 반드시 CTRL + SHIFT + ENTER 로 입력하는 것을 주의하세요!
인수 설명
VLOOKUP 여러개 값 출력 : 세로/가로, 1개 필드만 출력하는 경우
- $찾을범위 : 찾을값을 조회할 범위입니다. ( VLOOKUP 범위의 맨 좌측열)
- $출력범위 : 출력할 값이 나열된 범위입니다. ( VLOOKUP 범위 중 출력할 열)
- $찾을값 : 찾을범위에서 찾을 값입니다. ( VLOOKUP 함수의 첫번째 인수)
VLOOKUP 여러개 값 출력 : 여러 필드를 동시에 출력하는 경우
- $찾을범위 : 찾을값을 조회할 범위입니다.
- $출력범위 : 출력할 값이 나열된 범위입니다.
- $찾을값 : 찾을 범위에서 찾을 값입니다.
- $머릿글 : 출력할 범위의 머릿글입니다. (행절대참조로 입력합니다. (예: A$10 형태))
- $찾을머릿글범위 : 출력범위의 머릿글입니다.
VLOOKUP 여러개 값 중 '고유값'만 출력
- $찾을범위 : '찾을값'을 조회할 범위입니다.( VLOOKUP 범위 중 맨 좌측 열)
- $찾을값 : '찾을범위'에서 찾을 값입니다. ( VLOOKUP 함수의 조건)
- $출력범위 : 출력할 값이 나열된 범위입니다. ( VLOOKUP 범위 중 출력할 열)
- $머릿글 : 값을 출력할 범위의 머릿글입니다. 범위형태로 입력하되 동적확장범위(혼합참조형태)로 입력하는 것에 주의합니다. (예: $A$1:A1)
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))),"") }
예제파일에서는 위 공식이 사용되었습니다. - $찾을범위 : 지역 (=$B$3:$B$10)
- $출력범위 : 이름 (=$C$3:$C$10)
- $찾을값 : 찾을지역 (=$F$3)
VLOOKUP 함수 여러개 값을 동시에 출력하는 공식의 동작원리
예제에서 사용된 공식을 바탕으로 VLOOKUP 여러개 값을 동시에 출력하는 공식의 동작원리를 단계별로 알아보겠습니다. 본 포스트에서는 값을 세로로 출력하는 공식의 동작원리만 적어드렸습니다.
가로로 출력하는 공식은 세로로 출력하는 공식과 동일한 원리로 동작합니다. 여러 필드의 값을 동적으로 가져오는 공식은 INDEX/MATCH 함수를 응용합니다. INDEX/MATCH 함수에 대한 자세한 설명은 관련포스트를 참고하세요.
- 지역범위에서 선택한 지역과 동일한 값을 찾아 배열로 반환합니다.
=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, ... } - 전체 지역의 개수를 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 } - 지역 범위에서 선택한 지역과 일치하는 항목의 순번(=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 ,"" , ... } - 출력된 순번(=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 - 출력범위에서 각 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 } )
= { 박지훈, 박시형, 황석훈 } - IFERROR 함수로 오류처리를하여 공식을 마무리합니다.
=IFERROR(INDEX($C$3:$C$10, #NUM ),"")
출력할 개수보다 넓은 범위에 공식이 입력될 경우 SMALL 함수는 오류를 반환합니다. 즉, 입력된 개수보다 높은 값이 입력될경우 #NUM 오류를 반환하게됩니다. (예: 3개의 값중 4번째로 작은값을 출력하시오? -> #NUM!오류)
따라서 IFERROR 함수로 오류일경우 빈칸을 출력하는 방식으로 공식을 마무리합니다.
