Hàm VLOOKUP trong Excel dùng để tìm một giá trị ở cột đầu tiên của một bảng rồi trả về dữ liệu nằm cùng dòng ở cột bạn chỉ định — ví dụ gõ mã hàng "SP03" là tự hiện ra đơn giá 38.000 đồng. Đây là hàm tra cứu được hỏi nhiều nhất trong bài test Excel khi phỏng vấn văn phòng, kế toán, kho và bán hàng. Bài này đi từ cú pháp, ví dụ tra giá có bảng dữ liệu mẫu, cách chọn dò đúng hay gần đúng, tới cách sửa các lỗi #N/A, #REF!, #VALUE! và khi nào nên chuyển sang INDEX/MATCH hoặc XLOOKUP.
Học xong bài này, bạn sẽ làm được:
- Viết đúng công thức VLOOKUP 4 đối số và giải thích từng đối số.
- Tra đơn giá theo mã hàng cho cả bảng đơn hàng, kéo công thức xuống mà không lệch vùng nhờ ký hiệu $.
- Chọn đúng kiểu dò: FALSE/0 cho mã hàng, TRUE cho bảng xếp loại theo ngưỡng điểm.
- Tìm và sửa nguyên nhân của #N/A, #REF!, #VALUE!, kể cả lỗi khoảng trắng thừa và số lưu dạng text.
- Viết lại cùng phép tra bằng INDEX/MATCH và XLOOKUP khi cần dò sang trái.
Học tin học văn phòng theo đúng thứ tự? VLOOKUP thuộc bước 4 – tra cứu và làm sạch dữ liệu. Xem lộ trình 8 bước học tin học văn phòng: từ quản lý tệp, Word chuẩn, Excel nền tảng tới PivotTable, dashboard và thi chứng chỉ.
VLOOKUP là gì và dùng khi nào?
Chữ V trong VLOOKUP là "vertical" — tra theo chiều dọc. Hàm đi từ trên xuống dưới ở cột đầu tiên của vùng bảng, dừng ở dòng có giá trị cần tìm, rồi lấy dữ liệu ở cột thứ N của dòng đó. Bạn dùng nó mỗi khi có một "bảng gốc" (bảng giá, danh sách nhân viên, danh mục khách hàng) và một bảng khác chỉ có mã, cần điền thêm thông tin.
Ảnh: Modjou / Wikimedia Commons, CC BY-SA 4.0
Tình huống dùng VLOOKUP hằng ngày
- Cửa hàng tạp hóa: nhập mã hàng trên phiếu bán, tự hiện tên hàng và đơn giá.
- Phòng nhân sự: có mã nhân viên trong bảng chấm công, cần lấy mức lương từ hồ sơ.
- Kho: đối chiếu danh sách mã nhập kho với danh mục, tìm mã chưa khai báo.
- Giáo viên: đổi điểm trung bình thành xếp loại theo ngưỡng.
Hai điều kiện để VLOOKUP chạy đúng
Thứ nhất, giá trị cần tìm phải nằm ở cột đầu tiên của vùng bạn chọn; VLOOKUP chỉ lấy dữ liệu ở các cột bên phải cột đó. Thứ hai, giá trị cần tìm và giá trị trong bảng phải giống hệt nhau cả về kiểu dữ liệu: "SP03" có thêm dấu cách ở cuối, hay số 1001 lưu dạng chữ, đều bị coi là khác. Hai điều này giải thích phần lớn lỗi ở cuối bài.
Cú pháp hàm VLOOKUP
Công thức đầy đủ: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Trong Excel tiếng Việt, tên hàm và thứ tự đối số vẫn giữ nguyên tiếng Anh.
| Đối số | Ý nghĩa | Ví dụ |
|---|---|---|
| lookup_value | Giá trị cần tìm (thường là ô chứa mã) | F2 chứa "SP03" |
| table_array | Vùng bảng gốc, cột đầu tiên chứa mã | $A$2:$D$7 |
| col_index_num | Số thứ tự cột cần lấy, đếm từ cột đầu của vùng (cột đầu = 1) | 4 = cột D – Đơn giá |
| range_lookup | FALSE hoặc 0: dò đúng; TRUE hoặc 1: dò gần đúng. Bỏ trống = TRUE | FALSE |
Dấu phân cách: dấu phẩy hay chấm phẩy?
Bài này viết công thức với dấu phẩy ,. Nếu máy bạn cài định dạng vùng Việt Nam (dấu thập phân là dấu phẩy), Excel sẽ dùng dấu chấm phẩy ; để ngăn các đối số: =VLOOKUP(F2;$A$2:$D$7;4;FALSE). Gõ sai dấu, Excel báo "There's a problem with this formula". Cách nhận biết nhanh: gõ =VLOOKUP( rồi xem dòng gợi ý hiện ra dùng dấu nào. Google Sheets cũng đổi dấu theo cài đặt vùng của bảng tính.
Đừng bỏ trống đối số thứ tư
Khi bỏ trống range_lookup, Excel hiểu là TRUE (dò gần đúng). Với bảng mã hàng không sắp xếp, công thức vẫn trả về một con số trông rất hợp lý nhưng là giá của mặt hàng khác — sai mà không báo lỗi. Quy tắc cho người mới: luôn gõ FALSE, chỉ dùng TRUE khi bạn cố ý tra theo ngưỡng.
Ví dụ làm mẫu: tra đơn giá theo mã hàng
Một cửa hàng tạp hóa có bảng giá ở vùng A1:D7 (Hình 2 minh họa người đang nhập dữ liệu vào bảng tính như vậy). Bảng đơn hàng nằm ở cột F đến I. Bạn cần điền đơn giá vào cột H và tính thành tiền ở cột I.
| Dòng | A – Mã hàng | B – Tên hàng | C – Đơn vị | D – Đơn giá (đồng) |
|---|---|---|---|---|
| 2 | SP01 | Gạo túi 5 kg | túi | 185.000 |
| 3 | SP02 | Dầu ăn 1 lít | chai | 52.000 |
| 4 | SP03 | Nước mắm 500 ml | chai | 38.000 |
| 5 | SP04 | Đường trắng 1 kg | gói | 27.000 |
| 6 | SP05 | Mì gói (thùng 30 gói) | thùng | 115.000 |
| 7 | SP06 | Sữa tươi 1 lít | hộp | 34.000 |
Ảnh: Mark Diamond / Wikimedia Commons, Public domain
Các bước làm
- Bấm ô H2, gõ
=VLOOKUP(F2,$A$2:$D$7,4,FALSE)rồi Enter. F2 là "SP03" nên kết quả là 38.000. - Bấm ô I2, gõ
=G2*H2. - Chọn H2:I2, kéo nút điền (ô vuông nhỏ góc dưới phải) xuống dòng 5.
- Ở I6, gõ
=SUM(I2:I5)để ra tổng đơn hàng.
| Dòng | F – Mã hàng | G – Số lượng | H – Đơn giá (VLOOKUP) | I – Thành tiền |
|---|---|---|---|---|
| 2 | SP03 | 4 | 38.000 | 152.000 |
| 3 | SP01 | 2 | 185.000 | 370.000 |
| 4 | SP05 | 1 | 115.000 | 115.000 |
| 5 | SP06 | 6 | 34.000 | 204.000 |
| 6 | Tổng | 841.000 |
Kiểm tra bằng tay: 4 × 38.000 = 152.000; 2 × 185.000 = 370.000; 1 × 115.000 = 115.000; 6 × 34.000 = 204.000. Cộng lại 152.000 + 370.000 + 115.000 + 204.000 = 841.000 đồng, khớp ô I6.
Vì sao phải có dấu $ trong vùng bảng?
Nếu gõ A2:D7 không có $, khi kéo công thức xuống dòng 3, Excel tự đổi thành A3:D8, dòng 4 thành A4:D9… Vùng trượt dần xuống, bỏ mất SP01 ở trên, nên các dòng dưới báo #N/A dù mã có trong bảng. Dấu $ "khóa" cột và dòng: $A$2:$D$7 giữ nguyên khi sao chép. Mẹo: bôi đen vùng trong thanh công thức rồi bấm F4 để thêm $ nhanh. Còn F2 thì để tương đối, vì mỗi dòng phải tra mã của chính dòng đó.
Lấy cột khác chỉ cần đổi col_index_num
Muốn lấy tên hàng: =VLOOKUP(F2,$A$2:$D$7,2,FALSE) → "Nước mắm 500 ml". Muốn lấy đơn vị: đổi số 2 thành 3 → "chai". Đếm cột luôn tính từ cột đầu của vùng, không phải từ cột A của trang tính: nếu vùng là $B$2:$D$7 thì cột D là cột thứ 3.
Dò đúng (FALSE) và dò gần đúng (TRUE)
Dò đúng tìm giá trị trùng khớp hoàn toàn; không có thì trả #N/A. Dò gần đúng tìm giá trị lớn nhất nhưng không vượt quá giá trị cần tìm, và yêu cầu cột đầu của bảng sắp xếp tăng dần. Nghe khó hiểu, nhưng đó chính là cách tra theo ngưỡng: xếp loại học lực, bậc hoa hồng, mức phí vận chuyển theo cân nặng.
Ví dụ bảng xếp loại theo điểm
| Dòng | K – Điểm từ | L – Xếp loại |
|---|---|---|
| 2 | 0 | Yếu |
| 3 | 5 | Trung bình |
| 4 | 6,5 | Khá |
| 5 | 8 | Giỏi |
| 6 | 9 | Xuất sắc |
Công thức =VLOOKUP(N2,$K$2:$L$6,2,TRUE) với N2 là điểm của học sinh:
- Điểm 7,2 → ngưỡng lớn nhất không vượt 7,2 là 6,5 → Khá.
- Điểm 8 → trùng đúng ngưỡng 8 → Giỏi.
- Điểm 4,5 → chỉ có ngưỡng 0 không vượt → Yếu.
- Điểm 9,5 → ngưỡng 9 → Xuất sắc.
Khi nào dò gần đúng cho kết quả sai?
Nếu cột K không sắp xếp tăng dần, Excel vẫn trả kết quả nhưng có thể sai mà không báo lỗi. Nếu điểm nhỏ hơn ngưỡng đầu tiên (ví dụ bảng bắt đầu từ 1 mà điểm là 0,5), kết quả là #N/A. Vì vậy bảng ngưỡng luôn bắt đầu từ giá trị nhỏ nhất có thể (ở đây là 0). Lưu ý thêm: máy cài vùng Việt Nam gõ điểm là 6,5; máy vùng Mỹ gõ 6.5 — gõ sai kiểu, Excel hiểu thành chữ và phép dò hỏng.
Lỗi #N/A, #REF!, #VALUE! và cách sửa
Ba lỗi này chiếm gần hết các ca "VLOOKUP không chạy". Đọc mã lỗi trước khi sửa: mỗi mã chỉ về một nhóm nguyên nhân khác nhau.
| Lỗi | Nguyên nhân thường gặp | Cách sửa |
|---|---|---|
| #N/A | Không tìm thấy: gõ sai mã, khoảng trắng thừa, số lưu dạng text, vùng chưa khóa $ bị trượt, dò gần đúng với giá trị nhỏ hơn ngưỡng đầu | Kiểm tra mã, dùng TRIM, chuyển kiểu dữ liệu, thêm $ |
| #REF! | col_index_num lớn hơn số cột của vùng (gõ 5 khi vùng chỉ có A:D), hoặc vùng tham chiếu đã bị xóa | Đếm lại số cột, chọn lại vùng |
| #VALUE! | col_index_num nhỏ hơn 1 (gõ 0) hoặc không phải số | Sửa thành số cột từ 1 trở lên |
Khoảng trắng thừa: dùng TRIM
Dữ liệu dán từ phần mềm bán hàng, email hay web thường dính dấu cách ở cuối: "SP03 " nhìn giống hệt "SP03". Kiểm tra bằng =LEN(F2): mã 4 ký tự mà ra 5 là có ký tự thừa. Sửa bằng =VLOOKUP(TRIM(F2),$A$2:$D$7,4,FALSE). Nếu khoảng trắng nằm trong bảng gốc, tạo cột phụ =TRIM(A2), sao chép rồi dán giá trị đè lên cột mã. Dữ liệu từ web đôi khi chứa dấu cách không ngắt (mã 160) mà TRIM không xóa được; khi đó dùng =SUBSTITUTE(F2,CHAR(160),"").
Số lưu dạng text
Mã nhân viên 1001 trong bảng gốc là số, nhưng ở bảng chấm công lại là chữ "1001" (thường có tam giác xanh ở góc ô, căn trái). Hai bên không khớp nên ra #N/A. Cách sửa: nếu bảng gốc là số, tra bằng =VLOOKUP(VALUE(F2),…); nếu bảng gốc là chữ, tra bằng =VLOOKUP(F2&"",…). Cách triệt để là đồng bộ kiểu dữ liệu của cả cột bằng Data → Text to Columns → Finish.
Ảnh: Lha Charitable Trust / Wikimedia Commons, CC BY-SA 4.0
Ẩn lỗi gọn gàng bằng IFERROR
Khi mã không có trong bảng là chuyện bình thường (khách gõ mã mới), thay #N/A bằng lời nhắn dễ hiểu: =IFERROR(VLOOKUP(F2,$A$2:$D$7,4,FALSE),"Không có mã"). Cẩn thận: IFERROR che mọi lỗi, kể cả #REF! do bạn đếm sai cột. Chỉ bọc IFERROR khi công thức đã chạy đúng; Excel 2013 trở lên có IFNA chỉ bắt #N/A, an toàn hơn. Khi đang học, như các học viên ở Hình 3, đừng vội che lỗi — hãy đọc nó.
Giới hạn của VLOOKUP: INDEX/MATCH và XLOOKUP
VLOOKUP có ba điểm yếu: chỉ dò sang phải (không lấy được cột nằm bên trái cột mã), số cột gõ cứng nên chèn thêm cột vào bảng là lệch kết quả, và chỉ trả về dòng khớp đầu tiên khi mã bị trùng. Hai cách thay thế dưới đây khắc phục hai điểm đầu.
INDEX/MATCH: dò được cả sang trái
MATCH tìm vị trí dòng, INDEX lấy giá trị ở vị trí đó. Muốn biết mã của "Dầu ăn 1 lít" (cột mã nằm bên trái cột tên, VLOOKUP chịu):
=INDEX($A$2:$A$7,MATCH("Dầu ăn 1 lít",$B$2:$B$7,0)) → SP02.
Số 0 trong MATCH nghĩa là dò đúng, giống FALSE của VLOOKUP. Vì chỉ ra cột kết quả bằng vùng chứ không bằng số, chèn thêm cột vào bảng không làm lệch. Công thức này chạy trên mọi phiên bản Excel và Google Sheets.
XLOOKUP: gọn hơn, có sẵn giá trị khi không tìm thấy
=XLOOKUP(F2,$A$2:$A$7,$D$2:$D$7,"Không có mã") → 38.000 với SP03. XLOOKUP mặc định dò đúng, dò được hai chiều, có sẵn đối số "nếu không tìm thấy". Hàm có trong Excel cho Microsoft 365, Excel 2021 trở lên và Excel bản web; Google Sheets cũng có XLOOKUP. Excel 2019, 2016 trở về trước không có — mở file ở máy cũ sẽ báo lỗi #NAME?.
Nên học hàm nào trước?
| Tiêu chí | VLOOKUP | INDEX/MATCH | XLOOKUP |
|---|---|---|---|
| Dò sang trái | Không | Có | Có |
| Chèn cột không lệch | Không | Có | Có |
| Mặc định khi bỏ trống kiểu dò | Gần đúng (dễ sai) | MATCH mặc định gần đúng, nên gõ 0 | Dò đúng |
| Phiên bản Excel | Mọi bản | Mọi bản | Microsoft 365, 2021+, bản web; Google Sheets |
Lời khuyên thực tế: học VLOOKUP trước vì file cũ ở công ty và đề test tuyển dụng vẫn dùng nhiều nhất; học INDEX/MATCH để xử lý ca dò trái; dùng XLOOKUP khi chắc mọi người nhận file đều có Excel mới.
Câu hỏi thường gặp
VLOOKUP có phân biệt chữ hoa, chữ thường không?
Không. "sp03" và "SP03" được coi là giống nhau. Nếu cần phân biệt, phải dùng công thức kết hợp EXACT, ít gặp trong công việc văn phòng.
Có tra từ một sheet khác được không?
Được. Vùng bảng ghi kèm tên sheet: =VLOOKUP(F2,BangGia!$A$2:$D$7,4,FALSE). Tên sheet có dấu cách thì đặt trong nháy đơn: 'Bảng giá'!$A$2:$D$7.
Mã bị trùng trong bảng gốc thì sao?
VLOOKUP chỉ lấy dòng đầu tiên khớp, không báo gì. Trước khi tra, kiểm tra trùng bằng =COUNTIF($A$2:$A$7,A2): kết quả lớn hơn 1 là mã bị trùng, cần làm sạch bảng.
Bảng gốc thêm hàng mới, có phải sửa công thức không?
Nếu vùng cố định $A$2:$D$7 thì hàng thêm ở dòng 8 không được tra. Cách gọn là biến bảng gốc thành Table (Ctrl + T) rồi tham chiếu tên bảng; vùng sẽ tự mở rộng khi thêm dòng.
Bài tập tự luyện (có đáp án)
Dùng bảng giá A1:D7 và bảng ngưỡng K1:L6 ở trên. Tự làm trước, rồi mới xem đáp án. Nên gõ lại bảng vào Excel hoặc Google Sheets để thử thật, như ở Hình 4.
Ảnh: Simon Hattinga Verschure webmarbles / Wikimedia Commons, CC0
- Viết công thức lấy đơn giá của SP04.
- Đơn hàng gồm SP02 × 3, SP04 × 5, SP01 × 1. Tính tổng tiền.
- Viết công thức lấy đơn vị tính của SP05.
- Xếp loại cho điểm 6,4 và điểm 9.
- Công thức
=VLOOKUP("SP07",$A$2:$D$7,4,FALSE)trả về gì? Sửa để hiện "Không có mã". - Công thức
=VLOOKUP("SP01",$A$2:$D$7,5,FALSE)trả về gì, vì sao?
Đáp án
=VLOOKUP("SP04",$A$2:$D$7,4,FALSE)→ 27.000.- 3 × 52.000 = 156.000; 5 × 27.000 = 135.000; 1 × 185.000 = 185.000. Tổng 156.000 + 135.000 + 185.000 = 476.000 đồng.
=VLOOKUP("SP05",$A$2:$D$7,3,FALSE)→ "thùng".- 6,4 nhỏ hơn 6,5 nên lấy ngưỡng 5 → Trung bình; 9 trùng ngưỡng 9 → Xuất sắc.
- Trả về #N/A vì SP07 không có trong bảng. Sửa:
=IFERROR(VLOOKUP("SP07",$A$2:$D$7,4,FALSE),"Không có mã"). - Trả về #REF! vì vùng A:D chỉ có 4 cột, không có cột thứ 5.
Tiêu chí tự đánh giá
- Đạt: đúng 6/6 câu; mọi công thức có FALSE (hoặc 0) và vùng bảng có $; tổng câu 2 đúng 476.000.
- Đạt: giải thích được bằng lời vì sao câu 4 ra Trung bình chứ không phải Khá.
- Chưa đạt: bỏ trống đối số thứ tư, hoặc kéo công thức xuống bị #N/A ở các dòng dưới mà không biết vì sao.
Bước tiếp theo trong lộ trình là PivotTable, biểu đồ & dashboard: khi bảng đơn hàng đã có đủ đơn giá và thành tiền nhờ VLOOKUP, bạn sẽ tổng hợp doanh thu theo tháng, sản phẩm, nhân viên chỉ bằng vài cú kéo thả.
Học tiếp có hệ thống: Xem lộ trình 8 bước học tin học văn phòng (Excel, Word) trên NAMBIN, từ Excel nền tảng đến bộ báo cáo hoàn chỉnh và bài test tuyển dụng.
.jpg){kind=link}