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

XLOOKUP: cách tra cứu mới

Viết XLOOKUP với vùng tìm và vùng trả về riêng, dùng if_not_found để hiện thông báo thay cho #N/A, so sánh với VLOOKUP và biết khi nào dùng VLOOKUP vì tệp phải mở trên Excel 2016 hoặc 2019. XLOOKUP cần Excel 2021/365.

12 phút3 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 (3 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: COUNTIF và SUMIF: đếm và cộng theo một điều kiện
Bạn cần đếm các đơn có giá trị lớn hơn 5000000 bằng hàm đếm theo điều kiện. Điều kiện viết thế nào, và vì sao?
Xem đáp án
Điều kiện viết là ">5000000", đặt trong dấu nháy kép. Điều kiện có ký hiệu so sánh phải đặt trong dấu nháy kép, và số gõ liề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ì, và vì sao?
Xem đáp án
Kết quả là FALSE. Dấu <> nghĩa là khác (không bằng), mà C2 bằng 5 nên điều kiện khác 5 sai.
Từ bài: Địa chỉ tuyệt đối ($) và phím F4
Khi sao chép công thức tra cứu xuống nhiều hàng, vì sao bảng tra cần dấu $?
Xem đáp án
Địa chỉ có dấu $ giữ nguyên khi sao chép, nên mọi hàng nhìn vào cùng một bảng tra. Không có $ thì bảng tra trượt xuống theo từng hàng.

Mục tiêu

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

Viết XLOOKUP với vùng tìm (lookup_array) và vùng trả về (return_array) riêng.
Dùng if_not_found để hiện thông báo thay cho #N/A.
So sánh XLOOKUP với VLOOKUP: mặc định khớp chính xác, tra được sang trái.
Dùng VLOOKUP (bài L09-01) khi tệp phải mở trên Excel 2016 hoặc 2019.

Phiên bản: XLOOKUP dùng được trên Excel 2021/365. Theo Microsoft, hàm này không có trên Excel 2016 và Excel 2019. Nếu tệp của bạn phải mở trên các bản đó, hãy dùng VLOOKUP, hàm bạn đã học ở bài L09-01.

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

Vẫn là hóa đơn và bảng danh mục ở bài VLOOKUP. Lần này bạn tra đơn giá bằng XLOOKUP: bạn chọn riêng vùng để tìm mã và vùng để lấy đơn giá, nên không phải đếm số thứ tự cột. Với mã ở ô B2, Đơn giá ở D2 là =XLOOKUP(B2,'Bảng mã'!$A$2:$A$5,'Bảng mã'!$C$2:$C$5).

Cú pháp=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

lookup_valuegiá trị cần tìm
Bắt buộc. Giá trị cần tìm, ví dụ mã hàng.
lookup_arrayvùng tìm
Bắt buộc. Mảng hoặc vùng ô để tìm giá trị cần tìm.
return_arrayvùng trả về
Bắt buộc. Mảng hoặc vùng ô để lấy kết quả.
if_not_foundchữ hiện khi không tìm thấyTùy chọn
Tùy chọn. Nếu không tìm thấy, hàm trả về chữ bạn đưa vào đây. Nếu bỏ trống thì hàm trả về #N/A.
match_modekiểu khớpTùy chọn
Tùy chọn. Khi bỏ trống là 0: khớp chính xác, và nếu không có thì trả về #N/A.
search_modechiều tìmTùy chọn
Tùy chọn. Bài này không dùng đối số này.

Hóa đơn và bảng danh mục như bài trước.

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á bằng XLOOKUP, rồi xử lý mã không có trong bảng

Làm gì: Chọn ô D2. Giá trị cần tìm là mã hàng ở B2.
Vì sao: Đối số thứ nhất của XLOOKUP là giá trị cần tìm, giống VLOOKUP.
Chỗ hay sai
Làm gì: Chọn vùng tìm: cột Mã hàng của Bảng mã, 'Bảng mã'!$A$2:$A$5. Chọn vùng trả về: cột Đơn giá, 'Bảng mã'!$C$2:$C$5.
Vì sao: Mỗi mã ở vùng tìm cho ra kết quả ở ô cùng hàng của vùng trả về. Vì vậy hãy chọn hai vùng bắt đầu và kết thúc ở cùng hàng, ở đây là hàng 2 đến hàng 5. Bạn không đếm số thứ tự cột như ở VLOOKUP.
Làm gì: 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: XLOOKUP mặc định khớp chính xác, nên không cần đối số kiểu tìm như FALSE của VLOOKUP. Hai vùng đã có dấu $, nên khi sao chép chúng không trượt xuống.
Làm gì: Xử lý mã không có trong bảng: thêm đối số thứ tư, ví dụ =XLOOKUP(B2,'Bảng mã'!$A$2:$A$5,'Bảng mã'!$C$2:$C$5,"Không có mã"). Nếu mã ở B2 không có trong bảng, ô hiện chữ Không có mã.
Vì sao: Nếu thiếu đối số if_not_found và không tìm thấy mã, hàm trả về #N/A. Đối số này cho bạn đặt chữ hiện ra thay cho #N/A. Chữ nằm trong dấu nháy kép.

Tự hỏi mình

Vì sao ở XLOOKUP bạn không cần đếm số thứ tự cột trong bảng tra?
Hóa đơn gửi khách nên hiện #N/A hay hiện chữ Không có mã? Vì sao?

XLOOKUP và VLOOKUP khác nhau thế nào

Điểm so sánh	VLOOKUP	XLOOKUP
Phiên bản	Mọi phiên bản	Excel 2021/365
Chọn kết quả	Số thứ tự cột trong bảng tra	Vùng trả về riêng
Khi bỏ trống kiểu tìm	Tìm gần đúng	Khớp chính xác
Tra sang trái	Giá trị cần tìm phải ở cột đầu của bảng tra	Được, vùng trả về nằm bên trái hay phải đều được
Không tìm thấy	`#N/A` khi tìm chính xác	`#N/A`, hoặc chữ bạn đặt ở `if_not_found`

Ví dụ tra sang trái: bảng mã có Tên hàng ở cột B và Mã hàng ở cột A, bên trái cột tên. Muốn lấy mã từ tên hàng ở ô E2, bạn viết =XLOOKUP(E2,'Bảng mã'!$B$2:$B$5,'Bảng mã'!$A$2:$A$5). Với VLOOKUP, giá trị cần tìm phải nằm ở cột đầu của bảng tra, nên ví dụ này cần thêm một cách khác. Cách tra sang trái trên Excel cũ sẽ học ở bài L09-04.

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

Các câu sau chép xuống nhiều hàng, nên các vùng đều 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 mã ở 'Bảng mã'!A2:C5 (Mã hàng ở cột A, Đơn giá ở cột C). Tra Đơn giá, và khi không tìm thấy mã thì hiện chữ Không có mã.

Câu 2. Trang DanhSach có Phòng ban ở cột A và Mã NV ở cột B, từ hàng 2 đến hàng 20. Lấy Phòng ban theo Mã NV ở E2.

Câu 3. Lấy Tên hàng theo Mã hàng ở B2, từ Bảng mã A2:C9 (cột A là Mã hàng, cột B là Tên hàng, cột C là Đơn giá).

Tìm chỗ sai

Cột Đơn giá của hóa đơn dùng công thức dưới đây, và kết quả sai. 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 ba bài: tra Tên hàng và Đơn giá, tra mã từ tên hàng (tra sang trái), và hiện chữ khi mã không có trong bảng.

Đọc xong, giờ thử

Luyện tập ngay (3 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

Tệp của bạn phải mở được trên cả Excel 2016 và Excel 2019. Bạn nên làm gì?

Dùng VLOOKUP thay cho XLOOKUPDùng XLOOKUP vì nó là hàm mới hơnĐổi tên trang tính chứa bảng tra

Vùng tìm là 'Bảng mã'!$A$2:$A$9. Vùng nào hợp lý làm vùng trả về để lấy Đơn giá, nằm ở cột C?

'Bảng mã'!$C$2:$C$9'Bảng mã'!$C$2:$C$10'Bảng mã'!$B$2:$B$9

Bạn bỏ trống đối số match_mode của XLOOKUP. Kiểu tìm nào được dùng?

Khớp chính xácKhớp gần đúngHàm báo lỗi vì thiếu đối số

Mã hàng không có trong bảng và công thức XLOOKUP không có đối số thứ tư. Ô hiện gì?

#N/A0Ô trống

Lỗi hay gặp

Kết quả XLOOKUP sai vì vùng tìm và vùng trả về lệch nhau một hàng.
Nguyên nhân
Hai vùng không bắt đầu và kết thúc ở cùng hàng, ví dụ A2:A9 và C3:C10, nên mỗi mã lấy kết quả của hàng bên dưới.
Cách sửa
Chọn hai vùng cùng hàng đầu và hàng cuối: =XLOOKUP(C2,'Bảng mã'!$A$2:$A$9,'Bảng mã'!$C$2:$C$9).
Gửi tệp cho người dùng Excel 2016 hoặc 2019 và hàm XLOOKUP không dùng được.
Nguyên nhân
XLOOKUP không có trên Excel 2016 và Excel 2019.
Cách sửa
Dùng VLOOKUP (bài L09-01) khi tệp phải mở trên các bản đó.
Viết số thứ tự cột như 3 vào XLOOKUP, quen tay từ VLOOKUP.
Nguyên nhân
XLOOKUP nhận vùng trả về chứ không nhận số thứ tự cột. Hai hàm có thứ tự đối số khác nhau.
Cách sửa
Thay số thứ tự cột bằng vùng trả về: =XLOOKUP(B2,'Bảng mã'!$A$2:$A$5,'Bảng mã'!$C$2:$C$5).

Trên Excel thật

Phiên bản: Lưới DeskLab luôn tính được XLOOKUP, nhưng Excel 2016 và Excel 2019 không có hàm này. Hãy kiểm tra phiên bản Excel của người nhận trước khi gửi tệp.
Kết quả: Lưới DeskLab và Excel khớp chính xác theo mặc định. Không tìm thấy thì ra #N/A, hoặc ra chữ bạn đặt ở if_not_found.