Bài 1 · Chương 9: Hàm tìm kiếm

VLOOKUP: tra cứu chính xác theo mã

Giải thích bốn đối số của VLOOKUP, tra cứu chính xác (FALSE hoặc 0) đơn giá theo mã hàng từ bảng danh mục, cố định bảng tra bằng $ để sao chép công thức và chẩn đoán các nguyên nhân #N/A thường gặp.

15 phút2 bài tậpTrung cấp

Mục lục bài

Mục tiêu
Ví dụ có lời giải
Điền tiếp công thức
Tìm chỗ sai
Kiểm tra nhanh
Lỗi hay gặp
Trên Excel thật
Luyện tập (2 bài tập)

Ôn lại trước khi học

Thử nhớ lại trước, rồi mới xem đáp án.

Từ bài: Hàm IF: chọn một trong hai kết quả
Khi ô C2 chứa số 5, biểu thức C2>=5 cho kết quả gì? Còn C2>5 thì sao?
Xem đáp án
Biểu thức C2>=5 cho TRUE, vì lớn hơn hoặc bằng bao gồm cả 5. Biểu thức C2>5 cho FALSE, vì 5 không lớn hơn 5. So sánh hai giá trị luôn cho TRUE hoặc FALSE.
Từ bài: Địa chỉ tuyệt đối ($) và phím F4
Viết địa chỉ tuyệt đối của vùng A2:C9, cố định cả hai ô góc. Phím nào thêm dấu $ nhanh?
Xem đáp án
Địa chỉ là $A$2:$C$9. Chọn địa chỉ trong thanh công thức rồi nhấn F4 để đổi qua lại giữa các kiểu địa chỉ.
Từ bài: Hàm SUM, AutoSum và dấu phân cách ; hay ,
Trong =SUM(B2:B6,D2:D6), dấu hai chấm và dấu phẩy khác nhau thế nào?
Xem đáp án
Dấu hai chấm nối hai góc thành một vùng, như B2:B6. Dấu phẩy ngăn cách các đối số, nên công thức có hai đối số là hai vùng.

Mục tiêu

Sau bài này bạn có thể:

Giải thích bốn đối số của VLOOKUP.
Tra cứu chính xác (FALSE hoặc 0) đơn giá theo mã hàng từ bảng danh mục.
Cố định bảng tra bằng $ để sao chép công thức.
Chẩn đoán các nguyên nhân #N/A thường gặp.

Ví dụ có lời giải

Hóa đơn chỉ ghi Mã hàng và Số lượng. Đơn giá nằm ở một bảng danh mục riêng, trên trang tính Bảng mã. Hàm tra cứu VLOOKUP đọc mã hàng, tìm mã đó trong bảng danh mục rồi trả về đơn giá. Với mã ở ô B2, Đơn giá ở D2 là =VLOOKUP(B2,'Bảng mã'!$A$2:$C$5,3,FALSE).

Cú pháp=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_valuegiá trị cần tìm
Bắt buộc. Giá trị cần tra, và nó phải nằm ở cột đầu tiên của vùng bạn chọn.
table_arraybảng tra
Bắt buộc. Vùng ô mà VLOOKUP tìm giá trị cần tra và lấy kết quả.
col_index_numsố thứ tự cột
Bắt buộc. Số cột chứa kết quả, đếm từ cột ngoài cùng bên trái của bảng tra, cột đó là 1.
range_lookupkiểu tìmTùy chọn
Tùy chọn. FALSE (hoặc 0) tìm chính xác. TRUE tìm gần đúng, và là kiểu mặc định khi bạn bỏ trống đối số này.

Hóa đơn nằm trên trang tính chính, còn danh mục nằm trên trang tính Bảng mã.

Trang tính chính:

	A	B	C	D
1	Mã HĐ	Mã hàng	Số lượng	Đơn giá
2	HD001	GIA01	5
3	HD002	BUT01	10
4	HD003	SOT01	3
5	HD004	KEP01	20

Trang tính Bảng mã:

	A	B	C
1	Mã hàng	Tên hàng	Đơn giá
2	BUT01	Bút bi xanh (hộp 20 cây)	42,000
3	GIA01	Giấy A4 70gsm (ram 500 tờ)	78,000
4	KEP01	Kẹp giấy (hộp 100 cái)	9,000
5	SOT01	Sổ tay bìa cứng A5	28,000

Lấy Đơn giá từ Bảng mã theo Mã hàng

Làm gì: Chọn ô D2. Giá trị cần tìm là mã hàng ở B2. Bảng tra là 'Bảng mã'!A2:C5, và mã hàng nằm ở cột đầu tiên của bảng đó, cột A.
Vì sao: VLOOKUP chỉ tìm giá trị cần tra ở cột đầu tiên của bảng tra, nên cột Mã hàng phải đứng đầu. Tên trang tính có dấu cách thì đặt trong dấu nháy đơn.
Chỗ hay sai
Làm gì: Đếm cột chứa Đơn giá, tính từ cột đầu của bảng tra: A là 1, B là 2, C là 3. Vậy số thứ tự cột là 3.
Vì sao: Số thứ tự cột đếm từ cột ngoài cùng bên trái của bảng tra, không đếm từ cột A của trang tính. Chọn số lớn hơn số cột của bảng tra thì được lỗi #REF!.
Chỗ hay sai
Làm gì: Gõ FALSE làm đối số cuối.
Vì sao: FALSE yêu cầu khớp chính xác. Nếu bạn bỏ trống đối số này, Excel mặc định tìm gần đúng, và kiểu đó giả định cột đầu của bảng đã được sắp xếp. Với mã hàng, bạn cần khớp chính xác.
Chỗ hay sai
Làm gì: Cố định bảng tra thành 'Bảng mã'!$A$2:$C$5, nhấn Enter, rồi sao chép D2 xuống D5. Các ô hiện: 78,000, 42,000, 28,000 và 9,000.
Vì sao: Dấu $ giúp công thức luôn nhìn vào cùng một vùng tra khi sao chép xuống. Không có $ thì bảng tra trượt xuống từng hàng. GIA01 là 78,000, BUT01 là 42,000, SOT01 là 28,000 và KEP01 là 9,000.

Tự hỏi mình

Nếu bảng tra có thêm một cột Nhóm hàng chen vào giữa Tên hàng và Đơn giá, số thứ tự cột sẽ đổi thành bao nhiêu?
Vì sao cột Mã hàng phải là cột đầu tiên của bảng tra?

Khi VLOOKUP trả về lỗi

Mỗi lỗi của VLOOKUP cho bạn biết một nguyên nhân cụ thể. Đọc bảng dưới để biết cần kiểm tra gì.

Kết quả	Nguyên nhân hay gặp	Cách kiểm tra
`#N/A`	Dùng FALSE và Excel không tìm thấy đúng giá trị cần tra trong cột đầu của bảng tra.	Mã có thật trong bảng không? Có gõ sai, hoặc thừa dấu cách ở đầu hay cuối không?
`#N/A`	Dùng TRUE và giá trị cần tra nhỏ hơn giá trị nhỏ nhất ở cột đầu.	Đổi sang FALSE nếu bạn cần khớp chính xác.
`#REF!`	Số thứ tự cột lớn hơn số cột của bảng tra.	Đếm lại số cột của bảng tra, bắt đầu từ cột đầu là 1.
`#N/A` hoặc kết quả không như mong đợi	Bảng tra không bắt đầu từ cột chứa giá trị cần tra, nên VLOOKUP tìm trong một cột khác.	Chọn bảng tra bắt đầu từ cột chứa mã.

Khi cần khớp chính xác, hãy kiểm tra dữ liệu ở cột đầu của bảng tra: không có dấu cách thừa ở đầu hay cuối, và không có ký tự không in được. Theo Microsoft, trong các trường hợp đó VLOOKUP có thể trả về giá trị không mong đợi, Khi đó hãy sửa dữ liệu trước khi sửa công thức.

Điền tiếp công thức

Các câu sau đều chép công thức xuống nhiều hàng, nên bảng tra luôn có dấu $. Mỗi câu bỏ trống nhiều hơn câu trước.

Câu 1. Hóa đơn có Mã hàng ở B2, bảng tra ở 'Bảng mã'!A2:C5. Lấy Đơn giá (cột thứ 3 của bảng), khớp chính xác.

Câu 2. Trang DanhSach có Mã NV ở cột A và Họ tên ở cột B, từ hàng 2 đến hàng 20. Lấy Họ tên theo Mã NV ở A2, khớp chính xác.

Câu 3. Hóa đơn có Mã hàng ở C2, và Bảng mã nằm ở 'Bảng mã'!A2:C9 (Mã hàng, Tên hàng, Đơn giá). Viết công thức lấy Đơn giá, khớp chính xác, rồi sao chép xuống.

Tìm chỗ sai

Cột Đơn giá của hóa đơn dùng công thức dưới đây. Mã hàng cần khớp chính xác, nhưng có một phần của công thức đặt sai kiểu tìm. Bấm vào phần bạn nghĩ là sai.

Tìm chỗ sai

Công thức này có một chỗ sai. Bấm vào phần bạn nghĩ là sai.

Sau đây là phần luyện tập, gồm hai bài: tra Tên hàng và Đơn giá từ trang tính Bảng mã, rồi tính Thành tiền khi cột Đơn giá còn trống.

Đọc xong, giờ thử

Luyện tập ngay (2 bài tập)

Làm trực tiếp trên lưới trong trình duyệt. Mỗi lần bấm chấm bài, DeskLab kiểm tra kết quả và gợi ý nếu bạn chưa làm đúng.

Bắt đầu luyện tập

Chấm tự động ngay trên máy bạn. Không cần đăng nhập. Tiến độ lưu trên trình duyệt này.

Kiểm tra nhanh

Bảng tra nằm ở D2:F9, với D là Mã, E là Tên, F là Đơn giá. Muốn lấy Đơn giá, số thứ tự cột là bao nhiêu?

362

Bạn bỏ trống đối số thứ tư của VLOOKUP. Excel dùng kiểu tìm nào?

Tìm gần đúng (TRUE)Tìm chính xác (FALSE)Báo lỗi vì thiếu đối số

VLOOKUP dùng FALSE và trả về #N/A. Điều đó cho biết điều gì?

Excel không tìm thấy đúng giá trị cần tra trong cột đầu của bảng traSố thứ tự cột lớn hơn số cột của bảng traBảng tra đã được sắp xếp

Cột Mã hàng của bảng tra có vài mã dính dấu cách ở cuối. Microsoft khuyên gì cho dữ liệu ở cột đầu của bảng tra?

Bảo đảm cột đầu không có dấu cách thừa ở đầu hoặc cuốiLuôn đổi đối số cuối thành TRUEĐổi tên trang tính chứa bảng tra

Lỗi hay gặp

Bỏ trống đối số thứ tư và kết quả có thể sai khi bảng chưa sắp xếp.
Nguyên nhân
Khi bỏ trống range_lookup, Excel mặc định tìm gần đúng (TRUE), và kiểu này giả định cột đầu của bảng đã sắp xếp.
Cách sửa
Viết đối số cuối là FALSE (hoặc 0) khi cần khớp chính xác: =VLOOKUP(C2,'Bảng mã'!$A$2:$C$9,3,FALSE).
Sao chép công thức xuống thì các hàng dưới ra #N/A hoặc sai.
Nguyên nhân
Bảng tra không có dấu $ nên trượt xuống từng hàng khi sao chép.
Cách sửa
Cố định bảng tra bằng $, ví dụ 'Bảng mã'!$A$2:$C$9.
Công thức trả về nhầm cột, hoặc ra lỗi #REF!.
Nguyên nhân
Số thứ tự cột đếm sai, ví dụ đếm từ cột A của trang tính thay vì từ cột đầu của bảng tra.
Cách sửa
Đếm cột trong bảng tra, bắt đầu từ cột đầu của bảng là 1.
VLOOKUP không tìm thấy giá trị mà bạn thấy rõ trong bảng.
Nguyên nhân
Giá trị cần tìm không nằm ở cột đầu tiên của bảng tra, hoặc dữ liệu có dấu cách thừa.
Cách sửa
Chọn bảng tra bắt đầu từ cột chứa mã, rồi kiểm tra không có dấu cách thừa ở đầu hay cuối.

Trên Excel thật

Kiểu tìm và #N/A: Lưới DeskLab và Excel cho cùng kết quả: bỏ trống đối số thứ tư thì tìm gần đúng, còn dùng FALSE mà không thấy giá trị thì ra #N/A.
Tên trang tính: Trang tính Bảng mã có dấu cách, nên trong cả lưới và Excel bạn viết 'Bảng mã'! với dấu nháy đơn quanh tên.
Dữ liệu bài tập: Bảng mã trong bài tập không xếp theo thứ tự mã, và mọi mã trên hóa đơn đều có trong bảng. Theo Microsoft, kiểu tìm gần đúng cần cột đầu đã sắp xếp, nên với dữ liệu như vậy hãy luôn viết FALSE khi cần khớp chính xác.