VLOOKUP 함수는 Microsoft Excel에서 특정 값을 검색하여 정확히 일치하거나 근사한 값을 찾아 반환하는 가장 강력하고 유용한 함수 중 하나입니다. 하지만 때로는 하나의 VLOOKUP만으로는 원하는 결과를 얻기 어려운 경우가 있습니다. 이럴 때 여러 개의 VLOOKUP을 중첩해서 사용하면 문제를 해결할 수 있습니다. 이 글에서는 엑셀에서 중첩 VLOOKUP(Nested VLOOKUP)을 구현하는 방법을 단계별로 자세히 알려드리겠습니다.
실습 파일 다운로드
이 글에서 사용하는 연습용 엑셀 파일은 아래 링크에서 무료로 다운로드할 수 있습니다.
엑셀의 VLOOKUP 함수란?
VLOOKUP은 'Vertical Lookup(세로 방향 조회)'의 약자로, 표의 첫 번째 열에서 특정 값을 찾은 뒤 같은 행에 있는 다른 열의 값을 반환하는 함수입니다.
기본 수식:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
각 인수의 의미는 다음과 같습니다.
| 인수 | 설명 |
|---|---|
| lookup_value | 찾으려는 값 |
| table_array | 값을 검색할 데이터 범위 |
| col_index_num | 결과를 가져올 열의 번호 |
| range_lookup | 논리값(TRUE 또는 FALSE) FALSE(또는 0)는 정확한 일치, TRUE(또는 1)는 근사 일치를 의미합니다. |
중첩 VLOOKUP을 활용하는 3가지 기준
이번 섹션에서는 중첩 VLOOKUP을 활용해 제품 가격과 판매액을 구하는 방법, 그리고 IFERROR 함수와 결합하는 방법까지 차례대로 살펴보겠습니다.
1. 중첩 VLOOKUP으로 제품 가격 추출하기
다음과 같은 데이터가 있다고 가정해 보겠습니다. 결과 테이블에서 제품 ID를 기준으로 가격을 가져오고 싶은데, 두 정보가 하나의 표에 함께 있지 않습니다. ID는 표 1에 있고, 가격은 표 2에 있습니다. 이 경우 표 1에서 ID를 검색한 뒤, 그 결과값을 이용해 표 2에서 가격을 찾아 결과 테이블의 Price 열에 표시하는 것이 핵심입니다.
단계:
- 가격을 표시할 셀을 클릭합니다 (예: 결과 테이블에서 ID A101 옆의 셀, I5)
- 아래 수식을 입력합니다.
=VLOOKUP(VLOOKUP(H5, $B$5:$C$9, 2, FALSE), $E$5:$F$9, 2, FALSE)
수식의 구성 요소는 다음과 같습니다.
H5 = A101, 결과 테이블에 저장된 조회 값(ID)
$B$5:$C$9 = 표 1에서 조회 값을 검색할 데이터 범위
$E$5:$F$9 = 표 2에서 조회 값을 검색할 데이터 범위
2 = 결과를 가져올 열 번호
FALSE = 정확한 일치를 원하므로 FALSE를 지정합니다.
- 키보드에서 Ctrl + Shift + Enter를 누릅니다.
결과 셀(I5)에 ID A101의 가격($50)이 표시됩니다.
- 채우기 핸들(Fill Handle)을 아래로 드래그하여 나머지 행에도 수식을 적용하면, 결과 테이블의 모든 제품 ID에 대한 가격을 한 번에 구할 수 있습니다.
위 그림처럼 중첩 VLOOKUP 수식 하나만으로 결과 테이블의 모든 제품 ID 가격을 손쉽게 가져왔습니다.
수식 분석:
- VLOOKUP(H5, $B$5:$C$9, 2, 0)
- 출력 결과: "Football"
- 설명: H5(A101)를 $B$5:$C$9 범위에서 검색하고, 열 번호 2를 지정해 정확히 일치하는 제품명 "Football"을 반환합니다.
- VLOOKUP(VLOOKUP(H5, $B$5:$C$9,2,0), $E$5:$F$9, 2, 0) → 변환되면
- VLOOKUP("Football", $E$5:$F$9, 2, 0)
- 출력 결과: $50
- 설명: 내부 VLOOKUP에서 얻은 "Football"을 $E$5:$F$9 범위에서 검색해 정확히 일치하는 가격 $50을 반환합니다.
2. 중첩 VLOOKUP으로 판매액 구하기
이번에는 제품명을 기준으로 판매액을 구해 보겠습니다. 표 1에서 제품명을 검색하고, 매칭된 값을 바탕으로 표 2에서 판매액을 추출해 결과 테이블의 Sales 열에 표시합니다.
단계:
- 판매액을 표시할 셀을 클릭합니다 (예: 결과 테이블에서 Football 옆의 셀, J5)
- 아래 수식을 입력합니다.
=VLOOKUP(VLOOKUP(I5,$B$5:$C$9,2,0),$E$5:$G$9,2,0)
수식의 구성 요소는 다음과 같습니다.
I5 = Football, 결과 테이블에 저장된 조회 값(제품명)
$B$5:$C$9 = 표 1에서 조회 값을 검색할 데이터 범위
$E$5:$G$9 = 표 2에서 조회 값을 검색할 데이터 범위
2 = 결과를 가져올 열 번호
0 = 정확한 일치를 원하므로 0 또는 FALSE를 지정합니다.
- 키보드에서 Ctrl + Shift + Enter를 누릅니다.
결과 셀(J5)에 Football의 판매액($1,000)이 표시됩니다.
- 채우기 핸들을 아래로 드래그해 나머지 행에도 수식을 적용하면 모든 제품의 판매액을 구할 수 있습니다.
위 그림처럼 중첩 VLOOKUP 수식으로 결과 테이블의 모든 제품 판매액을 성공적으로 가져왔습니다.
수식 분석:
- VLOOKUP(I5, $B$5:$C$9, 2, 0)
- 출력 결과: "David"
- 설명: I5(Football)를 $B$5:$C$9 범위에서 검색하고, 열 번호 2를 지정해 정확히 일치하는 영업사원 이름 "David"를 반환합니다.
- VLOOKUP(VLOOKUP(I5, $B$5:$C$9, 2, 0), $E$5:$G$9, 2, 0) → 변환되면
- VLOOKUP("David", $E$5:$G$9, 2, 0)
- 출력 결과: $1,000
- 설명: 내부 VLOOKUP에서 얻은 "David"를 $E$5:$G$9 범위에서 검색해 정확히 일치하는 판매액 $1,000을 반환합니다.
3. 중첩 VLOOKUP과 IFERROR 함수 결합하기
이번 섹션에서는 중첩 VLOOKUP과 IFERROR 함수를 결합해, 하나의 조회 값으로 여러 개의 표에서 결과를 추출하는 방법을 알아보겠습니다.
다음 데이터에서 제품들이 3개의 서로 다른 표에 나눠져 있습니다. 세 표 중에서 ID(A106)에 해당하는 제품을 찾아 결과 테이블의 Product 열에 표시해 보겠습니다.
단계:
- 제품명을 표시할 셀을 클릭합니다 (예: 결과 테이블에서 ID A106 옆의 셀, L5)
- 아래 수식을 입력합니다.
=IFERROR(VLOOKUP(K5,$B$5:$C$7,2,0),IFERROR(VLOOKUP(K5,$E$5:$F$7,2,0),VLOOKUP(K5,$H$5:$I$7,2,0)))
수식의 구성 요소는 다음과 같습니다.
K5 = A106, 결과 테이블에 저장된 조회 값(ID)
$B$5:$C$7 = 표 1에서 조회 값을 검색할 데이터 범위
$E$5:$F$7 = 표 2에서 조회 값을 검색할 데이터 범위
$H$5:$I$7 = 표 3에서 조회 값을 검색할 데이터 범위
2 = 결과를 가져올 열 번호
0 = 정확한 일치를 원하므로 0 또는 FALSE를 지정합니다.
- 키보드에서 Ctrl + Shift + Enter를 누릅니다.
결과 셀(L5)에 조회한 ID A106의 제품명(Cricket Bat)이 표시됩니다.
수식 분석:
- VLOOKUP(K5, $H$5:$I$7, 2, 0)
- 출력 결과: #N/A
- 설명: K5(A106)를 $H$5:$I$7 범위(표 3)에서 검색하지만 해당 ID가 없어 #N/A 오류를 반환합니다.
- VLOOKUP(K5, $E$5:$F$7, 2, 0)
- 출력 결과: "Cricket Bat"
- 설명: K5(A106)를 $E$5:$F$7 범위(표 2)에서 검색하면 일치하는 값이 있으므로 "Cricket Bat"을 반환합니다.
- IFERROR(VLOOKUP(K5, $E$5:$F$7, 2, 0), VLOOKUP(K5, $H$5:$I$7, 2, 0)) → 변환되면
- IFERROR("Cricket Bat", #N/A)
- 출력 결과: "Cricket Bat"
- 설명: IFERROR 함수는 첫 번째 수식의 결과가 오류인지 확인하고, 오류라면 사용자가 지정한 다른 값을 반환합니다.
- VLOOKUP(K5, $B$5:$C$7, 2, 0)
- 출력 결과: #N/A
- 설명: K5(A106)를 $B$5:$C$7 범위(표 1)에서 검색하지만 해당 ID가 없어 #N/A 오류를 반환합니다.
- IFERROR(VLOOKUP(K5,B5:C7,2,0),IFERROR(VLOOKUP(K5,E5:F7,2,0),VLOOKUP(K5,H5:I7,2,0))) → 변환되면
- IFERROR(#N/A, "Cricket Bat")
- 출력 결과: "Cricket Bat"
- 설명: 첫 번째 VLOOKUP이 #N/A 오류를 반환했기 때문에, IFERROR는 최종적으로 "Cricket Bat"을 출력합니다.
주의사항
- 검색 대상 데이터 범위는 고정되어 있으므로, 배열 표의 셀 참조 앞에 반드시 달러($) 기호를 붙여 절대 참조로 만들어야 합니다.
- 배열 수식으로 결과를 추출할 때는 반드시 Ctrl + Shift + Enter를 눌러야 합니다. 단, Microsoft 365를 사용하는 경우에는 Enter 키만 눌러도 정상 작동합니다.
마무리
이 글에서는 엑셀에서 중첩 VLOOKUP을 3가지 다른 기준으로 활용하는 방법을 자세히 설명했습니다. 이 내용이 실무에서 데이터를 검색하고 관리하는 데 큰 도움이 되기를 바랍니다. 주제에 대해 궁금한 점이 있다면 언제든지 질문해 주세요.
함께 읽으면 좋은 글
- 엑셀에서 VLOOKUP과 SUM 함수 함께 사용하는 방법 (6가지 방법)
- 엑셀 VLOOKUP으로 근사 일치 값 찾기 (예제 5가지)
- 엑셀에서 VLOOKUP 대소문자 구분하여 사용하는 방법 (4가지 방법)
- 엑셀에서 VLOOKUP 다중 조건 활용하기 (6가지 방법 + 대안)
- 엑셀 VBA에서 VLOOKUP 함수 사용하는 방법 (예제 4가지)