=VLOOKUP(F2,A2:D6,3,FALSE)는 A2:D6의 첫 열에서 F2의 값을 찾아 같은 행의 세 번째 열 값을 반환합니다. 끝의 FALSE는 "정확히 일치하는 값만"이라는 뜻입니다. F2에서 다른 상품을 고르면 가격이 바뀝니다.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
G2를 클릭하면 표 A2:D6이 테두리로 표시됩니다. 수식의 3을 2로 바꾸면 G2는 가격 대신 분류를 반환합니다. Category가 표의 두 번째 열이기 때문입니다. 일치는 대소문자를 무시하므로 pear도 Pear를 찾습니다.
VLOOKUP 구문
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| 인수 | 의미 | 예제에서 |
|---|---|---|
lookup_value | 찾을 값입니다. | F2 (Pear) |
table_array | 검색할 표입니다. VLOOKUP은 표의 첫 열에서만 찾습니다. | A2:D6 |
col_index_num | 반환할 표의 열 번호로, 표의 첫 열(1)부터 셉니다. | 3 (Price) |
range_lookup | 정확히 일치는 FALSE 또는 0, 유사 일치는 TRUE, 1 또는 생략입니다. | FALSE |
열 번호는 시트의 A열이 아니라 표의 시작부터 셉니다. C열에서 시작하는 표에서 col_index_num 2는 D열을 뜻합니다. 표의 너비보다 큰 번호는 #REF!를, 0은 #VALUE!를 반환합니다.
소수점으로 쉼표를 쓰는 언어로 설정된 엑셀에서는 인수를 세미콜론으로 구분합니다: =VLOOKUP(F2;A2:D6;3;FALSE).
MATCH로 반환할 열 고르기
숫자 3을 직접 써 두면 누군가 표 안에 열을 삽입했을 때 조용히 망가집니다. 수식은 계속 세 번째 열을 반환하는데, 그 열에는 이제 다른 값이 들어 있습니다. 대신 MATCH가 머리글에서 열 번호를 찾게 하세요. 여기서 G1은 드롭다운입니다. Stock이나 Category를 고르면 G2가 따라 바뀝니다.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Carrot | 0.8 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
MATCH(G1,A1:D1,0)은 머리글 행에서 "Price"의 위치인 3을 반환하고, VLOOKUP이 이것을 열 번호로 써서 Carrot의 0.8을 냅니다. 이것이 양방향 조회입니다. 행은 상품으로, 열은 머리글로 고릅니다. 같은 아이디어를 VLOOKUP 대신 INDEX로 쓴 방법은 INDEX와 MATCH 페이지에 있습니다.
유사 일치: TRUE를 쓰는 VLOOKUP
마지막 인수가 TRUE이면 VLOOKUP은 같은 값을 찾지 않습니다. 찾는 값보다 작거나 같은 값 중 가장 큰 값을 찾습니다. 세율 구간, 등급, 배송비, 수수료 단계 같은 구간에 필요한 동작입니다. 첫 열은 작은 값부터 큰 값 순으로 정렬되어 있어야 합니다.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sales from | Rate | Rep | Sales | Rate | |
| 2 | 0 | 0% | Ana | 750 | 0% | |
| 3 | 1000 | 3% | Ben | 4,200 | 3% | |
| 4 | 5000 | 5% | Cara | 5,000 | 5% | |
| 5 | 10000 | 8% | Dev | 12,500 | 8% |
Ben의 4,200은 A열에 없습니다. 그보다 크지 않은 가장 큰 값은 1,000이므로 3%를 받습니다. Cara의 5,000은 5,000 행과 정확히 일치해 5%를 받습니다. Dev의 12,500은 마지막 구간보다 크므로 마지막 비율인 8%를 받습니다. 첫 구간보다 작은 값(여기서는 음수 매출)은 #N/A를 반환하므로 표가 0에서 시작합니다.
$A$2:$B$5의 $ 기호는 F2를 F5까지 아래로 채울 때 표를 제자리에 고정합니다. 이 기호가 없으면 F3은 A3:B6을 검색해 첫 구간을 건너뜁니다.
네 번째 인수를 생략하면 TRUE와 같습니다. 정렬되지 않은 상품 목록에서는 이것이 눈에 띄지 않는 버그가 됩니다. 엑셀이 목록이 정렬된 것처럼 검색해 다른 행의 가격을 반환하거나, 있는 값에 #N/A를 반환할 수 있습니다. 이름, 코드, ID를 조회할 때는 항상 FALSE로 끝내세요.
VLOOKUP이 #N/A를 반환하는 이유
#N/A는 "찾을 수 없음"을 뜻합니다. 아래 시트는 흔한 원인 세 가지를 보여 주며, G열은 각 조회를 IFNA와 TRIM으로 감싸 반복합니다.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | Fixed |
| 2 | Apple | Fruit | $1.20 | 40 | Kiwi | #N/A | Not found |
| 3 | Pear | Fruit | $1.50 | 25 | Milk | #N/A | $1.10 |
| 4 | Carrot | Vegetable | $0.80 | 60 | Fruit | #N/A | Not found |
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
#N/A 찾는 값이 조회 범위에 없습니다.- 값이 표에 없습니다. Kiwi는 A2:A6에 없습니다. 진짜 "찾을 수 없음"이며,
IFNA(...,"Not found")가 이것을 읽을 수 있는 텍스트로 바꿉니다. E2를 Apple로 바꾸면 두 열 모두 가격을 보여 줍니다. - 남는 공백. E3에는 뒤에 공백이 붙은
"Milk "가 들어 있어Milk와 같지 않습니다.TRIM(E3)이 공백을 지워 G3이 가격을 찾습니다. 공백이 표 쪽에 있다면 조회할 때마다 처리하지 말고 A열을 TRIM으로 한 번 정리하세요. - 값이 다른 열에 있습니다. Fruit는 있지만 B열에 있습니다. VLOOKUP은 표의 첫 열에서만 검색하므로 E4는 두 열 모두에서 실패합니다. 검색할 열에서 표를 시작하거나, 검색 열과 반환 열을 따로 받는 XLOOKUP을 쓰세요.
조회 수식은 IFERROR가 아니라 IFNA로 감싸세요. IFNA는 #N/A만 잡으므로, 잘못된 열 번호에서 생긴 #REF!는 "Not found"로 숨겨지지 않고 그대로 드러납니다.
원인이 두 가지 더 있습니다:
- 텍스트로 저장된 숫자. A열에 상품 코드가 텍스트로 들어 있고(주로 가져온 데이터에서 생기며 모서리에 작은 초록색 삼각형이 있음) F2에 숫자 101이 있으면, 목록에 101이 있는데도
=VLOOKUP(F2,A2:B6,2,FALSE)는 #N/A를 반환합니다. 한쪽을 변환하세요.=VLOOKUP(F2&"",A2:B6,2,FALSE)는 텍스트 "101"을 검색하고, F2가 텍스트일 때=VLOOKUP(VALUE(F2),A2:B6,2,FALSE)는 숫자를 검색합니다. - 정렬되지 않은 데이터에서 유사 일치, 위 섹션에서 설명한 경우입니다.
VLOOKUP이 빈칸 대신 0을 반환할 때
VLOOKUP이 찾아간 셀이 비어 있으면 엑셀은 빈 셀이 아니라 0을 보여 줍니다. 그러면 Stock 열의 0은 재고가 입력된 적이 없는데도 "품절"로 읽힙니다. 수식에 &""를 붙이거나 결과의 길이를 검사하세요:
=VLOOKUP(F2,A2:D6,4,FALSE)&""
=IF(LEN(VLOOKUP(F2,A2:D6,4,FALSE))=0,"",VLOOKUP(F2,A2:D6,4,FALSE))
첫 번째가 더 짧지만 반환하는 모든 숫자를 텍스트로 바꾸므로 나중에 SUM이 건너뜁니다. 두 번째는 숫자를 숫자로 유지합니다.
다른 시트에서 VLOOKUP
표 앞에 시트 이름과 !를 씁니다. 엑셀에서 수식을 만들 때 다른 시트의 탭을 클릭하고 범위를 선택하면 엑셀이 Prices!A2:B6을 대신 써 줍니다. 여기서는 Orders 탭이 Prices 탭에서 가격을 조회합니다.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Product | Qty | Price | Total |
| 2 | 1001 | Pear | 3 | $1.50 | $4.50 |
| 3 | 1002 | Milk | 2 | $1.10 | $2.20 |
| 4 | 1003 | Apple | 5 | $1.20 | $6.00 |
| 5 | 1004 | Bread | 1 | $2.40 | $2.40 |
Prices 탭을 열고 Apple의 가격을 바꾸면 주문 합계가 바뀝니다. 두 가지를 알아 두세요:
- 공백이 있는 시트 이름은 작은따옴표가 필요합니다:
=VLOOKUP(B2,'Price list'!$A$2:$B$6,2,FALSE). - 다른 통합 문서에 있는 표는 대괄호 안에 파일 이름을 붙입니다:
[Prices.xlsx]Prices!$A$2:$B$6. 그 파일이 닫혀 있으면 엑셀은 수식에 전체 경로를 표시하고, 저장된 파일을 기준으로 조회를 계속합니다.
와일드카드를 쓰는 VLOOKUP(부분 일치)
FALSE를 쓰면 찾는 값에 와일드카드를 넣을 수 있습니다. *는 글자 수와 상관없이 아무 문자를, ?는 정확히 한 글자를 뜻합니다. "*"&E2&"*"는 이름에 E2의 텍스트가 들어 있는 첫 번째 상품을 찾습니다.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Contains | Price | |
| 2 | Green tea | Drinks | $3.20 | coffee | $2.90 | |
| 3 | Iced coffee | Drinks | $2.90 | |||
| 4 | Coffee beans | Pantry | $8.50 | |||
| 5 | Black tea | Drinks | $2.70 | |||
| 6 | Orange juice | Drinks | $3.40 |
"coffee"는 Iced coffee와 Coffee beans 모두와 일치하며, VLOOKUP은 위에서부터 첫 번째 것인 $2.90을 반환합니다. E2를 bean으로 바꾸면 $8.50이 나오고, juice로 바꿔 볼 수도 있습니다. 실제 별표나 물음표를 검색하려면 앞에 물결표를 붙이세요: "~*".
VLOOKUP으로 왼쪽 열 가져오기
VLOOKUP은 검색하는 열보다 왼쪽에 있는 열을 반환할 수 없습니다. col_index_num은 오른쪽으로만 세고, 음수는 오류입니다. 주어진 가격의 상품을 찾으려면 XLOOKUP이나 INDEX와 MATCH로 C열을 검색하고 A열을 반환하세요:
=XLOOKUP(2.4, C2:C6, A2:A6) Excel 2021 and Microsoft 365
=INDEX(A2:A6, MATCH(2.4, C2:C6, 0)) every version
첫 번째 시트의 데이터에서 둘 다 Bread를 반환합니다. 자세한 설명은 XLOOKUP에 있습니다.
연습: 무게별 배송비
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Weight from (kg) | Cost | Weight (kg) | Cost | |
| 2 | 0 | $4.50 | 7 | ||
| 3 | 2 | $6.00 | |||
| 4 | 5 | $9.50 | |||
| 5 | 10 | $14.00 | |||
| 6 | 20 | $22.00 |
직접 해 보세요: 각 요금은 해당 무게부터 목록의 다음 무게 전까지 적용됩니다. E2에서 VLOOKUP으로 D2에 있는 소포 무게의 배송비를 반환하세요.
조건이 두 개인 VLOOKUP
VLOOKUP은 찾는 값을 하나만 받습니다. 두 열을 기준으로 찾으려면 두 열을 합친 도우미 열을 만들어 표의 맨 앞에 두고, 똑같이 합친 텍스트를 조회합니다. 아래 A열은 =B2&"-"&C2를 아래로 채운 것이므로 Coffee-Small, Coffee-Large 같은 값이 들어 있습니다.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Key | Product | Size | Price | Product | Size | Price |
| 2 | Coffee-Small | Coffee | Small | $2.50 | Tea | Large | |
| 3 | Coffee-Large | Coffee | Large | $3.50 | |||
| 4 | Tea-Small | Tea | Small | $2.00 | |||
| 5 | Tea-Large | Tea | Large | $3.00 | |||
| 6 | Juice-Small | Juice | Small | $3.00 |
직접 해 보세요: A열은 상품과 크기를 대시로 합친 것입니다. G2에서 E2의 상품과 F2의 크기에 맞는 가격을 반환하세요.
구분 기호가 중요합니다. "Tea"&"Large"는 TeaLarge가 되어 A열의 어떤 값과도 일치하지 않습니다. Excel 2021과 Microsoft 365에서는 =XLOOKUP(1,(B2:B6=E2)*(C2:C6=F2),D2:D6)으로 도우미 열 없이 할 수 있습니다. 그 방법과 INDEX/MATCH 버전은 여러 조건으로 조회하기에서 보여 줍니다.
자주 묻는 질문
엑셀에서 VLOOKUP은 어떻게 쓰나요?
=VLOOKUP(을 입력하고 인수 네 개를 넣습니다. 찾을 값, 표(첫 열에 그 값이 있어야 함), 반환할 열의 번호, 정확히 일치를 뜻하는 FALSE입니다. =VLOOKUP("Pear",A2:D6,3,FALSE)는 A열에서 Pear를 찾아 그 행의 C열 값을 반환합니다.
VLOOKUP 끝의 TRUE나 FALSE는 무슨 뜻인가요?
FALSE(또는 0)는 정확히 일치를 요청하며 값이 없으면 #N/A를 반환합니다. TRUE(또는 1, 또는 인수 생략)는 유사 일치를 요청합니다. 찾는 값보다 작거나 같은 값 중 가장 큰 값을 찾으며, 첫 열이 오름차순으로 정렬되어 있을 때만 제대로 동작합니다.
VLOOKUP이 #N/A를 반환하는 이유는 무엇인가요?
표의 첫 열에서 값을 찾지 못했기 때문입니다. 흔한 원인은 오타, 남는 공백("Milk "는 "Milk"가 아님), 한쪽에만 텍스트로 저장된 숫자, 다른 열에 있는 값입니다. 원하는 텍스트를 보여 주려면 수식을 IFNA로 감싸세요: =IFNA(VLOOKUP(F2,A2:D6,3,FALSE),"Not found").
VLOOKUP으로 왼쪽 열을 가져올 수 있나요?
없습니다. VLOOKUP은 표의 첫 열보다 오른쪽에 있는 열만 반환합니다. Excel 2021이나 Microsoft 365에서는 =XLOOKUP(F2,C2:C6,A2:A6)을, 모든 버전에서는 =INDEX(A2:A6,MATCH(F2,C2:C6,0))을 쓰세요.
다른 시트에서 VLOOKUP하려면 어떻게 하나요?
범위 앞에 시트 이름과 느낌표를 붙입니다: =VLOOKUP(B2,Prices!$A$2:$B$6,2,FALSE). 시트 이름에 공백이 있으면 작은따옴표로 감싸세요: 'Price list'!$A$2:$B$6.
