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

실습 가이드

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

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

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

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

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

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

VLOOKUP 함수의 제한 사항

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

1. VLOOKUP 함수의 참조범위는 첫번째 열에 있어야 합니다.
   : 즉, 참조범위의 오른쪽 방향으로만 조회할 수 있습니다. 참조범위의 왼쪽 방향으로 조회하려면 INDEX/MATCH 공식 또는 XLOOKUP 함수를 사용합니다.
   해결책1 INDEX/MATCH 공식 (365 이전)
   해결책2 XLOOKUP 함수 (365 이후)
2. VLOOKUP 함수는 위에서 첫번째로 일치하는 결과만 반환합니다.
   : 마지막 결과를 출력하거나 n번째 결과를 출력하려면 아래 공식을 사용합니다.
   마지막 결과를 출력하려면 마지막 값 출력 공식
   n번째 결과를 출력하려면 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))

   

   오빠두Tip : L8:L23 보다 넓은 범위를 선택하면, 기존 범위의 개수인 16개를 넘어가는 셀에는 #N/A 오류가 반환됩니다. 이는 정상적인 결과이므로 다음 단계로 넘어가도 무방합니다.
2. 365 버전 사용자는 L8 셀에 수식을 입력한 뒤 ENTER 키만 눌러도 범위 위로 배열이 자동으로 반환됩니다. (파란색 테두리 확인)

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)),"")

   

2. 365 버전 사용자는 N8 셀에 수식을 입력한 뒤 ENTER 키만 눌러도 범위 위로 배열이 자동으로 반환됩니다. (파란색 테두리 확인)

ROWS 확장 범위 사용법

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

1. ROWS+확장범위로 순번 만들기 : 예제파일의 P8 셀을 선택한 뒤 아래 수식을 입력합니다. 이후 수식을 아래로 자동채우기 하면 1부터 증가하는 연속된 순번이 출력됩니다.
   =ROWS($A$1:A1)

   

   오빠두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))

   

   오빠두Tip : 365 버전 사용자는 수식을 ENTER 로만 입력해도 동일한 결과가 반환됩니다.

VLOOKUP 여러개 값 공식 완성

마지막으로 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)))

   

   오빠두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))),"")