Cách Dùng Hàm VLOOKUP Trong Excel: Cú Pháp, Ví Dụ, Lỗi

VLOOKUP là hàm Excel dò một giá trị ở cột đầu tiên của vùng dữ liệu rồi trả về giá trị nằm cùng hàng ở cột bạn chỉ định. Cách dùng hàm VLOOKUP gói trong cú pháp bốn đối số, =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Đối số cuối đặt là 0 (hoặc FALSE) để dò chính xác, còn 1 (hoặc TRUE) hay bỏ trống là dò gần đúng. Ví dụ =VLOOKUP(F3,$B$3:$D$8,2,FALSE) lấy mã sách ở ô F3, tìm trong cột B của vùng B3:D8 và trả về tên sách ở cột thứ 2. Dấu $ (phím F4) khoá vùng để kéo công thức xuống không bị trượt. Lỗi #N/A báo hàm không tìm thấy giá trị cần dò.

Bài đi từ định nghĩa và cú pháp tới các ví dụ có dữ liệu kèm kết quả viết bằng chữ, rồi cách đọc và sửa lỗi, cuối cùng là những trường hợp VLOOKUP không còn phù hợp. Cuối bài có ba bài tập kèm đáp án. Mọi ví dụ dùng chung một bảng danh mục sách nhỏ, bạn có thể gõ lại vào Excel để đối chiếu từng kết quả.

Hàm VLOOKUP là gì và dùng vào việc gì?

Chữ V trong VLOOKUP là vertical (dọc): hàm dò theo cột. Cách làm giống tra danh bạ. Bạn có một “chìa khoá” (ví dụ mã sách), hàm tìm chìa khoá đó trong cột đầu của bảng rồi đọc ra thông tin nằm cùng hàng. Hàm dùng được cho những việc như:

• điền tên sách, tác giả vào danh sách đơn hàng khi chỉ có mã sách;
• ghép dữ liệu từ hai bảng có chung một cột mã;
• xếp nhóm (mỏng, vừa, dày…) theo các mốc số.

Hai điều kiện cần nhớ: giá trị cần tìm phải nằm ở cột đầu tiên của vùng dữ liệu, và kết quả chỉ lấy được từ cột đó hoặc các cột bên phải nó.

Dữ liệu mẫu dùng trong bài

Bảng danh mục nằm ở B2:D8: tiêu đề ở hàng 2, dữ liệu từ hàng 3 đến hàng 8, bắt đầu từ cột B. Mã sách và các số liệu trong bài là dữ liệu minh hoạ.

Cách dùng hàm VLOOKUP: cú pháp và ý nghĩa 4 đối số

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Nếu vùng bắt đầu ở cột B thì cột B là cột 1, cột C là cột 2, cột D là cột 3. Đếm nhầm từ cột A của sheet sẽ trả về sai cột, hoặc báo #REF! khi số vượt quá số cột của vùng.

Bài này viết các đối số cách nhau bằng dấu phẩy. Nếu Excel báo lỗi cú pháp, hãy thử dấu chấm phẩy (;). Hai dạng là cùng một công thức; ký tự nào được chấp nhận tuỳ cài đặt vùng (Regional) của Windows và Excel trên máy bạn. Khi gõ chữ trong công thức, dùng dấu ngoặc kép thẳng (“). Dấu ngoặc kép cong dán từ web hoặc từ tin nhắn có thể làm công thức báo lỗi.

Ví dụ 1: tra tên sách theo mã sách

Gõ S04 vào ô F3, rồi gõ công thức sau vào G3:

=VLOOKUP(F3,$B$3:$D$8,2,FALSE)

Kết quả: Đất rừng phương Nam. Excel làm lần lượt ba bước:

1. Lấy giá trị ở F3, tức S04.
2. Tìm S04 trong cột B của vùng B3:D8 và dừng ở hàng 6, vì với FALSE hàm chỉ chấp nhận ô khớp hoàn toàn.
3. Đọc ô nằm cùng hàng 6 ở cột thứ 2 của vùng, tức cột C. Đó là tên sách.

Hàm không phân biệt chữ hoa và chữ thường, nên gõ s04 vẫn ra cùng kết quả.

Ví dụ 2: kéo công thức cho cả danh sách và khoá vùng bằng dấu $

Giả sử cột F là danh sách mã sách trong một đơn hàng: F3 là S05, F4 là S01, F5 là S06. Gõ vào G3 để lấy tên sách và H3 để lấy tác giả:

• G3: =VLOOKUP(F3,$B$3:$D$8,2,FALSE)
• H3: =VLOOKUP(F3,$B$3:$D$8,3,FALSE)

Rồi chọn G3:H3 và kéo xuống hàng 5. Địa chỉ F3 không có dấu $ nên tự đổi thành F4, F5 khi kéo xuống. Vùng $B$3:$D$8 có dấu $ nên đứng yên. Kết quả:

Thử viết vùng không có dấu $, tức B3:D8. Khi kéo xuống, vùng trượt theo: ở G4 nó thành B4:D9. Vùng này đã mất hàng 3, hàng chứa S01, nên G4 báo #N/A dù mã có trong bảng. Để thêm dấu $, hãy bôi đen địa chỉ vùng trong công thức rồi nhấn F4 một lần (trên một số laptop cần giữ thêm phím Fn). Bạn cũng có thể tự gõ dấu $.

Dò chính xác và dò gần đúng

Đối số cuối quyết định hàm tìm khớp hoàn toàn hay tìm theo mốc:

Bỏ trống đối số cuối nghĩa là dò gần đúng. Vì vậy công thức tra mã mà quên ghi FALSE có thể trả về kết quả sai mà không báo lỗi, nhất là khi cột mã chưa sắp xếp.

Ví dụ 3: xếp nhóm sách theo số trang

Trên một sheet riêng, lập bảng mốc ở E3:F5 như sau, sắp xếp tăng dần:

Số trang của năm cuốn sách giả định nằm ở B3:B7. Gõ vào C3 rồi kéo xuống C7:

=VLOOKUP(B3,$E$3:$F$5,2,TRUE)

Chú ý Sách C: 280 trang gần mốc 300 hơn mốc 150, nhưng kết quả là Vừa. Dò gần đúng không chọn mốc gần nhất mà chọn mốc lớn nhất không vượt quá giá trị tìm, ở đây là 150. Vì vậy bảng mốc nên có mốc 0 ở đầu; không có thì sách dưới mốc nhỏ nhất sẽ báo #N/A.

Tra dữ liệu ở sheet khác

Khi bảng danh mục nằm ở sheet khác, đặt tên sheet và dấu chấm than trước địa chỉ vùng. Với sheet tên “Danh mục” (có dấu cách), tên phải nằm trong dấu nháy đơn:

=VLOOKUP(A2,'Danh mục'!$B$3:$D$8,2,FALSE)

Tên sheet không có dấu cách thì không cần nháy đơn, ví dụ DanMuc!$B$3:$D$8. Cách nhanh hơn: khi đang gõ công thức, bấm sang sheet kia và quét chọn vùng; Excel sẽ tự điền tên sheet.

Tra theo một phần của chữ hoặc của mã

Khi dò chính xác và giá trị tìm là văn bản, bạn có thể dùng * thay cho một chuỗi ký tự bất kỳ và ? thay cho đúng một ký tự. Muốn lấy tác giả của cuốn có tên bắt đầu bằng “Lão”, đặt tên sách ở cột đầu của vùng:

=VLOOKUP("Lão*",$C$3:$D$8,2,FALSE)

Kết quả: Nam Cao. Nếu nhiều dòng cùng khớp, hàm lấy dòng đầu tiên từ trên xuống.

Có khi mã cần tra nằm lẫn trong một chuỗi dài hơn. Giả sử ô A2 ghi số phiếu mượn S03-0915, trong đó ba ký tự đầu là mã sách. Lồng LEFT vào đối số đầu để cắt lấy mã rồi mới dò:

=VLOOKUP(LEFT(A2,3),$B$3:$D$8,2,FALSE)

Kết quả: Lão Hạc. Mã nằm ở cuối thì dùng RIGHT, nằm giữa thì dùng MID theo cùng cách. Lưu ý LEFT, RIGHT và MID luôn trả về văn bản. Nếu cột đầu của bảng chứa số thật (ví dụ mã kho 101, 102), phải bọc thêm VALUE, như VALUE(LEFT(A2,3)), nếu không hàm sẽ báo #N/A.

Các lỗi của VLOOKUP và cách sửa

Dữ liệu có mà vẫn #N/A: kiểm theo năm bước

1. Chữ hay số? Giá trị tìm là số nhưng cột đầu lưu dạng văn bản (hoặc ngược lại) thì không khớp. Dùng =ISNUMBER(F3) để biết ô có phải số thật không. Đổi số thành văn bản bằng F3&"", đổi văn bản thành số bằng VALUE(F3).
2. Khoảng trắng thừa. So =LEN(F3) với số ký tự bạn mong đợi (mã S03 có 3 ký tự). Nếu dư, thử TRIM(F3). Dữ liệu dán từ web đôi khi có dấu cách không ngắt mà TRIM không bỏ được; khi đó dùng TRIM(SUBSTITUTE(F3,CHAR(160)," ")).
3. Cột đầu của vùng. Giá trị tìm phải nằm ở cột trái nhất của vùng. Nếu cột mã nằm bên phải cột tên, vùng phải bắt đầu từ cột mã.
4. Đối số cuối. Ghi FALSE hoặc 0 khi cần khớp chính xác.
5. Vùng đủ rộng và đã khoá. Vùng phải bao hết các hàng cần tra, và có dấu $ nếu bạn kéo công thức (xem ví dụ 2).

Ẩn lỗi bằng IFERROR hoặc IFNA

Để bảng gọn hơn khi không tìm thấy mã, bọc VLOOKUP trong IFERROR. Giả sử F3 là S09, mã không có trong danh mục:

=IFERROR(VLOOKUP(F3,$B$3:$D$8,2,FALSE),"Không tìm thấy")

Kết quả: Không tìm thấy. Tuy vậy, IFERROR che mọi loại lỗi, kể cả #REF! và #NAME? do chính công thức bị sai. Công thức sai mà không có cảnh báo thì rất khó phát hiện. Nếu bạn chỉ muốn xử lý trường hợp không tìm thấy, dùng IFNA (từ Excel 2013), hàm này chỉ thay riêng lỗi #N/A:

=IFNA(VLOOKUP(F3,$B$3:$D$8,2,FALSE),"Không tìm thấy")

Khi nào không nên dùng VLOOKUP

Tra từ phải sang trái

VLOOKUP chỉ nhìn sang phải của cột dò. Muốn tìm mã sách khi chỉ biết tên sách (tên ở cột C, mã ở cột B), cách làm là INDEX kết hợp MATCH:

=INDEX($B$3:$B$8,MATCH("Lão Hạc",$C$3:$C$8,0))

Kết quả: S03. MATCH tìm vị trí của “Lão Hạc” trong cột C (vị trí thứ 3), INDEX lấy ô ở vị trí thứ 3 của cột B. Bản Excel mới có XLOOKUP làm việc này gọn hơn: =XLOOKUP("Lão Hạc",$C$3:$C$8,$B$3:$B$8), cũng cho S03. XLOOKUP có ở Excel 2021 trở lên và Microsoft 365; các bản cũ hơn không có.

Tra theo nhiều điều kiện

VLOOKUP chỉ có một giá trị tìm. Khi cần khớp đồng thời hai điều kiện, cách dễ đọc là thêm một cột phụ ghép hai điều kiện thành một khoá, đặt ở cột đầu. Ví dụ bảng tồn kho theo mã sách và kho, cột A là cột phụ với công thức =B3&"-"&C3:

Muốn biết sách S02 còn bao nhiêu cuốn ở Kho 2, ghép khoá giống cột phụ rồi tra ở cột thứ 4:

=VLOOKUP("S02-Kho 2",$A$3:$D$8,4,FALSE)

Kết quả: 9. Có thể thay cột phụ bằng cách kết hợp VLOOKUP với CHOOSE({1,2},…), nhưng công thức khó đọc và dễ sai hơn, nên bài này chọn cột phụ.

Chèn cột làm lệch số cột

Số cột trong VLOOKUP là con số cố định. Giả sử bảng danh mục ban đầu ở B2:D8, công thức lấy tác giả dùng số 3. Sau đó bạn chèn thêm cột “Thể loại” vào giữa cột C và D. Vùng tự giãn thành B3:E8. Số 3 giờ trỏ vào cột Thể loại mới, còn trống, nên công thức trả về 0 mà không báo lỗi. Cách khắc phục là để MATCH tìm số cột theo tên tiêu đề. Với S04 ở ô F3:

=VLOOKUP(F3,$B$3:$E$8,MATCH("Tác giả",$B$2:$E$2,0),FALSE)

Sau khi chèn cột, “Tác giả” nằm ở vị trí thứ 4 trong B2:E2, nên MATCH trả về 4 và kết quả là Đoàn Giỏi. Chèn thêm cột nữa thì công thức vẫn tự tìm đúng cột.

Mã bị trùng

Nếu cột đầu có hai dòng cùng mã, VLOOKUP chỉ trả về dòng đầu tiên từ trên xuống, dòng còn lại không bao giờ được chọn. Để kiểm trước, dùng hàm COUNTIF đếm số lần mã xuất hiện, ví dụ =COUNTIF($B$3:$B$8,F3). Kết quả lớn hơn 1 nghĩa là mã bị trùng.

So sánh VLOOKUP với HLOOKUP, INDEX và MATCH, XLOOKUP

Ba bài tập có đáp án

Dùng lại bảng danh mục B2:D8, bảng tồn kho và cách làm ở trên. Tự viết công thức trước khi xem đáp án.

1. Bài 1. Ô F3 chứa S02. Viết công thức lấy tên tác giả của mã đó từ bảng danh mục.
2. Bài 2. Một thư viện phân loại thành viên theo số cuốn đã mượn bằng bảng mốc ở E3:F5: từ 0 là “Mới”, từ 5 là “Quen”, từ 20 là “Thân thiết”. Ô B3 ghi 17 cuốn. Viết công thức dò gần đúng và cho biết kết quả.
3. Bài 3. Công thức =VLOOKUP("S01-Kho 2",$A$3:$D$8,5,FALSE) trên bảng tồn kho báo lỗi gì? Sửa thế nào và kết quả sau khi sửa là bao nhiêu?

Đáp án

• Bài 1: =VLOOKUP(F3,$B$3:$D$8,3,FALSE), kết quả Ngô Tất Tố. Tác giả nằm ở cột thứ 3 của vùng B3:D8.
• Bài 2: =VLOOKUP(B3,$E$3:$F$5,2,TRUE), kết quả Quen. Mốc lớn nhất không vượt quá 17 là 5, dù 17 gần mốc 20 hơn.
• Bài 3: Lỗi #REF!, vì vùng A3:D8 chỉ có 4 cột mà công thức đòi cột 5. Đổi 5 thành 4 để lấy cột Tồn kho, kết quả 7.