엑셀 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 여러개 값 불러오기 응용 공식 #1 (각 조건으로 여러 범위를 비교하는 경우)
=IFERROR(INDEX($출력범위, SMALL(IF(($찾을값1=$찾을범위1)*($찾을값2=$찾을범위2),MATCH(ROW($찾을범위), ROW($찾을범위)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1)),"")
엑셀 VLOOKUP 여러개 출력 - 여러 범위를 조건으로 비교하는 경우 VLOOKUP 여러개 값 불러오기 응용 공식 #2 (한 범위에서 여러 조건을 찾는 경우)
=IFERROR(INDEX($출력범위,SMALL(IF(ISNUMBER(MATCH($조건범위,$조건,0)),MATCH(ROW($출력범위), ROW($출력범위)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1)),"")
엑셀 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 함수는 SUMIF 함수와 함께 실무자가 가장 많이 사용하는 엑셀 10대 필수 함수 중 하나입니다. 가장 자주 사용되는 함수이지만, 실무에서 발생하는 다양한 상황에 적용할 때에는 몇가지 제한사항이 있어서 다른 함수나 공식으로 대체해서 사용해야 합니다.
- VLOOKUP 함수의 참조범위는 범위의 첫번째 열에 있어야 합니다.
: 즉, 참조범위의 오른쪽 방향으로만 조회할 수 있는데요. 참조범위의 왼쪽방향으로 조회하려면 INDEX/MATCH공식 또는 XLOOKUP 함수를 사용합니다. - VLOOKUP 함수는 위에서 첫번째 결과만 출력합니다.
: 만약 마지막 결과를 출력하거나, n번째 결과를 출력하려면 아래 함수나 공식을 사용합니다. - VLOOKUP 함수는 하나의 결과만 출력할 수 있습니다.
: 만약 여러개의 결과를 반환하려면 FILTER 함수나 이번 강의에서 소개해드린 공식을 사용합니다.
VLOOKUP 여러개 값 불러오기 공식의 첫번째 계산은 MATCH+ROW 공식에서 시작됩니다. MATCH+ROW 공식을 사용하면 1부터 증가하는 연속되는 순번을 배열로 반환할 수 있습니다.
- MATCH+ROW 공식으로 순번 만들기 : 예제파일의 L8:L23 범위를 선택한 뒤, 아래 수식을 입력합니다. 이후 CTRL + SHIFT + ENTER로 수식을 입력하면 1부터 증가하는 연속된 순번이 반환됩니다.
=MATCH(ROW($B$6:$B$21),ROW($B$6:$B$21))
MATCH + ROW 함수로 연속되는 순번을 출력합니다. 오빠두Tip : L8:L23 보다 넓은 범위를 선택할 경우, 기존 범위의 개수인 16개를 넘어가는 셀에는 #N/A 오류가 반환됩니다. (이는 정상결과이므로 다음 단계로 넘어가도 괜찮습니다.) - 만약 365 버전 사용자일 경우, L8셀에 수식을 입력한 뒤, ENTER 키로 수식을 입력하면 범위 위로 배열이 반환됩니다. (파란색 테두리 확인)
365 버전에서는 배열이 범위 위로 반환됩니다.
이제 IF 함수를 사용해서, 특정 조건을 만족하는 경우에만 MATCH+ROW 함수로 계산된 순번이 반환되도록 공식을 입력합니다.
- IF 함수로 조건을 만족할 경우 순번 반환 : 예제파일의 N8:N23 범위를 선택한 뒤, 아래 수식을 입력합니다. 이후 CTRL + SHIFT + ENTER 로 수식을 입력하면 조건을 만족하는 경우의 순번만 범위로 반환됩니다.
=IF(($F$8=$B$6:$B$21),MATCH(ROW($B$6:$B$21),ROW($B$6:$B$21)),"")
IF 함수로 조건을 만족할 경우, 순번을 출력합니다. - 만약 365 버전 사용자일 경우, N8셀에 수식을 입력한 뒤 ENTER 키로 수식을 입력하면 범위 위로 배열이 반환됩니다. (파란색 테두리 확인)
365 버전에서는 배열이 범위 위로 반환됩니다.
ROWS 함수와 확장범위를 활용하면 아래로 자동채우기를 할 때마다 1씩 증가하는 연속된 순번을 만들 수 있습니다. ROWS+확장범위 공식은 다양한 자동화 배열수식에 활용할 수 있으므로 엑셀 고급단계로 넘어가기 위한 실무자라면 반드시 숙지하시길 권장드립니다.
- ROWS+확장범위로 순번 만들기 : 예제파일의 P8셀을 선택한 뒤, 아래 수식을 입력합니다. 이후 수식을 아래로 자동채우기 하면 1부터 증가하는 연속된 순번이 만들어집니다.
=ROWS($A$1:A1)
ROWS+확장범위는 자동채우기를 할 때 연속된 순번을 반환하는 공식입니다. 오빠두Tip : 공식에 사용된 $A$1:A1 은 $B$10:B10 으로 입력해도 무방합니다. 또한 A1셀 선택 후, 콜론(:)을 입력하면 셀주소를 범위로 손쉽게 변경할 수 있습니다. - 엑셀의 참조방식과 절대참조, 혼합참조에 대한 자세한 설명은 아래 기초입문강의를 참고하세요.
이제 SMALL 함수를 활용하여, 우리가 방금 전 IF 함수로 반환한 순번 범위의 값을 작은 값 부터 하나씩 차례대로 출력합니다.
- 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))
SMALL 함수로 순번범위의 가장 작은값 부터 차례대로 출력합니다. 오빠두Tip : 365 버전 사용자는 수식을 ENTER 로 입력해도 괜찮습니다.
이제 INDEX 함수를 사용하여 VLOOKUP 여러개 값 출력하기 공식을 완성합니다.
- 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)))
INDEX 함수로 VLOOKUP 여러개 값 출력 공식을 완성합니다. - 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))),"")
IFERROR 함수로 오류처리를 하면 공식이 마무리됩니다.
