VLOOKUP 함수 여러개 값 출력 공식
지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀
조건에 맞는 여러 결과를 세로 또는 가로로 출력합니다.
인수 설명
01결과를 세로로 불러오기
=INDEX ( 출력범위, SMALL ( IF ( 찾을값 = 찾을범위, MATCH ( ROW(찾을범위), ROW(찾을범위) ), "" ), ROWS($A$1:A1) ) )인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
출력범위필수출력할 값이 나열된 범위입니다.
찾을값필수찾을범위에서 찾을 값입니다.
찾을범위필수찾을값을 조회할 범위입니다.
A
B
C
D
E
F
G
1
지역
이름
찾을지역
결과값 [세로]
2
서울
박지훈
서울
박지훈
3
경기
김진아
박시형
4
서울
박시형
황석훈
5
경기
유매력
6
서울
황석훈
7
인천
박나연
8
인천
하태정
9
부산
박남기
10
02결과를 가로로 불러오기
=INDEX ( 출력범위, SMALL ( IF ( 찾을값 = 찾을범위, MATCH ( ROW(찾을범위), ROW(찾을범위) ), "" ), COLUMNS($A$1:A1) ) )인수 설명 자세히 보기
인수구분설명
출력범위필수출력할 값이 나열된 범위입니다.
찾을값필수찾을범위에서 찾을 값입니다.
찾을범위필수찾을값을 조회할 범위입니다.
A
B
C
D
E
F
G
H
I
J
K
1
지역
이름
찾을지역
결과값 [가로]
2
서울
박지훈
서울
박지훈
박시형
황석훈
3
경기
김진아
4
서울
박시형
5
경기
유매력
6
서울
황석훈
7
인천
박나연
8
인천
하태정
9
부산
박남기
10
동작 원리
조건을 만족하는 행의 순번만 남긴 배열을 만든 다음, 그 순번을 하나씩 꺼내 INDEX 함수로 값을 가져옵니다.
예제 시트에서 지역이 '서울'인 이름을 세로로 출력하는 F2 셀의 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
1
조건을 만족하는 자리를 참·거짓으로 표시합니다
찾을값 하나를 찾을범위 전체와 비교하면 셀마다 참·거짓이 담긴 배열이 나옵니다.
= "서울" = { "서울", "경기", "서울", "경기", "서울", "인천", "인천", "부산" }
= { TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE, FALSE }
지역이 서울인 자리만 TRUE 가 됩니다. 2
행마다 1부터 세는 순번을 만듭니다
ROW 함수는 실제 행 번호를 돌려주므로 2부터 시작합니다. MATCH 함수로 그 행 번호가 몇 번째인지 다시 세어 1부터 시작하는 순번으로 바꿉니다.
= MATCH ( ROW(A2:A9), ROW(A2:A9) )
= MATCH ( { 2,3,4,5,6,7,8,9 }, { 2,3,4,5,6,7,8,9 } )
= { 1,2,3,4,5,6,7,8 }
행 번호가 아니라 1부터 시작하는 순번이 됩니다. 3
조건을 만족하는 순번만 남깁니다
IF 함수가 참인 자리에는 순번을 넣고, 거짓인 자리에는 빈 문자열을 넣습니다.
= IF ( { TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE, FALSE }, { 1,2,3,4,5,6,7,8 }, "" )
= { 1, "", 3, "", 5, "", "", "" }
서울인 1·3·5 번째 순번만 남습니다. 4
남은 순번을 작은 것부터 하나씩 꺼냅니다
ROWS($A$1:A1) 은 아래로 채울 때마다 1, 2, 3 으로 커집니다. SMALL 함수가 그 순서대로 작은 값을 꺼내므로 첫 칸은 1, 둘째 칸은 3 이 됩니다.
= SMALL ( { 1, "", 3, "", 5, "", "", "" }, 1 )
= 1
첫 번째 칸에 들어갈 순번입니다. = SMALL ( { 1, "", 3, "", 5, "", "", "" }, 2 )
= 3
두 번째 칸에 들어갈 순번입니다. 5
그 순번의 값을 가져옵니다
INDEX 함수가 출력범위에서 n 번째 값을 꺼냅니다.
출력할 개수보다 넓은 범위에 채우면 SMALL 함수가 #NUM! 오류를 냅니다. 공식 전체를 IFERROR 함수로 감싸면 남는 칸이 빈칸으로 정리됩니다.
= INDEX ( { 박지훈, 김진아, 박시형, 유매력, 황석훈, 박나연, 하태정, 박남기 }, { 1, 3, 5 } )
= { 박지훈, 박시형, 황석훈 }
지역이 서울인 세 사람의 이름입니다. 
감탄만 나오네
네 물론 가능합니다. 단, 오피스 365 또는 최신버전의 엑셀을 사용중이실경우에만 TEXTJOIN 함수를 응용할 수 있는데요.
아래 공식을 이용해보시겠어요?
만약 엑셀 2016 이전버전을 사용중이시라면 VBA로 사용자지정함수를 작성하셔야 합니다.
관련 내용은 다른 포스트로 작성해드리겠습니다
제 답변이 도움이 되셨길 바랍니다.^-^ 감사합니다.
이후 짧은 영상강의로 간단한 공식 사용법 영상을 올려드리겠습니다.
차트윈님도 올 한해 건승하시고, 준비하신 일 잘 이루시길 기원하겠습니다^-^*
감사합니다.
혹시 지금은 필드가 두 개(지역,이름)만 있는데 3~6(지역,이름,주소, 전화번호)개 확장되었을 때 리턴 값이 다 나오게 할수는 없는지요
예제는 서울 지역 선택시 해당지역에사는 사람만 나오는데 더 확장해서 그 사람의 주소와 이름까지 옆 필드에 나왔으면 좋겠습니다 부탁드려요
해당 공식을 응용하면 됩니다.
아래처럼 수식을 변경해보시겠어요?
마찬가지로 배열수식으로 입력 후 자동채우기 하시면 됩니다.^^
문의주신 내용 반영해서 이번주안으로 포스트내용 업데이트 해드릴테니 다시 들려주시겠어요?
제 답변이 도움이 되셨길 바랍니다.
좋은 의견 감사드립니다.
늘 감탄의 연속입니다~~~더 많은 복 받은실겁니다
보이지 않는 누군가에게 선을 쌓은 집은 분명 선의 열매가 열린다고 했어요
위의 식 적용했을때 출력값이 두개 이상 일경우 그중 하나만 나오게 할수는 없을까요??
예제파일에서 서울 박지훈이 두개 일경우 하나만 나오게요
https://www.oppadu.com/엑셀-중복값-제거-함수-공식/
공식을 이용해보시겠어요?
소중한 의견 다시한번 감사드립니다.
제 답변이 도움이 되셨길 바랍니다.^^*
이제 회사가서 적용하는 것 만 남았는데 노력해 보겠습니다.
vba코딩으로 데이터 입력시 sheet2(DB)로 자동입력되는 방식인데 출력범위를 '표'로 지정하고 match함수포함된 함수로 응용하면 자동채우기 하지않더라도 추가입력된 자료가 자동으로 출력이 되는데, 문제는 찾을 머리글이 6개인데 "환자명"만 나옵니다(설명이 길어져 죄송합니다)
예를들어 머리글이 "환자명","등록번호","성별"등등등인데 환자명은 정상적으로 나오는데 등록번호가 나오지 않아 "등록번호"를 "환자명"으로 바꾸면 "환자명"은 출력이 됩니다.
(혹시, 제 질문과 글이 취지에 맞지않는다면 죄송합니다)
해당공식은 찾을범위가 표 형식이여도 정상동작합니다.
4. VLOOKUP 여러값/여러필드를 동적으로 반환하는 공식
두 공식을 확인해보시겠어요?^^
여러 필드를 동시에 반환할 수 있습니다.
감사합니다.
친절한 댓글도 감사하며, 요즘 완전 초보가 엑셀배우는 재미에 시간가는줄 모르고 삽니다^^