엑셀 작업을 하다 보면, 두 개의 다른 표에 있는 데이터를 하나로 합쳐야 하는 일이 정말 많습니다. 예를 들어, **(표1)**에는 ‘상품 ID’와 ‘판매 수량’이 있고, **(표2)**에는 ‘상품 ID’와 ‘상품명’, ‘가격’이 있다고 상상해보세요. 판매 목록에 ‘상품명’과 ‘가격’을 가져오려면 어떻게 해야 할까요?
일일이 복사해서 붙여넣기엔 너무 번거롭고 실수하기도 쉽습니다. 바로 이럴 때, 엑셀의 가장 강력한 무기 중 하나인 VLOOKUP 함수가 등장합니다.
VLOOKUP이란 무엇일까요? (가장 쉬운 개념)
VLOOKUP은 **“수직(Vertical)으로 찾아본다(Lookup)“**는 뜻입니다. 복잡하게 생각할 것 없이, **엑셀을 위한 ‘전화번호부’**라고 생각하면 가장 쉽습니다.
- 찾고 싶은 것 (이름): ‘홍길동’
- 찾을 범위 (전화번호부): 주소록 전체
- 가져올 정보 (전화번호): ‘홍길동’을 찾아서 그 옆에 있는 전화번호를 가져와!
VLOOKUP은 이처럼 특정 값을 기준으로, 지정된 표(범위)에서 원하는 정보를 찾아 가져오는 함수입니다.
VLOOKUP 수식 완벽 분해
VLOOKUP의 기본 수식은 4개의 인수로 구성됩니다. 하나씩 차근차근 살펴보겠습니다.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
1. lookup_value: 무엇을 찾을까요?
- ‘찾을 값’입니다. 전화번호부의 ‘이름’에 해당하죠.
- 우리 예시에서는 (표1)에 있는 ‘상품 ID’ 셀(예:
A2)이 됩니다.
2. table_array: 어디서 찾을까요?
- ‘찾을 범위’입니다. 즉, 전화번호부 전체에 해당합니다.
- 우리 예시에서는 (표2)의 ‘상품 ID’부터 ‘가격’까지의 전체 데이터 범위(예:
F2:H10)가 됩니다. - ⚠️ 핵심 규칙:
lookup_value(‘상품 ID’)는 반드시table_array범위의 첫 번째 열에 있어야 합니다! - ⭐ 전문가 팁: 이 범위는 수식을 아래로 복사해도 변하면 안 되므로, **절대 참조(
$)**로 고정해야 합니다. 범위를 선택하고F4키를 누르면F2:H10이$F$2:$H$10으로 바뀝니다.
3. col_index_num: 어떤 정보를 가져올까요?
- ‘가져올 정보가 있는 열 번호’입니다.
table_array범위 안에서 몇 번째 열의 정보를 가져올지 숫자로 지정합니다.- ‘상품명’을 가져오고 싶다면,
table_array($F$2:$H$10)에서 ‘상품명’은 2번째 열에 있으므로2를 입력합니다. ‘가격’을 원한다면3을 입력합니다.
4. range_lookup: 얼마나 정확하게 찾을까요?
- ‘검색 방법’을 지정하며,
TRUE또는FALSE를 선택합니다. FALSE(정확히 일치): 99%의 경우, 여러분이 원하는 옵션입니다.lookup_value와 정확히 일치하는 값만 찾습니다. 값이 없으면#N/A오류를 반환합니다.TRUE(유사 일치): 특정 구간에 해당하는 값을 찾을 때 사용합니다(예: 점수에 따른 등급). 하지만 사용법이 까다롭고(기준 열이 오름차순 정렬되어야 함), 초보자가 사용하면 예상치 못한 결과를 얻기 쉽습니다.
결론: 실무에서는 고민 없이 FALSE를 사용하세요!
단계별 실전 예제
아래 두 표를 가지고 직접 ‘상품명’을 가져와 보겠습니다.
| (A) | (B) | (C) | (F) | (G) | (H) | |
|---|---|---|---|---|---|---|
| 상품 ID | 판매 수량 | 상품명 | 상품 ID | 상품명 | 가격 | |
| 102 | 5 | 101 | 사과 | 1,000 | ||
| 103 | 3 | 102 | 바나나 | 1,500 | ||
| 101 | 7 | 103 | 오렌지 | 2,000 |
- 결과를 표시할
C2셀에=VLOOKUP(이라고 입력합니다. lookup_value: 찾을 값인A2셀을 클릭합니다. 수식:=VLOOKUP(A2,를 입력하고table_array: 상품 정보가 있는$F$2:$H$4범위를 드래그한 후F4키를 눌러 고정합니다. 수식:=VLOOKUP(A2, $F$2:$H$4,를 입력하고col_index_num: ‘상품명’은 범위 내 두 번째 열이므로2를 입력합니다. 수식:=VLOOKUP(A2, $F$2:$H$4, 2,를 입력하고range_lookup: 정확히 일치해야 하므로FALSE를 입력합니다. 수식:=VLOOKUP(A2, $F$2:$H$4, 2, FALSE)- 괄호를 닫고 Enter를 누르면
C2셀에 ‘바나나’가 표시됩니다! 이제C2셀의 채우기 핸들(셀 오른쪽 아래 작은 사각형)을 더블 클릭하거나 아래로 끌어 수식을 복사하면 모든 상품명이 자동으로 채워집니다.
”왜 VLOOKUP이 안될까요?” 가장 흔한 오류와 해결책
#N/A오류: 가장 흔한 오류로, ‘Not Available’ 즉, 값을 찾지 못했다는 뜻입니다.- 원인 1: 찾는 값이 범위에 정말로 없음.
- 원인 2: 숫자와 텍스트 형식 불일치. 한쪽은 숫자
101인데 다른 쪽은 텍스트'101일 경우, 다른 값으로 인식합니다. - 원인 3: 눈에 안 보이는 공백. 셀 값 앞이나 뒤에 공백이 있으면 다른 값으로 인식합니다.
TRIM함수로 공백을 제거해보세요.
#REF!오류:col_index_num이table_array의 전체 열 개수보다 클 때 발생합니다. 범위를 3열만 잡고 4번째 열의 값을 가져오라고 명령한 셈입니다.- 결과가 자꾸 밀리거나 이상해요:
table_array범위를 절대 참조($)로 고정하지 않았을 가능성이 높습니다. 수식을 아래로 복사하면 검색 범위도 같이 한 칸씩 밀려 내려가기 때문입니다.
VLOOKUP의 한계와 현대적 대안: XLOOKUP
VLOOKUP은 매우 강력하지만, ‘찾는 값이 반드시 범위의 첫 번째 열에 있어야 한다’는 치명적인 제약이 있습니다. 만약 ‘상품명’으로 ‘상품 ID’를 찾아야 한다면 VLOOKUP은 사용할 수 없습니다.
최신 엑셀(Microsoft 365, Excel 2021 등)에서는 이 모든 문제를 해결한 XLOOKUP 함수를 사용할 수 있습니다.
=XLOOKUP(lookup_value, lookup_array, return_array)
lookup_array: 찾을 값이 있는 열 (예: 상품명 열 전체)return_array: 가져올 값이 있는 열 (예: 상품 ID 열 전체)
VLOOKUP처럼 범위를 통째로 잡고 열 번호를 셀 필요 없이, ‘찾을 열’과 ‘가져올 열’만 지정하면 되므로 훨씬 직관적이고 강력합니다. 왼쪽 방향 조회도 당연히 가능합니다.
결론
VLOOKUP은 엑셀 데이터 관리의 핵심입니다. 처음에는 조금 복잡해 보일 수 있지만, 아래 세 가지 원칙만 기억하면 대부분의 문제를 해결할 수 있습니다.
- 찾는 값은 범위의 첫 번째 열에!
- 검색 범위(
table_array)는F4키로 절대 참조($) 고정! - 검색 방식(
range_lookup)은 고민 없이FALSE!