VLOOKUP 완벽 가이드

VLOOKUP이란 무엇이며 왜 중요한가

VLOOKUP(수직 검색)은 테이블의 첫 번째 열에서 값을 검색하고 다른 열에서 해당하는 값을 반환합니다. Excel의 내장 검색 엔진이라고 생각하세요. 제품 ID를 입력하면 가격, 카테고리, 재고 수준을 즉시 확인할 수 있습니다. 보고서 작성, 데이터 대사, 데이터 병합 작업을 한다면 VLOOKUP으로 매주 몇 시간을 절약할 수 있습니다.

구문: =VLOOKUP(검색값, 테이블배열, 열번호, [범위검색])

검색값 — 검색하려는 값이 포함된 셀 (예: A2의 제품 ID)
테이블배열 — 검색 열과 결과 열을 모두 포함하는 전체 데이터 범위
열번호 — 결과가 있는 열 번호 (테이블배열의 왼쪽부터 세어서)
범위검색 — FALSE는 정확 일치 (95%의 경우 사용), TRUE는 근사 일치

VLOOKUP 구문 다이어그램: 각 인수를 샘플 스프레드시트에 매핑 — lookup_value는 'SKU-301'이 포함된 A2 셀을 가리키고, table_array는 B2:D100 범위를 강조, col_index_num은 3으로 원 표시되어 Price 열을 가리키며, range_lookup은 정확 일치를 위해 FALSE 표시 — 그림 1 — 4가지 VLOOKUP 인수를 시각적으로 설명합니다. 열번호는 워크시트의 A열이 아닌 선택한 범위의 왼쪽 가장자리부터 계산합니다.

단계별: 첫 번째 VLOOKUP

실제 시나리오를 살펴보겠습니다. B열부터 E열까지 제품 카탈로그가 있고, A열에 검색할 제품 ID 목록이 있습니다. 카탈로그에서 가격을 F열로 가져오려고 합니다.

1단계 — 데이터 설정하기

워크북을 열고 데이터 레이아웃을 확인합니다:

제품 카탈로그 시트로 이동합니다. B열에 제품 ID, D열에 가격이 있는지 확인합니다.
카탈로그 범위 내에 완전히 빈 행이 없도록 합니다. Excel은 빈 행을 데이터의 끝으로 간주하여 VLOOKUP이 그 아래 행을 건너뛸 수 있습니다.
카탈로그 범위(B2:E500)를 선택하고 Ctrl+T를 눌러 Excel 테이블로 변환합니다. 테이블 디자인 탭에서 Catalog로 이름을 지정합니다. 테이블은 자동 확장되며 구조화된 참조를 제공합니다.

Excel spreadsheet showing a product catalog in columns B-E: Column B 'Product ID' (B2:B12), Column C 'Product Name', Column D 'Price', Column E 'Category'. Column A shows a smaller lookup list of 5 product IDs (A2:A6). Column F is empty with header 'Lookup Result'. The catalog range B2:E12 is formatted as an Excel Table with blue banded rows. — 그림 2 — VLOOKUP을 작성하기 전의 샘플 데이터 레이아웃. 카탈로그는 오른쪽(B~E열), 검색 목록은 A열, F열에 결과가 표시됩니다.

2단계 — 첫 번째 결과 셀에 수식 작성하기

F2 셀("검색 결과" 아래 첫 번째 행)을 클릭합니다.
=VLOOKUP(을 입력합니다 — Excel이 4가지 인수에 대한 툴팁을 표시합니다. 입력하면서 참고하세요.
검색값으로 A2 셀을 클릭합니다. Excel이 A2를 수식에 삽입합니다.
쉼표를 입력한 후 마우스로 카탈로그 범위 전체 B2:E500을 선택합니다. 즉시 F4를 눌러 참조를 고정합니다 — $B$2:$E$500으로 표시되어야 합니다. 이 절대 참조는 수식을 아래로 복사할 때 범위가 이동하는 것을 방지합니다.
쉼표를 입력한 후 열번호로 3을 입력합니다. 왜 3일까요? 가격은 B2:E500의 왼쪽 가장자리부터 세어 3번째 열이기 때문입니다: B=1, C=2, D=3.
쉼표를 입력한 후 정확 일치를 위해 FALSE를 입력합니다.
괄호를 닫고 Enter를 누릅니다.

완성된 수식: =VLOOKUP(A2, $B$2:$E$500, 3, FALSE)

Excel formula bar showing =VLOOKUP(A2, $B$2:$E$500, 3, FALSE) with each argument color-highlighted. Cell F2 displays the returned price value. A tooltip near the formula bar shows the four-argument hint. The cursor is positioned in cell F2. — 그림 3 — F2의 완성된 수식. 테이블배열의 $ 기호에 주목하세요 — 수식을 후속 행으로 복사할 때 필수적입니다.

3단계 — 수식을 아래로 복사하기

F2 셀을 클릭하여 선택합니다. 선택 영역의 오른쪽 아래 모서리에 작은 녹색 사각형(채우기 핸들)이 표시됩니다.
채우기 핸들을 더블 클릭합니다. Excel이 A열의 데이터에 맞춰 수식을 아래로 자동 채웁니다.
또는 F2를 선택하고 Ctrl+Shift+아래 화살표로 마지막 행까지 선택을 확장한 후 Ctrl+D(아래로 채우기)를 누릅니다.
몇 개 행을 확인합니다: F5를 클릭하고 수식 입력줄을 확인합니다. =VLOOKUP(A5, $B$2:$E$500, 3, FALSE)로 표시되어야 합니다 — A5는 변경되었지만(상대 참조) $B$2:$E$500은 그대로(절대 참조)임을 확인하세요.

Excel sheet showing column F filled with VLOOKUP results. Cell F2 shows $49.99, F3 shows $12.50, F4 shows #N/A (for a product ID not found in the catalog), F5 shows $299.00. The fill handle is highlighted on F2, and an arrow indicates the formula was copied down. — 그림 4 — 수식을 아래로 복사한 후. F4의 #N/A는 해당 제품 ID가 카탈로그에 없음을 의미합니다 — 다음 단계에서 수정합니다.

4단계 — IFERROR로 누락된 값 처리하기

#N/A 오류는 보고서를 망가뜨려 보이게 합니다. 대신 친숙한 메시지를 표시하도록 수식을 래핑합니다:

F2 셀을 더블 클릭하여 수식을 편집합니다.
=VLOOKUP 바로 앞에 =IFERROR(를 입력합니다.
수식 끝(VLOOKUP의 닫는 괄호 뒤)으로 이동하여 쉼표를 입력한 다음 "카탈로그에 없음")을 입력합니다.
Enter를 누릅니다. 이제 수식은: =IFERROR(VLOOKUP(A2, $B$2:$E$500, 3, FALSE), "카탈로그에 없음")
F2의 채우기 핸들을 다시 더블 클릭하여 개선된 수식을 아래로 복사합니다.
이제 4행에는 못생긴 #N/A 대신 "카탈로그에 없음"이 표시됩니다.

주요 기법 및 모범 사례

기법 1 — 이름이 지정된 범위로 자기 문서화된 수식 만들기

$B$2:$E$500과 같은 난해한 셀 참조는 몇 주 후에 이해하기 어렵습니다. 이름이 지정된 범위로 이 문제를 해결합니다:

B2:E500 범위를 선택합니다. 이름 상자(일반적으로 셀 주소가 표시되는 수식 입력줄 왼쪽 필드)를 클릭합니다.
CatalogTable을 입력하고 Enter를 누릅니다. 이제 범위에 이름이 지정되었습니다.
VLOOKUP을 다음과 같이 다시 작성합니다: =IFERROR(VLOOKUP(A2, CatalogTable, 3, FALSE), "찾을 수 없음")
이 수식을 읽는 사람은 누구나 CatalogTable이 검색 소스임을 즉시 알 수 있습니다 — 셀 참조를 추적할 필요가 없습니다.

기법 2 — MATCH를 사용한 동적 열 인덱스

열번호를 3으로 하드코딩하면 열을 삽입하거나 삭제할 때 깨집니다. 대신 MATCH가 올바른 열 번호를 자동으로 찾도록 합니다:

1행(B1:E1)에 헤더가 있다고 가정합니다: "제품 ID", "제품명", "가격", "카테고리".
하드코딩된 3을 다음과 같이 바꿉니다: MATCH("가격", $B$1:$E$1, 0)
완성된 수식: =IFERROR(VLOOKUP(A2, CatalogTable, MATCH("가격", $B$1:$E$1, 0), FALSE), "찾을 수 없음")
이제 누군가 C와 D 사이에 "공급업체" 열을 삽입하면 가격이 3열에서 4열로 이동하지만 — MATCH가 자동으로 찾아내므로 수식은 계속 작동합니다.

기법 3 — VLOOKUP + COLUMN 트릭으로 다중 열 반환

각 검색 ID에 대해 제품명, 가격, 카테고리를 모두 가져와야 할 때:

F2(이름): =IFERROR(VLOOKUP($A2, CatalogTable, 2, FALSE), "")
G2(가격): =IFERROR(VLOOKUP($A2, CatalogTable, 3, FALSE), "")
H2(카테고리): =IFERROR(VLOOKUP($A2, CatalogTable, 4, FALSE), "")
$A2에 주목하세요 — 달러 기호는 열 참조를 A로 고정하지만 행(2)은 아래로 복사할 때 조정됩니다. 이렇게 하면 세 수식을 모두 한 번에 오른쪽과 아래로 복사할 수 있습니다.

흔한 실수 (그리고 즉시 해결하는 방법)

실수: 데이터가 분명히 있는데도 VLOOKUP이 #N/A를 반환합니다.
해결: 검색값과 테이블 첫 번째 열의 데이터 유형이 다릅니다. "00123"(텍스트) ≠ 123(숫자). 검색 열을 선택하고 데이터 > 텍스트 나누기 > 마침으로 텍스트-숫자를 실제 숫자로 변환합니다. 또는 검색값을 TEXT(A2, "00000")으로 래핑합니다.
실수: 2행에서는 작동하지만 3행으로 복사하면 깨집니다.
해결: 테이블배열을 $ 기호로 잠그는 것을 잊었습니다. F2를 편집하고 수식 내에서 B2:E500을 선택한 후 F4를 누릅니다. $B$2:$E$500이 되어야 합니다.
실수: VLOOKUP이 잘못된 값을 반환합니다 — 거의 맞지만 약간 다릅니다.
해결: 네 번째 인수를 생략했습니다. FALSE가 없으면 VLOOKUP은 기본적으로 근사 일치를 사용합니다. 정렬된 목록에서 가장 가까운 값을 찾는데, 원하는 정확 일치가 아닐 수 있습니다. 항상 FALSE를 명시적으로 입력하세요.
실수: 카탈로그에 열을 삽입했더니 모든 VLOOKUP이 깨졌습니다.
해결: 위의 MATCH 기법을 사용하여 열번호를 동적으로 만듭니다. 이미 깨졌다면 찾기 및 바꾸기(Ctrl+H)로 열 번호를 일괄 업데이트합니다.
실수: 중복이 있을 때 VLOOKUP이 첫 번째 일치 항목만 반환합니다.
해결: VLOOKUP은 항상 검색 열의 첫 번째 일치 항목을 반환합니다. 모든 일치 항목이 필요하면 SMALL/IF 배열 수식을 사용한 INDEX-MATCH로 전환하거나, 마지막 일치 항목을 반환할 수 있는 XLOOKUP으로 업그레이드하세요.

파워 유저를 위한 고급 팁

VLOOKUP + MATCH를 사용한 2차원 검색: =VLOOKUP(A2, Table, MATCH("Q3", Headers, 0), FALSE)로 행(제품)과 열(분기)을 모두 검색할 수 있습니다. "Q3"을 "Q4"로 한 곳만 변경하면 다음 분기 데이터를 가져옵니다.
와일드카드를 사용한 부분 일치: =VLOOKUP("*"&A1&"*", Table, 2, FALSE)는 A1의 텍스트를 포함하는 셀을 긴 문자열 속에 있더라도 찾습니다. 제품 설명 검색에 유용합니다.
CHOOSE를 사용한 역방향 검색: 오른쪽 열에서 검색하여 왼쪽 열에서 반환해야 하나요? =VLOOKUP(A2, CHOOSE({1,2}, D2:D100, A2:A100), 2, FALSE)가 열을 가상으로 교체하여 VLOOKUP이 검색 열을 먼저 볼 수 있게 합니다.
XLOOKUP으로 전환할 시기: Excel 2021 또는 Microsoft 365를 사용 중이라면 XLOOKUP이 위의 모든 제한을 없앱니다 — 왼쪽에서 오른쪽, 오른쪽에서 왼쪽, 기본 정확 일치, 내장 오류 처리. 구문: =XLOOKUP(A2, SearchColumn, ReturnColumn, "찾을 수 없음"). Excel 버전이 지원한다면 배울 가치가 있습니다.

Download Practice Template

이 기사에 대해 질문이 있거나 오류를 발견하셨나요? 피드백 제출