Trả lời nhanh: Hàm VLOOKUP dò một giá trị ở cột đầu tiên của bảng rồi trả về dữ liệu cùng hàng ở cột bạn chỉ định. Cú pháp: =VLOOKUP(giá_trị_tìm, vùng_bảng, số_thứ_tự_cột, FALSE). Luôn ghi FALSE (hoặc 0) để dò chính xác, cố định vùng bằng dấu $ và bọc IFNA để xử lý lỗi #N/A.
Điểm chính
- Giá trị cần tìm bắt buộc nằm ở cột ngoài cùng bên trái của vùng bảng.
- Bỏ trống tham số thứ 4 thì Excel và Google Sheets đều mặc định dò gần đúng (TRUE), rất dễ ra kết quả sai.
- Lỗi #N/A thường do khoảng trắng thừa, số lưu dạng văn bản hoặc vùng bảng bị trượt khi kéo công thức.
- Dò giữa 2 sheet chỉ cần thêm tên sheet trước vùng: ‘Bảng giá’!$A$2:$D$6.
- Excel 365, 2021, 2024 và Google Sheets đã có XLOOKUP, linh hoạt hơn VLOOKUP; Excel 2016/2019 thì dùng INDEX/MATCH.
Mục lục
- Cú pháp hàm VLOOKUP trong Excel và Google Sheets
- Cách dùng hàm VLOOKUP từng bước với ví dụ cụ thể
- Dò tìm chính xác (FALSE/0) và dò gần đúng (TRUE/1) khác nhau thế nào?
- Vì sao phải cố định vùng bằng dấu $?
- Cách dùng hàm VLOOKUP giữa 2 sheet và giữa 2 file
- Lỗi #N/A VLOOKUP: nguyên nhân và cách sửa
- Lỗi #REF!, #VALUE!, #NAME? trong VLOOKUP sửa thế nào?
- Dùng IFERROR hay IFNA để ẩn lỗi VLOOKUP?
- Giới hạn của VLOOKUP bạn cần biết
- Khi nào nên dùng XLOOKUP hoặc INDEX/MATCH thay cho VLOOKUP?
- Câu hỏi thường gặp
Cú pháp hàm VLOOKUP trong Excel và Google Sheets
VLOOKUP là viết tắt của Vertical Lookup (dò tìm theo chiều dọc). Hàm dò từ trên xuống ở cột đầu của vùng dữ liệu, tìm hàng chứa giá trị cần tìm, rồi lấy giá trị ở cột khác trên cùng hàng.
Cú pháp trong Excel theo tài liệu hỗ trợ của Microsoft:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Google Sheets dùng đúng cấu trúc này. Trên giao diện tiếng Việt, Google đặt tên các tham số là khoá_tìm_kiếm, dải_ô, chỉ_mục và được_sắp_xếp.
| Tham số | Bắt buộc? | Ý nghĩa | Lưu ý hay gặp |
|---|---|---|---|
| lookup_value (giá trị tìm) | Có | Giá trị cần dò, thường là một ô như F2 hoặc một chuỗi trong ngoặc kép như “SP003”. | Phải có mặt trong cột đầu tiên của vùng bảng. |
| table_array (vùng bảng) | Có | Vùng chứa cả cột dò và cột kết quả, ví dụ $A$2:$D$6. | Nên cố định bằng dấu $ để không bị trượt khi kéo công thức. |
| col_index_num (số thứ tự cột) | Có | Cột thứ mấy trong vùng bảng chứa kết quả. Cột đầu tiên của vùng là 1. | Đếm trong vùng bảng, không đếm theo cột A, B, C của trang tính. |
| range_lookup (kiểu dò) | Không | FALSE hoặc 0 là dò chính xác. TRUE hoặc 1 là dò gần đúng. | Bỏ trống sẽ bị hiểu là TRUE. |
Dấu phân cách giữa các tham số phụ thuộc thiết lập vùng (Region) của máy hoặc của file. Nếu Excel báo lỗi khi bạn gõ dấu phẩy, hãy thử dấu chấm phẩy: =VLOOKUP(F2;$A$2:$D$6;3;FALSE). Bài này dùng dấu phẩy cho dễ đọc.
Cách dùng hàm VLOOKUP từng bước với ví dụ cụ thể
Giả sử bạn có bảng giá văn phòng phẩm ở vùng A1:D6 như dưới đây (ví dụ minh hoạ). Bạn cần gõ mã sản phẩm vào ô F2 và để ô G2 tự hiện đơn giá.
| Hàng | A: Mã SP | B: Tên sản phẩm | C: Đơn giá (đồng) | D: Nhóm |
|---|---|---|---|---|
| 1 | Mã SP | Tên sản phẩm | Đơn giá | Nhóm |
| 2 | SP001 | Bút bi | 5.000 | Bút |
| 3 | SP002 | Sổ tay A5 | 28.000 | Sổ |
| 4 | SP003 | Giấy A4 (ram) | 85.000 | Giấy |
| 5 | SP004 | Bìa hồ sơ | 12.000 | Lưu trữ |
| 6 | SP005 | Kẹp giấy (hộp) | 9.000 | Lưu trữ |
- Bấm vào ô G2, gõ =VLOOKUP(.
- Bấm vào ô F2 (nơi bạn gõ mã cần tìm), rồi gõ dấu phẩy.
- Quét chọn vùng A2:D6. Nhấn F4 để Excel đổi thành $A$2:$D$6, rồi gõ dấu phẩy.
- Gõ 3 vì đơn giá nằm ở cột thứ 3 tính từ cột A của vùng bảng, rồi gõ dấu phẩy.
- Gõ FALSE (hoặc 0), đóng ngoặc và nhấn Enter.
Công thức hoàn chỉnh: =VLOOKUP(F2,$A$2:$D$6,3,FALSE). Khi F2 là SP003, G2 trả về 85.000. Muốn lấy tên sản phẩm thì đổi 3 thành 2.
Một điểm người mới hay nhầm: số thứ tự cột được đếm trong vùng bảng. Nếu vùng bảng bắt đầu từ cột B, ví dụ $B$2:$D$6, thì cột B là 1, cột C là 2, cột D là 3.
Dò tìm chính xác (FALSE/0) và dò gần đúng (TRUE/1) khác nhau thế nào?
Đây là tham số gây nhiều kết quả sai nhất. Cả Microsoft và Google đều ghi rõ: nếu bỏ trống tham số thứ 4, hàm mặc định dò gần đúng. Vì vậy công thức =VLOOKUP(F2,$A$2:$D$6,3) có thể trả về một đơn giá trông rất hợp lý nhưng là của sản phẩm khác.
Dò chính xác: dùng cho mã, tên, số điện thoại
Với FALSE hoặc 0, hàm chỉ nhận giá trị khớp hoàn toàn. Dữ liệu không cần sắp xếp. Không có giá trị khớp thì trả #N/A. Gần như mọi tình huống tra mã hàng, mã nhân viên, mã đơn đều cần kiểu dò này.
Dò gần đúng: dùng cho bảng mốc, bậc thang
Với TRUE hoặc 1, hàm tìm giá trị lớn nhất mà vẫn nhỏ hơn hoặc bằng giá trị cần tìm. Cột đầu của vùng bảng bắt buộc phải sắp xếp tăng dần. Ví dụ minh hoạ dưới đây là bảng chiết khấu theo doanh số đặt ở vùng H2:I5:
| H: Mốc doanh số (đồng) | I: Chiết khấu |
|---|---|
| 0 | 0% |
| 5.000.000 | 2% |
| 20.000.000 | 5% |
| 50.000.000 | 8% |
Công thức =VLOOKUP(27000000,$H$2:$I$5,2,TRUE) trả về 5%. Lý do: 27 triệu chưa đạt mốc 50 triệu, nên hàm dừng ở mốc gần nhất bên dưới là 20 triệu. Cách này thay được chuỗi IF lồng nhau khi xếp loại điểm hay tính hoa hồng theo bậc.
Hai điều cần nhớ với dò gần đúng. Thứ nhất, nếu giá trị cần tìm nhỏ hơn mốc nhỏ nhất, hàm trả #N/A. Đó là lý do bảng trên có mốc 0. Thứ hai, nếu cột mốc chưa được sắp xếp tăng dần, hàm vẫn trả kết quả nhưng kết quả có thể sai mà không báo lỗi.
Vì sao phải cố định vùng bằng dấu $?
Khi bạn kéo công thức từ G2 xuống G3, G4, Excel tự dịch mọi địa chỉ tương đối (relative reference) xuống một hàng. Vùng A2:D6 sẽ thành A3:D7, rồi A4:D8. Đến một lúc, mã SP001 nằm ngoài vùng dò và công thức trả #N/A dù dữ liệu vẫn còn đó.
Dấu $ biến địa chỉ thành tuyệt đối (absolute reference). $A$2:$D$6 giữ nguyên dù bạn kéo công thức theo hướng nào. Ô giá trị tìm F2 thì để tương đối để nó tự đổi thành F3, F4 theo từng hàng.
- $A$2: khoá cả cột lẫn hàng. Dùng cho vùng bảng.
- A$2: chỉ khoá hàng. Hữu ích khi kéo công thức sang ngang.
- $A2: chỉ khoá cột. Hữu ích khi kéo công thức xuống dưới mà giá trị tìm luôn nằm ở cột A.
Trên Excel cho Windows, đặt con trỏ vào địa chỉ rồi nhấn F4 để xoay vòng qua các kiểu trên. Trên thiết bị khác, bạn gõ tay dấu $.
Cách dùng hàm VLOOKUP giữa 2 sheet và giữa 2 file
Bảng giá thường nằm ở một sheet riêng, đơn hàng ở sheet khác. Bạn chỉ cần thêm tên sheet trước vùng bảng.
VLOOKUP giữa 2 sheet trong cùng file
Giả sử bảng giá ở sheet tên “Bảng giá”, còn sheet “Đơn hàng” có mã sản phẩm ở cột B. Tại ô C2 của sheet “Đơn hàng”, bạn làm như sau:
- Gõ =VLOOKUP( rồi bấm ô B2, gõ dấu phẩy.
- Bấm sang tab sheet “Bảng giá”, quét chọn A2:D6 và nhấn F4.
- Gõ dấu phẩy, số 3, dấu phẩy, FALSE, đóng ngoặc rồi nhấn Enter. Excel tự quay về sheet “Đơn hàng”.
Công thức nhận được: =VLOOKUP(B2,’Bảng giá’!$A$2:$D$6,3,FALSE). Tên sheet có dấu cách hoặc dấu tiếng Việt sẽ được đặt trong dấu nháy đơn, theo sau là dấu chấm than. Nếu bạn gõ tay, hãy giữ đúng cặp nháy đơn này, nếu không Excel sẽ báo lỗi công thức.
VLOOKUP giữa 2 file Excel
Cách làm giống hệt dò giữa 2 sheet. Bạn mở cả hai file, đến bước chọn vùng bảng thì chuyển sang cửa sổ file nguồn và quét chọn. Excel sẽ ghi tên file trong ngoặc vuông, ví dụ =VLOOKUP(B2,[BangGia.xlsx]Sheet1!$A$2:$D$6,3,FALSE).
Khi đóng file nguồn, Excel đổi phần tên file thành đường dẫn đầy đủ tới nơi lưu file. Kết quả cũ vẫn hiển thị, nhưng nếu file nguồn bị đổi tên, chuyển thư mục hoặc gửi cho người khác không kèm file nguồn, công thức sẽ không cập nhật được nữa. Mỗi lần mở file, Excel cũng có thể hỏi bạn có muốn cập nhật liên kết ngoài hay không. Giao diện và tên mục có thể khác theo phiên bản.
VLOOKUP giữa 2 file Google Sheets
Google Sheets không cho tham chiếu thẳng sang file khác bằng tên file. Bạn kết hợp VLOOKUP với IMPORTRANGE, hàm nhận đường link của file nguồn và vùng dữ liệu dạng chuỗi:
=VLOOKUP(B2,IMPORTRANGE(“đường_link_file_nguồn”,”Bảng giá!A2:D6″),3,FALSE)
Lần đầu dùng, ô công thức sẽ báo lỗi và yêu cầu bạn cho phép hai file kết nối với nhau. Sau khi cấp quyền, bất kỳ ai có quyền sửa file đích đều đọc được vùng dữ liệu đã kết nối. Cân nhắc kỹ nếu file nguồn chứa dữ liệu nhạy cảm.
Nhiều trang web và tiện ích AI mời bạn tải file Excel lên để “viết công thức VLOOKUP tự động”. Đừng tải lên file chứa dữ liệu khách hàng, lương hay số tài khoản. Chỉ cần mô tả cấu trúc cột hoặc dùng vài dòng dữ liệu giả là đủ để nhận công thức. Cũng nên cẩn thận với file Excel lạ có liên kết ngoài hoặc macro mà bạn không rõ nguồn gốc.
Lỗi #N/A VLOOKUP: nguyên nhân và cách sửa
#N/A nghĩa là “không có giá trị” (not available). Với dò chính xác, nó cho biết hàm không thấy giá trị cần tìm trong cột đầu. Kiểm tra lần lượt các nguyên nhân dưới đây.
| Nguyên nhân | Dấu hiệu nhận biết | Cách sửa |
|---|---|---|
| Khoảng trắng thừa ở đầu hoặc cuối | =LEN(A4) ra 6 trong khi “SP003” chỉ có 5 ký tự | Dùng =VLOOKUP(TRIM(F2),…) cho ô tìm. Với cột nguồn, tạo cột phụ =TRIM(CLEAN(A2)) rồi dán giá trị đè lên. |
| Số lưu dạng văn bản (hoặc ngược lại) | Ô có tam giác xanh ở góc, hoặc số căn trái | Chuyển về cùng kiểu: =VLOOKUP(VALUE(F2),…) nếu cột nguồn là số; =VLOOKUP(F2&””,…) nếu cột nguồn là văn bản. |
| Giá trị tìm không nằm ở cột đầu của vùng bảng | Vùng bảng bắt đầu từ cột bên trái cột mã | Chọn lại vùng sao cho cột mã là cột đầu tiên, hoặc chuyển sang XLOOKUP hay INDEX/MATCH. |
| Vùng bảng bị trượt khi kéo công thức | Hàng đầu đúng, các hàng dưới lỗi dần | Thêm dấu $ cho vùng bảng. |
| Giá trị thật sự không có trong bảng | Lọc cột nguồn không thấy mã đó | Bổ sung dữ liệu, hoặc bọc IFNA để hiện thông báo. |
Dữ liệu xuất từ phần mềm kế toán, CRM hay copy từ web rất hay mắc hai lỗi đầu. Cách phòng tốt nhất là chuẩn hoá ngay từ khâu nhập liệu (data entry), ví dụ quy định mã luôn là văn bản và không có dấu cách.
Lỗi #REF!, #VALUE!, #NAME? trong VLOOKUP sửa thế nào?
Các lỗi này cho biết chính công thức đang viết sai. Bọc IFERROR chỉ che lỗi chứ không sửa được.
Lỗi #REF!
Số thứ tự cột lớn hơn số cột của vùng bảng. Ví dụ vùng $A$2:$D$6 chỉ có 4 cột nhưng bạn ghi 5. Cách sửa: mở rộng vùng bảng tới cột chứa kết quả, hoặc giảm số thứ tự cột cho đúng. #REF! cũng xuất hiện khi sheet hoặc vùng nguồn đã bị xoá.
Lỗi #VALUE!
Theo tài liệu của Google, lỗi này xảy ra khi số thứ tự cột nhỏ hơn 1 hoặc là văn bản. Hãy kiểm tra tham số thứ 3: nó phải là số nguyên dương như 2, 3, 4.
Lỗi #NAME?
Thường do giá trị tìm dạng chữ thiếu dấu ngoặc kép, ví dụ =VLOOKUP(SP003,…) thay vì =VLOOKUP(“SP003”,…). Lỗi này cũng xuất hiện khi gõ sai tên hàm, hoặc khi mở file có XLOOKUP trên phiên bản Excel chưa hỗ trợ hàm này.
Không báo lỗi nhưng kết quả sai
Đây là tình huống nguy hiểm nhất vì không ai nhận ra. Ba thủ phạm quen thuộc:
- Bỏ trống tham số thứ 4, hàm tự dò gần đúng trên cột mã chưa sắp xếp.
- Có người chèn thêm một cột vào giữa vùng bảng. Số thứ tự cột 3 vẫn giữ nguyên nên giờ trỏ vào cột khác.
- Cột mã có giá trị trùng. VLOOKUP chỉ lấy dòng khớp đầu tiên từ trên xuống và bỏ qua các dòng sau.
Mã trùng là dấu hiệu bảng dữ liệu chưa có khoá duy nhất. Nguyên tắc này được giải thích kỹ trong bài về chuẩn hoá cơ sở dữ liệu, áp dụng được cả cho bảng tính.
Dùng IFERROR hay IFNA để ẩn lỗi VLOOKUP?
Khi danh sách tra cứu dài, một vài mã không có trong bảng là chuyện bình thường. Bạn có thể thay #N/A bằng thông báo dễ đọc:
- =IFERROR(VLOOKUP(F2,$A$2:$D$6,3,FALSE),”Không có mã”): thay mọi loại lỗi bằng chữ “Không có mã”.
- =IFNA(VLOOKUP(F2,$A$2:$D$6,3,FALSE),”Không có mã”): chỉ thay lỗi #N/A. Các lỗi #REF!, #VALUE! vẫn hiện ra.
Nên ưu tiên IFNA. Nếu bạn lỡ gõ sai số thứ tự cột, IFERROR sẽ biến cả cột thành “Không có mã” và bạn tưởng dữ liệu bị thiếu. IFNA giữ lại lỗi công thức để bạn kịp phát hiện. Google Sheets cũng gợi ý dùng IFNA cho trường hợp này.
Giới hạn của VLOOKUP bạn cần biết
VLOOKUP chạy tốt với bảng nhỏ, nhưng có những giới hạn mang tính thiết kế:
- Chỉ dò sang phải. Cột tìm phải nằm bên trái cột kết quả. Muốn tra tên ra mã khi cột mã đứng trước thì phải sắp xếp lại cột.
- Số thứ tự cột là con số cứng. Thêm hoặc xoá cột trong vùng bảng làm công thức trỏ sai.
- Chỉ trả kết quả đầu tiên. Không lấy được tất cả dòng khớp, cũng không chọn dò từ dưới lên.
Về bản chất, VLOOKUP là cách nối hai bảng qua một cột chung, giống phép JOIN trong cơ sở dữ liệu quan hệ. Khi số dòng lên tới hàng trăm nghìn và phải nối nhiều bảng mỗi ngày, công cụ như Power BI hay một cơ sở dữ liệu thật sẽ ổn định hơn một file Excel chằng chịt VLOOKUP.
Khi nào nên dùng XLOOKUP hoặc INDEX/MATCH thay cho VLOOKUP?
Nếu bạn đang dùng Excel cho Microsoft 365, Excel 2021, Excel 2024 hoặc Google Sheets, XLOOKUP là lựa chọn hợp lý cho công thức mới. Microsoft ghi rõ XLOOKUP không có trong Excel 2016 và Excel 2019. Với các phiên bản này, hoặc khi file phải gửi cho nhiều người dùng Excel khác nhau, INDEX/MATCH là phương án an toàn.
Cú pháp XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Viết lại ví dụ tra đơn giá: =XLOOKUP(F2,$A$2:$A$6,$C$2:$C$6,”Không có mã”). Bạn chọn riêng cột tìm và cột kết quả, nên không cần đếm cột. XLOOKUP mặc định dò chính xác và có sẵn tham số thứ 4 để thay thông báo khi không tìm thấy, không cần bọc IFNA.
Tham số search_mode bằng -1 cho phép dò từ dưới lên, tiện khi cần lấy giao dịch mới nhất của một khách hàng.
Cú pháp INDEX/MATCH
=INDEX($C$2:$C$6,MATCH(F2,$A$2:$A$6,0))
MATCH tìm vị trí của F2 trong cột mã, INDEX lấy giá trị ở vị trí đó trong cột đơn giá. Số 0 trong MATCH là dò chính xác, đừng bỏ qua. Cách này dò được sang trái và không bị ảnh hưởng khi chèn cột, chạy trên mọi phiên bản Excel và Google Sheets.
| Tiêu chí | VLOOKUP | INDEX/MATCH | XLOOKUP |
|---|---|---|---|
| Hướng dò | Chỉ sang phải | Mọi hướng | Mọi hướng |
| Kiểu dò mặc định | Gần đúng (TRUE) | Tuỳ tham số MATCH, cần ghi 0 | Chính xác |
| Chèn hoặc xoá cột | Dễ trỏ sai cột | Không ảnh hưởng | Không ảnh hưởng |
| Thông báo khi không thấy | Cần bọc IFNA/IFERROR | Cần bọc IFNA/IFERROR | Có sẵn tham số if_not_found |
| Dò từ dưới lên | Không | Không trực tiếp | Có (search_mode = -1) |
| Phiên bản hỗ trợ | Mọi phiên bản | Mọi phiên bản | Excel 365, 2021, 2024, Google Sheets |
Bạn không cần viết lại các file VLOOKUP đang chạy đúng. Hãy chuyển khi cần dò sang trái, khi bảng nguồn hay bị chèn cột, hoặc khi làm file mới. Nếu công việc của bạn chủ yếu là tra cứu và tổng hợp số liệu, nền tảng phân tích dữ liệu sẽ giúp bạn chọn đúng công cụ cho từng quy mô.
Câu hỏi thường gặp
Hàm VLOOKUP có phân biệt chữ hoa, chữ thường không?
Không. VLOOKUP coi “sp003” và “SP003” là một giá trị. Nếu bắt buộc phân biệt, bạn cần kết hợp INDEX/MATCH với hàm EXACT, ví dụ =INDEX($C$2:$C$6,MATCH(TRUE,EXACT($A$2:$A$6,F2),0)).
Vì sao VLOOKUP trả về 0 thay vì ô trống?
Khi ô kết quả trong bảng nguồn trống, VLOOKUP trả về 0. Muốn hiện ô trống, bạn có thể nối thêm chuỗi rỗng: =VLOOKUP(F2,$A$2:$D$6,3,FALSE)&””. Lưu ý cách này biến kết quả thành văn bản, nên không dùng khi cột đó còn được tính toán.
Có dùng VLOOKUP với nhiều điều kiện được không?
Được, bằng cột phụ. Tạo một cột ở bên trái bảng nguồn ghép các điều kiện, ví dụ =B2&”|”&C2, rồi dò bằng giá trị ghép tương ứng.
Lỗi #N/A dù đã thấy mã trong bảng thì kiểm tra gì trước?
Kiểm tra theo thứ tự: tham số thứ 4 đã là FALSE chưa, vùng bảng có dấu $ chưa, rồi so sánh trực tiếp hai ô bằng công thức =F2=A4. Nếu kết quả là FALSE, dùng =LEN() để tìm khoảng trắng thừa và =ISTEXT() để xem ô nào đang là văn bản.