=VLOOKUP(F2,A2:D6,3,FALSE) tìm giá trị trong F2 ở cột đầu tiên của A2:D6 và trả về giá trị ở cột thứ ba của cùng hàng. FALSE ở cuối nghĩa là "chỉ so khớp chính xác". Chọn một sản phẩm khác trong F2 và giá sẽ thay đổi.
| 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 |
Bấm vào G2 để thấy bảng A2:D6 được tô viền. Đổi 3 trong công thức thành 2 thì G2 trả về danh mục thay vì giá, vì Category là cột thứ hai của bảng. Phép so khớp bỏ qua chữ hoa chữ thường: pear vẫn tìm thấy Pear.
Cú pháp VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Đối số | Ý nghĩa | Trong ví dụ |
|---|---|---|
lookup_value | Giá trị cần tìm. | F2 (Pear) |
table_array | Bảng để tìm kiếm. VLOOKUP chỉ tìm trong cột đầu tiên của bảng. | A2:D6 |
col_index_num | Cột nào của bảng được trả về, đếm từ cột đầu tiên của bảng (1). | 3 (Price) |
range_lookup | FALSE hoặc 0 để so khớp chính xác. TRUE, 1 hoặc bỏ trống để so khớp gần đúng. | FALSE |
Số thứ tự cột được đếm từ đầu bảng, không phải từ cột A của trang tính. Với bảng bắt đầu ở cột C, col_index_num bằng 2 nghĩa là cột D. Một số lớn hơn độ rộng của bảng sẽ trả về #REF!, còn 0 trả về #VALUE!.
Trong Excel đặt ngôn ngữ dùng dấu phẩy thập phân, các đối số được ngăn cách bằng dấu chấm phẩy: =VLOOKUP(F2;A2:D6;3;FALSE).
Chọn cột trả về bằng MATCH
Số 3 viết cứng sẽ hỏng một cách âm thầm khi ai đó chèn một cột vào giữa bảng: công thức vẫn trả về cột thứ ba, giờ lại chứa thứ khác. Thay vào đó, hãy để MATCH tìm số thứ tự cột từ tiêu đề. Ở đây G1 là danh sách thả xuống: chọn Stock hoặc Category và G2 thay đổi theo.
| 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) trả về vị trí của "Price" trong hàng tiêu đề, là 3, và VLOOKUP dùng nó làm số thứ tự cột: 0,8 cho Carrot. Đây là tra cứu hai chiều: hàng được chọn theo sản phẩm, cột được chọn theo tiêu đề. Cùng ý tưởng này viết bằng INDEX thay cho VLOOKUP có ở trang INDEX và MATCH.
So khớp gần đúng: VLOOKUP với TRUE
Khi đối số cuối là TRUE, VLOOKUP không tìm giá trị bằng nhau. Hàm tìm giá trị lớn nhất nhỏ hơn hoặc bằng giá trị cần tìm. Đó chính là điều bạn cần cho các bậc: bậc thuế, xếp loại điểm, phí vận chuyển, mức hoa hồng. Cột đầu tiên phải được sắp xếp từ nhỏ đến lớn.
| 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% |
Con số 4.200 của Ben không có trong cột A. Giá trị lớn nhất không vượt quá nó là 1.000, nên Ben nhận 3%. Con số 5.000 của Cara khớp đúng hàng 5.000 và nhận 5%. Con số 12.500 của Dev vượt bậc cuối cùng và nhận tỷ lệ cuối, 8%. Một giá trị nhỏ hơn bậc đầu tiên (ở đây là doanh số âm) trả về #N/A, vì thế bảng bắt đầu từ 0.
Các dấu $ trong $A$2:$B$5 giữ bảng cố định khi F2 được điền xuống tới F5. Không có chúng, F3 sẽ tìm trong A3:B6 và bỏ qua bậc đầu tiên.
Bỏ trống đối số thứ tư cũng giống như TRUE. Với danh sách sản phẩm chưa sắp xếp, đó là một bug âm thầm: Excel tìm như thể danh sách đã được sắp xếp và có thể trả về giá của sai hàng, hoặc #N/A cho một giá trị có trong danh sách. Khi tra cứu tên, mã hay ID, hãy luôn kết thúc bằng FALSE.
Vì sao VLOOKUP trả về #N/A
#N/A nghĩa là "không tìm thấy". Trang tính dưới đây cho thấy ba nguyên nhân thường gặp, và cột G lặp lại từng phép tra cứu được bọc trong IFNA và 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 Giá trị bạn tìm không có trong vùng tra cứu.- Giá trị không có trong bảng. Kiwi không có trong A2:A6. Đó là trường hợp "không tìm thấy" thật sự, và
IFNA(...,"Not found")biến nó thành văn bản dễ đọc. Đổi E2 thành Apple thì cả hai cột đều hiện giá. - Khoảng trắng thừa. E3 chứa
"Milk "có khoảng trắng ở cuối, nên nó không bằngMilk.TRIM(E3)xóa khoảng trắng đó và G3 tìm được giá. Nếu khoảng trắng nằm trong bảng, hãy làm sạch cột A bằng TRIM một lần thay vì làm trong mọi phép tra cứu. - Giá trị nằm ở cột khác. Fruit có tồn tại, nhưng ở cột B. VLOOKUP chỉ tìm trong cột đầu tiên của bảng, nên E4 lỗi ở cả hai cột. Hãy cho bảng bắt đầu từ cột bạn tìm kiếm, hoặc dùng XLOOKUP, hàm nhận cột tìm kiếm và cột trả về riêng biệt.
Hãy dùng IFNA thay vì IFERROR để bọc phép tra cứu. IFNA chỉ bắt lỗi #N/A, nên lỗi #REF! do sai số thứ tự cột vẫn hiện ra thay vì bị che thành "Not found".
Thêm hai nguyên nhân nữa:
- Số được lưu dạng văn bản. Nếu cột A chứa mã sản phẩm gõ dưới dạng văn bản (thường gặp sau khi nhập dữ liệu, có hình tam giác nhỏ màu xanh ở góc ô) và F2 chứa số 101,
=VLOOKUP(F2,A2:B6,2,FALSE)trả về #N/A dù 101 có trong danh sách. Hãy chuyển đổi một bên:=VLOOKUP(F2&"",A2:B6,2,FALSE)tìm văn bản "101", còn=VLOOKUP(VALUE(F2),A2:B6,2,FALSE)tìm một số khi F2 là văn bản. - So khớp gần đúng trên dữ liệu chưa sắp xếp, như đã nói ở phần trên.
VLOOKUP trả về 0 thay vì ô trống
Khi ô mà VLOOKUP trỏ tới đang trống, Excel hiện 0 chứ không hiện ô trống. Khi đó số 0 trong cột Stock bị hiểu là "hết hàng" dù số tồn kho chưa từng được nhập. Hãy thêm &"" vào công thức, hoặc kiểm tra độ dài của kết quả:
=VLOOKUP(F2,A2:D6,4,FALSE)&""
=IF(LEN(VLOOKUP(F2,A2:D6,4,FALSE))=0,"",VLOOKUP(F2,A2:D6,4,FALSE))
Cách đầu ngắn hơn nhưng biến mọi số trả về thành văn bản, nên SUM phía sau sẽ bỏ qua chúng. Cách thứ hai giữ số ở dạng số.
VLOOKUP lấy dữ liệu từ sheet khác
Viết tên sheet và ! trước bảng. Khi dựng công thức trong Excel, bấm vào thẻ của sheet kia rồi chọn vùng: Excel tự viết Prices!A2:B6 cho bạn. Ở đây thẻ Orders tra cứu giá trên thẻ 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 |
Mở thẻ Prices và đổi giá của Apple: tổng tiền của đơn hàng cập nhật theo. Có hai chi tiết:
- Tên sheet có khoảng trắng cần đặt trong dấu nháy đơn:
=VLOOKUP(B2,'Price list'!$A$2:$B$6,2,FALSE). - Bảng nằm ở workbook khác sẽ có thêm tên tệp trong ngoặc vuông,
[Prices.xlsx]Prices!$A$2:$B$6. Khi tệp đó đóng, Excel hiện đường dẫn đầy đủ của nó trong công thức và phép tra cứu vẫn chạy dựa trên tệp đã lưu.
VLOOKUP với ký tự đại diện (khớp một phần)
Với FALSE, giá trị cần tìm có thể chứa ký tự đại diện (wildcard): * thay cho số ký tự bất kỳ và ? thay cho đúng một ký tự. "*"&E2&"*" tìm sản phẩm đầu tiên có tên chứa văn bản trong 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" khớp với cả Iced coffee và Coffee beans; VLOOKUP trả về kết quả đầu tiên tính từ trên xuống, 8,50, hoặc thành juice. Để tìm đúng dấu sao hay dấu hỏi, đặt dấu ngã trước nó: "~*".
VLOOKUP sang trái
VLOOKUP không thể trả về một cột nằm bên trái cột nó tìm kiếm: col_index_num chỉ đếm sang phải, và số âm là lỗi. Để tìm sản phẩm theo một mức giá, hãy tìm trong cột C và trả về cột A bằng XLOOKUP hoặc INDEX và MATCH:
=XLOOKUP(2.4, C2:C6, A2:A6) Excel 2021 and Microsoft 365
=INDEX(A2:A6, MATCH(2.4, C2:C6, 0)) every version
Cả hai đều trả về Bread với dữ liệu ở trang tính đầu tiên. XLOOKUP có phần giải thích đầy đủ.
Luyện tập: phí vận chuyển theo cân nặng
| 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 |
Đến lượt bạn: Mỗi mức phí áp dụng từ cân nặng của nó tới cân nặng kế tiếp trong danh sách. Trong E2, dùng VLOOKUP để trả về phí vận chuyển cho cân nặng kiện hàng trong D2.
VLOOKUP với hai điều kiện
VLOOKUP chỉ nhận một giá trị cần tìm. Để so khớp trên hai cột, hãy dựng một cột phụ nối hai cột lại, đặt nó làm cột đầu tiên của bảng, rồi tra cứu đúng chuỗi đã nối đó. Cột A bên dưới là =B2&"-"&C2 được điền xuống, nên nó chứa Coffee-Small, Coffee-Large và cứ thế tiếp tục.
| 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 |
Đến lượt bạn: Cột A nối sản phẩm và kích cỡ bằng dấu gạch nối. Trong G2, trả về giá cho sản phẩm trong E2 và kích cỡ trong F2.
Ký tự ngăn cách rất quan trọng: "Tea"&"Large" cho ra TeaLarge, không khớp với gì trong cột A. Trong Excel 2021 và Microsoft 365, bạn có thể bỏ cột phụ bằng =XLOOKUP(1,(B2:B6=E2)*(C2:C6=F2),D2:D6); trang tra cứu nhiều điều kiện trình bày cách đó cùng phiên bản INDEX/MATCH.
Câu hỏi thường gặp
Cách dùng hàm VLOOKUP trong Excel?
Gõ =VLOOKUP( và đưa vào bốn đối số: giá trị cần tìm, bảng dữ liệu (cột đầu tiên của bảng phải chứa giá trị đó), số thứ tự cột cần trả về, và FALSE để so khớp chính xác. =VLOOKUP("Pear",A2:D6,3,FALSE) tìm Pear trong cột A và trả về giá trị ở cột C của hàng đó.
TRUE hoặc FALSE ở cuối hàm VLOOKUP có nghĩa là gì?
FALSE (hoặc 0) yêu cầu so khớp chính xác và trả về #N/A khi không có giá trị đó. TRUE (hoặc 1, hoặc bỏ trống đối số) yêu cầu so khớp gần đúng: giá trị lớn nhất nhỏ hơn hoặc bằng giá trị cần tìm, cách này chỉ đúng khi cột đầu tiên được sắp xếp tăng dần.
Vì sao hàm VLOOKUP trả về #N/A?
Không tìm thấy giá trị trong cột đầu tiên của bảng. Nguyên nhân thường gặp là gõ sai, có khoảng trắng thừa ("Milk " không phải "Milk"), số được lưu dạng văn bản ở một bên, hoặc giá trị nằm ở cột khác. Bọc công thức trong IFNA để hiện văn bản của riêng bạn: =IFNA(VLOOKUP(F2,A2:D6,3,FALSE),"Not found").
VLOOKUP có tra cứu sang trái được không?
Không. VLOOKUP chỉ trả về các cột nằm bên phải cột đầu tiên của bảng. Hãy dùng =XLOOKUP(F2,C2:C6,A2:A6) trong Excel 2021 hoặc Microsoft 365, hoặc =INDEX(A2:A6,MATCH(F2,C2:C6,0)) trong mọi phiên bản.
Làm sao dùng VLOOKUP lấy dữ liệu từ sheet khác?
Đặt tên sheet và dấu chấm than trước vùng dữ liệu: =VLOOKUP(B2,Prices!$A$2:$B$6,2,FALSE). Nếu tên sheet có khoảng trắng, đặt nó trong dấu nháy đơn: 'Price list'!$A$2:$B$6.
