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

실습 가이드

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

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

365 버전 사용자는 본 강의에서 소개해드린 공식 대신 FILTER 함수를 사용하면 보다 빠르고 편리하게 동일한 결과를 얻을 수 있습니다. FILTER 함수에 대한 자세한 설명은 아래 FILTER 함수 실전예제 총정리 영상강의를 참고하세요.

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

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

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

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

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

1. VLOOKUP 함수의 참조범위는 첫번째 열에 있어야 합니다.
   : 즉, 참조범위의 오른쪽 방향으로만 조회할 수 있습니다. 참조범위의 왼쪽 방향으로 조회하려면 INDEX/MATCH 공식 또는 XLOOKUP 함수를 사용합니다.
   해결책1 INDEX/MATCH 공식 (365 이전)
   해결책2 XLOOKUP 함수 (365 이후)
2. VLOOKUP 함수는 위에서 첫번째로 일치하는 결과만 반환합니다.
   : 마지막 결과를 출력하거나 n번째 결과를 출력하려면 아래 공식을 사용합니다.
   마지막 결과를 출력하려면 마지막 값 출력 공식
   n번째 결과를 출력하려면 n 번째 결과 출력 공식
3. VLOOKUP 함수는 하나의 결과만 출력할 수 있습니다.
   : 여러 개의 결과를 동시에 반환하려면 FILTER 함수 또는 본 강의에서 소개해드린 공식을 사용합니다.
   

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

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

   

   오빠두Tip : 공식에 사용된 $A$1:A1 은 $B$10:B10 으로 입력해도 무방합니다. 또한 A1 셀을 선택한 뒤 콜론(:)을 입력하면 셀 주소를 범위로 손쉽게 변경할 수 있습니다.
2. 엑셀의 참조 방식과 절대참조·혼합참조에 대한 자세한 설명은 아래 기초입문강의를 참고하세요.
   

이번에는 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 로만 입력해도 동일한 결과가 반환됩니다.

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