Bạn nhận một bảng đơn hàng chỉ có mã hàng và số lượng, trong khi tên hàng và đơn giá lại nằm ở một bảng danh mục khác. Nếu bảng chỉ có vài dòng, bạn có thể nhìn rồi gõ tay. Khi bảng có vài trăm dòng, cách làm thủ công vừa mất cả buổi vừa dễ gõ nhầm, và chỉ một đơn giá sai cũng làm lệch cả báo cáo doanh thu.
Hàm VLOOKUP giải quyết đúng việc này: bạn đưa cho Excel một mã, Excel tự tìm mã đó trong bảng danh mục và lấy về thông tin bạn cần. Đây là hàm tra cứu được dùng nhiều nhất ở văn phòng, từ kế toán lấy đơn giá, nhân sự lấy thông tin nhân viên cho tới bộ phận kinh doanh tính hoa hồng.
Bài viết này đi từ cú pháp cơ bản tới tìm chính xác, tìm gần đúng theo bậc thang, cố định vùng bằng dấu $, kết hợp IFERROR và MATCH, tìm theo nhiều điều kiện và ký tự đại diện. Phần cuối bài giải thích các lỗi hay gặp và cho biết khi nào bạn nên chuyển sang XLOOKUP hoặc INDEX/MATCH. Các ví dụ dùng số liệu giả định và đều đã được kiểm tra trên Excel.
Hàm VLOOKUP là gì?
VLOOKUP là viết tắt của Vertical Lookup, nghĩa là tra cứu theo chiều dọc. Hàm này tìm một giá trị ở cột đầu tiên của vùng tra cứu, dò lần lượt từ trên xuống dưới. Khi gặp dòng chứa giá trị cần tìm, VLOOKUP dừng lại và lấy giá trị ở cột bạn chỉ định trên cùng dòng đó.
Cách VLOOKUP làm việc giống như khi bạn tra danh bạ điện thoại: bạn dò tên ở cột đầu, thấy tên rồi thì nhìn sang phải để đọc số điện thoại. Từ cách làm việc này, bạn cần nhớ ngay hai điều:
Giá trị cần tìm phải nằm ở cột đầu tiên của vùng tra cứu. VLOOKUP không dò ở cột thứ hai hay cột thứ ba.
Kết quả phải nằm bên phải cột tìm kiếm. VLOOKUP chỉ đi từ cột đầu tiên sang phải, không lấy được dữ liệu ở bên trái.
VLOOKUP có trong mọi phiên bản Excel, và Google Sheets cũng có hàm này với cú pháp giống hệt.
Cú pháp hàm VLOOKUP
Đối số là các thông tin bạn điền vào trong cặp ngoặc đơn của hàm, ngăn cách nhau bằng dấu phẩy. Hàm VLOOKUP có 4 đối số, trong đó 3 đối số đầu là bắt buộc:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Đối số | Ý nghĩa | Ví dụ |
|---|---|---|
lookup_value | Giá trị cần tìm. Bạn thường trỏ tới một ô, ví dụ ô chứa mã hàng. |
|
table_array | Vùng tra cứu. Cột đầu tiên của vùng phải chứa giá trị cần tìm, các cột còn lại chứa thông tin cần lấy. |
|
col_index_num | Số thứ tự của cột cần lấy, đếm trong vùng tra cứu. Cột đầu tiên của vùng là 1, cột kế tiếp là 2. |
|
range_lookup | Kiểu tìm. FALSE (hoặc 0) là tìm chính xác, TRUE (hoặc 1) là tìm gần đúng. Nếu bạn bỏ trống, Excel hiểu là TRUE. |
|
Người mới hay nhầm ở đối số thứ ba. Số thứ tự cột được đếm trong vùng tra cứu, không phải theo cột của trang tính. Với vùng G2:I6, cột G là cột 1, cột H là cột 2, cột I là cột 3, dù trên trang tính cột G là cột thứ 7.
Đối số cuối cùng tuy nằm trong ngoặc vuông, tức là không bắt buộc, nhưng bạn nên luôn ghi rõ. Phần lớn công việc tra cứu như tìm mã hàng, mã nhân viên, mã khách hàng cần tìm chính xác, nên bạn ghi FALSE. Bạn chỉ dùng TRUE khi tra theo bậc thang như tính hoa hồng hay xếp loại, và ví dụ 2 trong bài sẽ giải thích kỹ trường hợp này.
Lưu ý về dấu phân cách: bài viết dùng dấu phẩy giữa các đối số. Nếu Excel trên máy bạn báo lỗi khi gõ dấu phẩy, máy đang dùng dấu chấm phẩy theo cài đặt vùng (Region) của Windows. Khi đó bạn gõ =VLOOKUP(A2;$G$2:$I$6;2;FALSE) là được. Dấu phân cách phụ thuộc cài đặt vùng của máy, không phụ thuộc phiên bản Excel.
Ví dụ 1: Tra tên hàng và đơn giá theo mã hàng
Đây là cách dùng VLOOKUP cơ bản nhất. Trang tính có hai bảng:
Bảng đơn hàng nằm ở cột A tới cột E. Cột A ghi mã hàng, cột B ghi số lượng, còn cột C (Tên hàng), cột D (Đơn giá) và cột E (Thành tiền) đang để trống.
Bảng danh mục nằm ở vùng G1:I6. Dòng 1 là tiêu đề, 5 mặt hàng nằm từ dòng 2 tới dòng 6, với cột G là mã hàng, cột H là tên hàng và cột I là đơn giá. Vùng tra cứu chỉ cần lấy phần dữ liệu G2:I6, không lấy dòng tiêu đề.
Bạn bấm vào ô C2 và gõ công thức sau để lấy tên hàng:
=VLOOKUP(A2,$G$2:$I$6,2,FALSE)
Công thức này đọc là: lấy mã hàng ở ô A2, tìm trong cột đầu tiên của vùng $G$2:$I$6, tìm chính xác (FALSE), thấy rồi thì lấy giá trị ở cột thứ 2 của vùng, tức cột H. Ô A2 chứa mã SP03, VLOOKUP tìm thấy SP03 ở ô G4 và trả về Kẹp tài liệu ở ô H4. Các dấu $ trong công thức dùng để khóa vùng tra cứu, phần sau của bài sẽ giải thích kỹ.
Ở ô D2, bạn dùng công thức gần như giống hệt, chỉ đổi số cột từ 2 thành 3 để lấy đơn giá ở cột I:
=VLOOKUP(A2,$G$2:$I$6,3,FALSE)
Kết quả ở ô D2 là 12.000. Ô E2 tính thành tiền bằng =B2*D2, ra 10 × 12.000 = 120.000. Cuối cùng, bạn chọn vùng C2:E2, rồi kéo ô vuông nhỏ ở góc dưới bên phải vùng chọn xuống tới dòng 5 để áp dụng cho các đơn còn lại.

Bảng danh mục không cần sắp xếp khi bạn tìm chính xác bằng FALSE. Mã SP03 nằm ở giữa bảng hay cuối bảng thì VLOOKUP vẫn tìm ra. Bảng danh mục cũng có thể nằm ở một sheet khác, và cách viết công thức cho trường hợp này nằm trong phần câu hỏi thường gặp ở cuối bài.
Ví dụ 2: Tính hoa hồng theo bậc doanh số (tìm gần đúng)
Tìm gần đúng dùng cho các bảng bậc thang, nơi mỗi mức áp dụng cho cả một khoảng giá trị. Giả sử công ty trả hoa hồng theo doanh số tháng như sau:
Doanh số tháng | Tỷ lệ hoa hồng |
|---|---|
Dưới 50.000.000 | 0% |
Từ 50.000.000 đến dưới 100.000.000 | 2% |
Từ 100.000.000 đến dưới 200.000.000 | 3% |
Từ 200.000.000 trở lên | 5% |
Để Excel hiểu được bảng này, bạn chuyển nó thành bảng tra cứu ở vùng F1:G5. Cột F chỉ ghi mốc dưới của từng bậc (0, 50.000.000, 100.000.000, 200.000.000), cột G ghi tỷ lệ tương ứng. Danh sách nhân viên nằm ở cột A, doanh số ở cột B. Ô C2 dùng công thức:
=VLOOKUP(B2,$F$2:$G$5,2,TRUE)
Ô D2 tính tiền hoa hồng bằng =B2*C2, sau đó bạn kéo cả hai công thức xuống tới dòng 6.

Với TRUE, VLOOKUP tìm mốc lớn nhất không vượt quá giá trị cần tìm. Bạn theo dõi cách Excel tính cho từng người:
Trần Thị Bình đạt 120.000.000. Mốc lớn nhất không vượt quá con số này là 100.000.000, nên tỷ lệ là 3% và hoa hồng là 3.600.000.
Lê Minh Chi đạt đúng 200.000.000. Giá trị này trùng với mốc 200.000.000, nên tỷ lệ là 5%.
Phạm Quốc Dũng đạt 99.500.000. Con số này chưa tới mốc 100.000.000, nên Dũng chỉ được 2%.
Nguyễn Văn An đạt 35.000.000, thấp hơn mốc 50.000.000, nên rơi vào mốc 0 với tỷ lệ 0%.
3 quy tắc bắt buộc khi tìm gần đúng:
Cột đầu tiên phải sắp xếp tăng dần. Khi thử đảo thứ tự các mốc thành 100.000.000, 0, 200.000.000, 50.000.000, Excel trả về 0% cho Bình thay vì 3%, và không hề báo lỗi. Đây là lỗi nguy hiểm vì bạn khó phát hiện bằng mắt.
Dòng đầu tiên phải là mốc nhỏ nhất có thể xảy ra. Nếu bỏ dòng mốc 0, doanh số 35.000.000 của An nhỏ hơn mọi mốc còn lại, và VLOOKUP trả về lỗi #N/A.
Cột đầu tiên ghi mốc dưới của bậc. Bạn ghi "từ bao nhiêu", không ghi "đến bao nhiêu".
Cách làm này áp dụng cho nhiều bài toán bậc thang khác: xếp loại nhân viên theo điểm đánh giá, chiết khấu theo số lượng mua, đơn giá vận chuyển theo cân nặng. Bạn cũng có thể giải các bài này bằng nhiều hàm IF lồng nhau, nhưng bảng tra cứu kèm VLOOKUP dễ sửa hơn: khi công ty đổi chính sách, bạn chỉ sửa con số trong bảng mà không phải sửa công thức.
Cố định vùng tra cứu bằng dấu $
Trong các công thức ở trên, vùng tra cứu được viết là $G$2:$I$6 thay vì G2:I6. Dấu $ đứng trước chữ cái thì khóa cột, đứng trước con số thì khóa dòng, nên $G$2 nghĩa là ô G2 được khóa cả cột lẫn dòng. Nhờ vậy, khi bạn kéo công thức xuống dưới, vùng tra cứu giữ nguyên.
Nếu không có dấu $, Excel tự dịch mọi địa chỉ trong công thức xuống một dòng mỗi khi công thức đi xuống một dòng. Công thức ở ô C2 là =VLOOKUP(A2,G2:I6,2,FALSE), nhưng khi kéo xuống ô C3, nó trở thành =VLOOKUP(A3,G3:I7,2,FALSE). Vùng tra cứu lúc này bắt đầu từ dòng 3 và bỏ mất dòng 2, trong khi mã SP01 mà ô A3 cần tìm lại nằm đúng ở dòng 2.

Kết quả là có dòng ra đúng, có dòng ra #N/A, và lỗi này rất dễ bị bỏ sót vì vài dòng đầu thường vẫn đúng. Cách khóa vùng nhanh nhất là bôi đen phần G2:I6 trong thanh công thức rồi nhấn phím F4. Trên một số laptop, bạn cần nhấn Fn + F4. Bạn có thể xem thêm các phím tắt hay dùng khác trong bài Phím tắt Excel thông dụng.
Bạn chỉ khóa vùng tra cứu, không khóa ô A2. Ô A2 cần thay đổi thành A3, A4, A5 theo từng dòng để mỗi dòng tìm đúng mã hàng của mình.
Kết hợp VLOOKUP với IFERROR để thay lỗi #N/A
Khi mã cần tìm không có trong bảng danh mục, VLOOKUP trả về lỗi #N/A. Lỗi này là đúng về mặt logic, nhưng bảng báo cáo đầy #N/A trông rất rối, và các phép cộng phía sau cũng bị lỗi theo. Hàm IFERROR giúp bạn thay lỗi bằng một nội dung dễ hiểu hơn:
=IFERROR(VLOOKUP(A2,$G$2:$I$6,2,FALSE),"Không có mã")
IFERROR có 2 đối số. Nếu công thức VLOOKUP chạy bình thường, IFERROR giữ nguyên kết quả. Nếu VLOOKUP trả về lỗi, IFERROR hiện chữ "Không có mã" thay cho lỗi. Trong ảnh dưới, bảng danh mục vẫn ở vùng G2:I6 như ví dụ 1, còn cột A chứa 3 mã cần tra là SP03, SP07 và SP04.

Ảnh trên còn cho thấy lý do bạn luôn phải ghi FALSE ở đối số cuối. Cột D dùng công thức =VLOOKUP(A2,$G$2:$I$6,2), tức bỏ trống đối số cuối. Excel hiểu đây là tìm gần đúng, nên với mã SP07 không có trong bảng, công thức không báo lỗi mà trả về Mực in laser của mã SP05. Kết quả sai mà trông vẫn hợp lý là loại lỗi khó phát hiện nhất.
Hai lưu ý khi dùng IFERROR:
IFERROR che mọi loại lỗi, kể cả lỗi do bạn viết sai công thức như #REF!. Nếu chỉ muốn xử lý trường hợp không tìm thấy, bạn dùng IFNA (có từ Excel 2013):
=IFNA(VLOOKUP(A2,$G$2:$I$6,2,FALSE),"Không có mã"). Công thức này chỉ thay lỗi #N/A, các lỗi khác vẫn hiện ra để bạn sửa.Nếu ô mã hàng đang để trống, VLOOKUP cũng trả về #N/A. Với bảng nhập liệu có sẵn nhiều dòng trống, bạn dùng IF để bỏ qua dòng trống:
=IF(A2="","",VLOOKUP(A2,$G$2:$I$6,2,FALSE)). Bạn có thể đọc thêm cách viết hàm IF trong bài Hàm IF nhiều điều kiện trong Excel.
Dùng MATCH để lấy số thứ tự cột tự động
Đối số thứ ba của VLOOKUP là một con số gõ cố định, và điều này gây ra hai phiền toái. Thứ nhất, khi cần lấy nhiều cột, bạn phải sửa tay số cột ở từng công thức. Thứ hai, khi ai đó chèn thêm một cột vào giữa bảng tra cứu, số cột cũ không còn đúng. Ví dụ, nếu bạn chèn thêm cột "Đơn vị tính" vào giữa cột mã hàng và cột tên hàng của bảng danh mục, công thức lấy đơn giá của SP02 với số cột 3 sẽ trả về "Giấy A4 70gsm" thay vì 85.000, vì cột thứ 3 của vùng lúc này là cột tên hàng.
Hàm MATCH giải quyết cả hai việc. MATCH tìm vị trí của một giá trị trong một dòng hoặc một cột:
=MATCH(giá trị cần tìm, vùng tìm, 0)
Số 0 ở cuối nghĩa là tìm chính xác, giống FALSE của VLOOKUP. Khi dùng MATCH tìm tên tiêu đề trong dòng tiêu đề của bảng, bạn có ngay số thứ tự cột để đưa vào VLOOKUP.
Ví dụ: bảng nhân viên nằm ở vùng A1:E6, với dòng 1 là tiêu đề (Mã NV, Họ tên, Phòng ban, Lương cơ bản, Phụ cấp). Bạn làm một phiếu tra cứu ở cột G và cột H: ô H2 là nơi nhập mã nhân viên cần tra, còn các ô G3 tới G6 ghi tên thông tin cần lấy, viết đúng như tiêu đề của bảng. Ô H3 dùng công thức:
=VLOOKUP($H$2,$A$2:$E$6,MATCH(G3,$A$1:$E$1,0),FALSE)

Với ô H5, MATCH tìm chữ "Lương cơ bản" trong dòng tiêu đề A1:E1 và trả về 4, vì đây là cột thứ 4. VLOOKUP nhận số 4 và lấy lương cơ bản của nhân viên NV004 là 13.000.000. Bạn chỉ viết công thức một lần ở ô H3 rồi kéo xuống ô H6, MATCH tự đổi số cột thành 2, 3, 4, 5 cho từng dòng.
Có hai điểm bạn cần để ý trong công thức này. Ô $H$2 được khóa vì cả 4 dòng cùng tra một mã nhân viên. Dòng tiêu đề $A$1:$E$1 phải bắt đầu ở cùng cột với vùng tra cứu $A$2:$E$6, để MATCH và VLOOKUP đếm cột giống nhau. Nhờ MATCH tìm theo tên tiêu đề, khi có người chèn thêm cột vào bảng nhân viên, công thức vẫn lấy đúng cột cần lấy.
Tra cứu theo nhiều điều kiện bằng cột phụ
VLOOKUP chỉ nhận một giá trị cần tìm. Trong thực tế, nhiều bảng cần tới 2 điều kiện mới xác định được một dòng. Ví dụ, bảng giá trong ảnh bên dưới có mỗi mặt hàng 2 mức giá, một cho kênh bán lẻ và một cho kênh đại lý. Nếu chỉ tìm theo mã hàng SP02, VLOOKUP luôn dừng ở dòng đầu tiên nó gặp và trả về giá bán lẻ 85.000, dù bạn cần giá đại lý.
Cách giải là tạo một cột phụ ghép hai điều kiện lại thành một. Bảng giá có mã hàng ở cột B, kênh bán ở cột C, đơn giá ở cột D. Bạn dành cột A làm cột phụ và gõ vào ô A2:
=B2&"-"&C2
Sau khi kéo công thức xuống, cột A có các giá trị như "SP02-Bán lẻ", "SP02-Đại lý". Mỗi giá trị trong cột A là duy nhất, nên VLOOKUP tìm được đúng một dòng. Tiếp theo, ở bảng tra cứu bên phải, ô F2 chứa mã hàng, ô G2 chứa kênh, và ô H2 dùng công thức ghép hai điều kiện theo đúng cách đó:
=VLOOKUP(F2&"-"&G2,$A$2:$D$7,4,FALSE)

Công thức trên ghép SP02 và Đại lý thành "SP02-Đại lý", tìm chuỗi này ở cột A và lấy cột thứ 4 của vùng A2:D7, ra 80.000. Cột I trong ảnh dùng VLOOKUP theo một điều kiện là mã hàng, nên dòng 2 và dòng 3 đều lấy nhầm giá bán lẻ.
Khi làm cột phụ, bạn chú ý ba điểm sau:
Cột phụ phải nằm ở bên trái ngoài cùng của vùng tra cứu, vì VLOOKUP chỉ tìm ở cột đầu tiên.
Bạn nên chèn một ký tự ngăn cách như dấu "-" giữa hai điều kiện. Nếu ghép liền, hai cặp khác nhau có thể ra cùng một chuỗi, ví dụ "SP1" ghép với "2A" và "SP12" ghép với "A" đều ra "SP12A".
Thứ tự ghép trong công thức tra cứu phải giống thứ tự ghép ở cột phụ. Nếu cột phụ ghép mã hàng trước rồi tới kênh, công thức cũng phải ghép mã hàng trước.
Nếu dùng Excel 2021 hoặc Microsoft 365, bạn có thể bỏ cột phụ và dùng XLOOKUP ghép điều kiện ngay trong công thức: =XLOOKUP(F2&"-"&G2,$B$2:$B$7&"-"&$C$2:$C$7,$D$2:$D$7). Công thức này cho cùng kết quả 80.000.
Tìm theo một phần chữ bằng ký tự đại diện
Khi tìm chính xác (FALSE) với giá trị là chữ, VLOOKUP hiểu hai ký tự đặc biệt sau, gọi là ký tự đại diện:
Dấu * đại diện cho một đoạn chữ bất kỳ, dài bao nhiêu cũng được, kể cả không có chữ nào.
Dấu ? đại diện cho đúng một ký tự. Ví dụ, với bảng nhân viên ở phần MATCH, công thức
=VLOOKUP("NV00?",$A$2:$E$6,2,FALSE)khớp với mã NV001 ở dòng đầu và trả về Nguyễn Văn An.
Ký tự đại diện hữu ích khi bạn chỉ nhớ một phần tên. Ví dụ, danh sách khách hàng ở vùng A1:C6 có tên đầy đủ ở cột A, người liên hệ ở cột B và công nợ ở cột C. Bạn chỉ nhớ khách tên "Hòa Bình", nên gõ chữ này vào ô E2. Công thức ở ô G2 lấy công nợ của khách đó:
=VLOOKUP("*"&E2&"*",$A$2:$C$6,3,FALSE)
Phần "*"&E2&"*" tạo ra chuỗi "*Hòa Bình*", nghĩa là có đoạn chữ bất kỳ đứng trước và đứng sau "Hòa Bình". VLOOKUP tìm thấy "Công ty CP Thép Hòa Bình" và trả về công nợ 128.500.000. Ô F2 dùng công thức tương tự với số cột là 1 để hiện tên đầy đủ, giúp bạn kiểm tra lại xem Excel đã tìm đúng khách hàng chưa.

Một vài cách dùng khác của ký tự đại diện:
=VLOOKUP("Cửa hàng*",$A$2:$C$6,1,FALSE)tìm khách hàng có tên bắt đầu bằng "Cửa hàng". Kết quả là "Cửa hàng Văn phòng phẩm An Khang".=VLOOKUP("*Phú Quý",$A$2:$C$6,3,FALSE)tìm khách hàng có tên kết thúc bằng "Phú Quý" và trả về công nợ 18.300.000.Nếu gõ thiếu dấu *, công thức
=VLOOKUP("Hòa Bình",$A$2:$C$6,3,FALSE)trả về #N/A, vì trong bảng không có ô nào chỉ ghi đúng hai chữ "Hòa Bình".
Bạn cần nhớ rằng VLOOKUP vẫn chỉ trả về kết quả đầu tiên khớp mẫu. Danh sách trong ví dụ có hai cửa hàng, nhưng công thức "Cửa hàng*" chỉ trả về cửa hàng đứng trước. Vì vậy, bạn nên nhập từ khóa đủ riêng để chỉ một khách hàng khớp mẫu.
Các lỗi thường gặp khi dùng VLOOKUP và cách sửa
Phần lớn lỗi VLOOKUP rơi vào các nhóm trong bảng dưới đây. Khi gặp lỗi, bạn đối chiếu với bảng này để tìm nguyên nhân trước khi sửa công thức.
Bạn thấy | Nguyên nhân thường gặp | Cách sửa |
|---|---|---|
Lỗi #N/A | Giá trị cần tìm không có trong cột đầu tiên, có dấu cách thừa, hoặc một bên là số còn bên kia là số dạng chữ | Kiểm tra lại mã, dùng TRIM xóa dấu cách, đổi kiểu dữ liệu cho khớp, hoặc dùng IFERROR nếu mã thật sự không có |
Lỗi #REF! | Số thứ tự cột lớn hơn số cột của vùng tra cứu | Mở rộng vùng tra cứu cho đủ cột, hoặc sửa lại số cột |
Lỗi #VALUE! | Số thứ tự cột nhỏ hơn 1, ví dụ bằng 0 | Sửa số cột thành số từ 1 trở lên |
Lỗi #NAME? | Tên hàm bị gõ sai, ví dụ VLOKUP | Gõ lại đúng tên hàm VLOOKUP |
Kết quả sai nhưng không báo lỗi | Bỏ trống đối số cuối nên Excel tìm gần đúng, hoặc vùng tra cứu không khóa $ | Ghi FALSE ở đối số cuối và khóa vùng tra cứu bằng dấu $ |

Trong ảnh, bảng danh mục vẫn nằm ở vùng G2:I6 như ví dụ 1. Bảng mã số ở vùng K2:L4 có cột K lưu mã nhân viên dạng số (101, 102, 103) và cột L lưu họ tên. Hai nguyên nhân gây lỗi #N/A khó nhận ra nhất được giải thích kỹ hơn dưới đây.
Dấu cách thừa trong mã
Mã "SP03 " có một dấu cách ở cuối nhìn trên màn hình không khác gì "SP03", nhưng với Excel đây là hai giá trị khác nhau. Dấu cách thừa thường xuất hiện khi dữ liệu được dán từ phần mềm kế toán, email hoặc trang web. Bạn có hai cách xử lý:
Nếu dấu cách nằm ở giá trị cần tìm, bạn bọc ô đó bằng TRIM ngay trong công thức, ví dụ
=VLOOKUP(TRIM(A2),$G$2:$I$6,2,FALSE).Nếu dấu cách nằm trong bảng tra cứu, bạn cần làm sạch chính bảng đó, vì TRIM trong công thức không sửa được dữ liệu của bảng. Bạn tạo một cột phụ với công thức
=TRIM(G2)và kéo xuống hết bảng. Sau đó, bạn copy cột phụ, dán giá trị (chuột phải, chọn Paste Special > Values) đè lên cột mã cũ, rồi xóa cột phụ.
Để kiểm tra một ô có dấu cách thừa hay không, bạn dùng hàm =LEN(G2) để đếm số ký tự. Mã "SP03" có 4 ký tự, nếu LEN trả về 5 thì ô đó đang có một ký tự thừa.
Số lưu dưới dạng chữ
Với Excel, số 102 và chữ "102" là hai giá trị khác nhau. Lỗi này hay gặp khi mã nhân viên, mã khách hàng là các con số và được xuất ra từ phần mềm khác. Ô chứa số dạng chữ thường có hình tam giác xanh nhỏ ở góc trên bên trái và căn lề trái. Bạn có thể kiểm tra chắc chắn bằng hàm =ISTEXT(A2): nếu kết quả là TRUE, ô A2 đang chứa chữ.
Khi bảng tra cứu lưu số còn ô cần tìm là chữ, bạn đổi chữ thành số bằng VALUE:
=VLOOKUP(VALUE(A2),$K$2:$L$4,2,FALSE).Khi bảng tra cứu lưu mã dạng chữ còn ô cần tìm là số, ví dụ bảng ở vùng N2:O4, bạn đổi số thành chữ bằng cách nối với một chuỗi rỗng:
=VLOOKUP(A2&"",$N$2:$O$4,2,FALSE).
Bảng tra cứu có thêm dòng mới
Vùng $G$2:$I$6 cố định đúng 5 dòng. Khi bạn thêm mặt hàng mới ở dòng 7, VLOOKUP không nhìn thấy dòng này và trả về #N/A. Có hai cách để vùng tra cứu tự mở rộng. Cách thứ nhất là tra cứu trên cả cột, ví dụ =VLOOKUP(A2,$G:$I,2,FALSE). Cách thứ hai là chuyển bảng danh mục thành Table bằng phím Ctrl + T. Khi bạn bôi đen vùng dữ liệu của Table lúc viết công thức, Excel ghi tên Table (ví dụ Table1) vào công thức, và vùng này tự mở rộng mỗi khi bạn thêm dòng mới ngay bên dưới Table. Với cách tra cứu trên cả cột, bạn cần đảm bảo các ô phía dưới bảng ở cột G không chứa dữ liệu khác.
Giới hạn của VLOOKUP và khi nào nên dùng XLOOKUP hoặc INDEX/MATCH
VLOOKUP dễ học và đủ dùng cho phần lớn việc tra cứu hằng ngày, nhưng hàm này có một số giới hạn mà bạn cần biết để chọn đúng công cụ:
VLOOKUP chỉ lấy được dữ liệu bên phải cột tìm kiếm. Trong bảng danh mục, nếu bạn biết tên hàng "Sổ tay A5" và cần tra ngược ra mã hàng ở cột G, VLOOKUP không làm được vì cột mã nằm bên trái cột tên. Công thức INDEX/MATCH
=INDEX($G$2:$G$6,MATCH("Sổ tay A5",$H$2:$H$6,0))trả về đúng mã SP04. Trong công thức này, MATCH tìm ra "Sổ tay A5" nằm ở vị trí thứ 4 của cột H, rồi INDEX lấy giá trị thứ 4 của cột G.VLOOKUP chỉ trả về giá trị đầu tiên tìm thấy. Khi bảng có nhiều dòng trùng giá trị cần tìm, các dòng phía sau bị bỏ qua. Bạn dùng cột phụ như phần trên, hoặc dùng hàm FILTER nếu cần lấy tất cả kết quả. Ví dụ,
=FILTER($D$2:$D$7,$B$2:$B$7="SP02")trả về cả hai mức giá 85.000 và 80.000 của mã SP02. Hàm FILTER chỉ có trong Excel 2021 và Microsoft 365.Số thứ tự cột là một con số gõ cố định. Khi bảng bị chèn thêm cột, công thức có thể lấy nhầm cột. Bạn khắc phục bằng MATCH như đã trình bày ở trên.
VLOOKUP mặc định tìm gần đúng. Nếu bạn quên ghi FALSE, công thức có thể trả về kết quả sai mà không báo lỗi.
VLOOKUP không phân biệt chữ hoa, chữ thường. Công thức
=VLOOKUP("sp03",$G$2:$I$6,2,FALSE)vẫn tìm ra mã SP03. Điều này thường có lợi, nhưng bạn cần lưu ý nếu hệ thống mã của công ty phân biệt chữ hoa và chữ thường.
Bảng dưới đây giúp bạn chọn hàm phù hợp:
Tiêu chí | VLOOKUP | INDEX/MATCH | XLOOKUP |
|---|---|---|---|
Phiên bản Excel | Mọi phiên bản | Mọi phiên bản | Excel 2021, 2024, Microsoft 365 |
Lấy dữ liệu bên trái cột tìm | Không | Có | Có |
Mặc định khi bỏ trống kiểu tìm | Tìm gần đúng | MATCH tìm gần đúng nếu bỏ trống số 0 | Tìm chính xác |
Xử lý khi không tìm thấy | Cần thêm IFERROR hoặc IFNA | Cần thêm IFERROR hoặc IFNA | Có sẵn đối số thứ tư |
Cách viết | Ngắn gọn, dễ học | Cần hiểu cả 2 hàm | Ngắn gọn, dễ học |
Nếu Excel của bạn có XLOOKUP, bạn nên dùng XLOOKUP cho các file mới. Ví dụ, =XLOOKUP("Sổ tay A5",$H$2:$H$6,$G$2:$G$6) tra ngược ra mã SP04, còn =XLOOKUP("SP07",$G$2:$G$6,$H$2:$H$6,"Không có mã") tự hiện "Không có mã" mà không cần IFERROR. Bạn xem cách dùng đầy đủ trong bài Hàm XLOOKUP trong Excel.
Dù vậy, bạn vẫn nên nắm vững VLOOKUP, vì rất nhiều file ở công ty đang dùng hàm này và không phải đồng nghiệp nào cũng dùng Excel có XLOOKUP.
Câu hỏi thường gặp về hàm VLOOKUP
Làm sao dùng VLOOKUP lấy dữ liệu từ sheet khác?
Bạn viết thêm tên sheet và dấu chấm than trước vùng tra cứu. Ví dụ, khi bảng danh mục nằm ở vùng A2:C6 của sheet DanhMuc, công thức là =VLOOKUP(A2,DanhMuc!$A$2:$C$6,2,FALSE). Cách đơn giản nhất là khi gõ tới đối số thứ hai, bạn bấm sang sheet DanhMuc rồi bôi đen vùng cần tra, Excel sẽ tự điền tên sheet. Nếu tên sheet có dấu cách, Excel tự đặt tên sheet trong cặp dấu nháy đơn.
Vì sao VLOOKUP báo #N/A dù bạn nhìn thấy mã trong bảng?
Nguyên nhân thường gặp nhất là dấu cách thừa hoặc số lưu dưới dạng chữ. Bạn dùng LEN để đếm số ký tự và ISTEXT để kiểm tra kiểu dữ liệu ở cả ô cần tìm lẫn ô trong bảng. Nếu hai bên khác nhau, bạn sửa theo hướng dẫn ở phần lỗi thường gặp.
Khi nào dùng TRUE, khi nào dùng FALSE?
Bạn dùng FALSE khi tìm một mã, một tên cụ thể, và đây là trường hợp của phần lớn công việc. Bạn chỉ dùng TRUE khi tra theo bậc thang như hoa hồng, xếp loại, chiết khấu, và khi đó cột đầu tiên của bảng phải sắp xếp tăng dần.
Nên học VLOOKUP hay XLOOKUP trước?
Bạn nên học VLOOKUP trước, vì hàm này có trong mọi phiên bản Excel và giúp bạn hiểu cách tra cứu hoạt động. Sau đó, bạn học thêm XLOOKUP để dùng cho các file mới nếu Excel của bạn có hàm này.
Bạn muốn dùng thành thạo VLOOKUP và các hàm tra cứu?
VLOOKUP là một trong những hàm đầu tiên người đi làm cần nắm, nhưng hàm này phát huy tác dụng nhất khi bạn kết hợp được với IF, IFERROR, MATCH và các hàm tính tổng có điều kiện như SUMIFS. Bạn có thể xem thứ tự học các nhóm hàm này trong bài Lộ trình học Excel từ cơ bản đến nâng cao.
Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.