HƯỚNG DẪN SỬ DỤNG HÀM XLOOKUP THAY THẾ HÀM VLOOKUP và HLOOKUP

Nếu bạn thường xuyên sử dụng hàm VLOOKUP và HLOOKUP trong Excel để dò tìm dữ liệu, có lẽ bạn đã trải qua không ít rắc rối vì những nhược điểm khó chịu của các hàm tham chiếu này. Tin mừng cho bạn là trong phiên bản cập nhật Office 365 Insiders, Excel đã cho ra mắt hàm XLOOKUP để bù đắp cho sự bất tiện khi dùng hàm VLOOKUP và HLOOKUP.

Tuyệt đỉnh Excel - Trở thành bậc thầy Excel trong 16 giờ

Trong bài viết ngày hôm nay, bạn hãy cùng Gitiho đi tìm hiểu cách sử dụng hàm XLOOKUP thay thế các hàm LOOKUP cũ qua 11 ví dụ cụ thể tương ứng với 11 cách ứng dụng nhé.

Xem thêm: Giới thiệu các hàm trong Excel và các ví dụ minh họa dễ hiểu

Giới thiệu hàm XLOOKUP trong Excel

Nếu bạn chưa biết thì tên gọi của hàm tìm kiếm trong Excel mới này được đặt dựa vào khả năng tìm kiếm đa chiều của nó - X - hay còn được miêu tả là khả năng tham chiếu dữ liệu theo cả chiều ngang và chiều dọc. Với khả năng đặc biệt này, hàm XLOOKUP hoàn toàn có thể thay thế cả hàm VLOOKUP và HLOOKUP.

Cho đến hiện tại, hàm XLOOKUP chỉ được thiết lập trên phiên bản Excel 365. Do đó, nếu bạn chưa cài đặt Office 365 cho hệ thống của mình thì hãy tham khảo bài viết dưới đây nhé.

Xem thêm: Hướng dẫn các ưu điểm của Microsoft Office 365 và cách cài đặt

Cú pháp hàm XLOOKUP

Hàm XLOOKUP trong Excel có cú pháp như sau:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],[match_mode], [search_mode])

Trong đó:

Các tham số cơ bản và bắt buộc của hàm XLOOKUP là:

• lookup_value - Đối tượng cần tìm kiếm
• lookup_array - Phạm vi cần tiến hành tìm kiếm
• return_array - Phạm vi trả về dữ liệu tương ứng

Các tham số nâng cao và tùy chọn của hàm XLOOKUP là:

• if_not_found - Giá trị trả về nếu hàm không tìm thấy đối tượng trong phạm vi tìm kiếm
• match_mode - Kiểu khớp
  • match_mode = 0 - Khớp chính xác đối tượng cần tìm. Nếu không tìm thấy giá trị khớp hoàn toàn, hàm trả về lỗi #N/A. Đây là kiểu khớp mặc định.
  • match_mode = -1 - Khớp chính xác đối tượng cần tìm. Nếu không tìm thấy giá trị khớp hoàn toàn, hàm trả về giá trị nhỏ hơn gần nhất.
  • match_mode = 1 - Khớp chính xác đối tượng cần tìm. Nếu không tìm thấy giá trị khớp hoàn toàn, hàm trả về giá trị lớn hơn gần nhất.
  • match_mode = 2 - Khớp với một ký tự đại diện (wildcard)
• search_mode - Kiểu tìm kiếm
  • search_mode = 1 - Tìm kiếm từ mục đầu tiên. Đây là kiểu tìm kiếm mặc định.
  • search_mode = -1 - Tìm kiếm từ mục cuối cùng đổ ngược lên.
  • search_mode = 2 - Tìm kiếm nhị phân dựa trên phạm vi tìm kiếm được sắp xếp theo thứ tự tăng dần. Nếu phạm vi tìm kiếm không được sắp xếp theo đúng thứ tự, hàm trả về kết quả không hợp lệ.
  • search_mode = -2 - Tìm kiếm nhị phân dựa trên phạm vi tìm kiếm được sắp xếp theo thứ tự giảm dần. Nếu phạm vi tìm kiếm không được sắp xếp theo đúng thứ tự, hàm trả về kết quả không hợp lệ.

Tại sao nên dùng hàm XLOOKUP?

Nếu bạn đang băn khoăn tại sao Excel cập nhật hàm XLOOKUP mới trong khi không hề có khiếu nại từ người dùng về hàm VLOOKUP và HLOOKUP, hãy nhìn vào nhược điểm của các hàm LOOKUP cũ mà bạn có thể khắc phục bằng hàm tìm kiếm trong Excel mới mang tên XLOOKUP.

1. Hàm VLOOKUP và HLOOKUP mặc định tìm kiếm xấp xỉ

Thay vì tìm kiếm đối tượng chính xác, hàm VLOOKUP và HLOOKUP mặc định chức năng tìm kiếm xấp xỉ. Đó chính là lý do bạn nhất định phải thiết lập tham số thứ tư của hàm VLOOKUP và HLOOKUP là False nếu không muốn Excel trả về kết quả sai.

2. Hàm VLOOKUP và HLOOKUP dừng hoạt động sau khi thêm bớt cột/hàng

Tham số thứ ba trong công thức hàm VLOOKUP và hàm HLOOKUP thể hiện số thứ tự của cột/hàng trả về giá trị. Vì tham số này liên kết đến một vị trí cố định trên bảng tính Excel, bạn sẽ không thể sử dụng công thức hàm sau khi thay đổi cấu trúc cột/hàng. Thay vào đó, bạn cần viết lại một hàm LOOKUP mới.

3. Hàm VLOOKUP không thể tìm kiếm bên trái

Một trong những nhược điểm lớn nhất của hàm VLOOKUP chính là nó không thể tham chiếu giá trị cần tìm từ phải qua trái. Nghĩa là hàm của bạn sẽ không hoạt động với toàn bộ vùng dữ liệu nằm bên trái cột chứa giá trị cần khớp.

4. Hàm VLOOKUP và HLOOKUP không tìm kiếm theo trình tự ngược

Nếu bạn không tự sắp xếp dữ liệu của mình theo trình tự ngược lại từ thứ tự lớn dần sang thứ tự nhỏ dần, hàm VLOOKUP và HLOOKUP sẽ không thể tự thay đổi kiểu tìm kiếm.

5. Hàm VLOOKUP và HLOOKUP không thể tìm giá trị lớn hơn kế tiếp

Khi bạn tìm kiếm giá trị xấp xỉ với hàm LOOKUP, bạn bắt buộc phải sắp xếp dữ liệu theo trình tự yêu cầu. Ngay cả khi đã thực hiện bước này, bạn cũng chỉ có thể nhận được giá trị nhỏ hơn gần nhất với giá trị cần tìm.

6. Hàm VLOOKUP và HLOOKUP tham chiếu tới các ô không cần thiết

Tham số thứ hai của hàm VLOOKUP và HLOOKUP - table_array - yêu cầu bạn điền toàn bộ phạm vi bảng dữ liệu, nghĩa là trong đó chắc chắn có những ô hoàn toàn có thể bỏ qua trong quá trình tìm kiếm. Việc kéo dài thao tác tìm kiếm sẽ làm chậm bảng tính của bạn.

Ngược lại với các hàm LOOKUP cũ, hàm XLOOKUP được thiết kế với sự linh hoạt và mạnh mẽ hơn rất nhiều. Nhờ đó, nó có thể vượt qua mọi nhược điểm của những người tiền nhiệm.

Một phương pháp sử dụng hàm tìm kiếm trong Excel hoàn hảo để thay thế hàm VLOOKUP và HLOOKUP chính là hàm INDEX MATCH. Không chỉ giải quyết nhanh gọn các vấn đề của hàm LOOKUP, hàm INDEX MATCH còn có rất nhiều ứng dụng khác. Bạn có thể tham khảo bài viết dưới đây để tìm hiểu về giải pháp hữu hiệu này.

Xem thêm: Cách dùng hàm MATCH - trợ thủ đắc lực cho hàm tìm kiếm trong Excel

Cách sử dụng hàm XLOOKUP trong Excel

Trong phần này, chúng ta sẽ khám phá sức mạnh của hàm tìm kiếm trong Excel mang tên XLOOKUP. Qua các ví dụ cụ thể, chúng ta có thể hiểu hơn về sự khác biệt của hàm so với các hàm LOOKUP cũ.

Ví dụ 1: Tìm kiếm giá trị

Đầu tiên, chúng ta sẽ áp dụng hàm XLOOKUP vào một thao tác tìm kiếm dữ liệu đơn giản với một bảng điểm như sau:

Yêu cầu: Tìm điểm Toán của học sinh tên Đạt và điền vào ô G3.

Cách làm:

Chúng ta sẽ sử dụng hàm XLOOKUP với công thức hàm tìm kiếm trong Excel như sau:

=XLOOKUP(G2,A2:A14,B2:B14)

Trong đó:

• lookup_value - G2: Giá trị cần tìm nằm tại ô G2.
• lookup_array - A2:A14: Giá trị cần khớp nằm tại cột A trong bảng điểm.
• return_array - B2:B14: Giá trị cần trả về là giá trị tương ứng nằm tại cột B trong bảng điểm.

Như vậy, công thức hàm XLOOKUP cơ bản phía trên trả về kết quả là 4.7, tương ứng với giá trị tại ô B5 trong bảng điểm. Ví dụ này không có gì khó hiểu đúng không nào?

Qua ví dụ này, bạn có thể thấy một điểm khác biệt giữa hàm XLOOKUP và hàm VLOOKUP trong phương thức hoạt động với các vùng dữ liệu. Nếu sử dụng hàm VLOOKUP, bạn sẽ phải chọn toàn bộ bảng điểm (A1:D14). Trong khi đó, hàm XLOOKUP chỉ cần bạn trực tiếp chọn ra cột chứa dữ liệu cần khớp (cột A) và cột chứa giá trị tương ứng cần trả về (cột B).

Một lợi thế rõ ràng của việc có 2 tham số lookup_array và return_array riêng biệt chính là hàm có thể tìm kiếm bên trái. Giả sử chúng ta đổi cột Học sinh sang bên phải cột Điểm Toán. Bạn sẽ thấy rằng công thức hàm tìm kiếm trong Excel vẫn hoạt động ngon lành mà không gặp phải bất kỳ lỗi gì.

=XLOOKUP(G2,D2:D14,A2:A14)

Ngoài ra, hàm XLOOKUP còn khắc phục thêm một nhược điểm nữa của hàm VLOOKUP và HLOOKUP. Hãy nhìn vào hình dưới đây với công thức:

=XLOOKUP(F2,C2:C14,A2:A14)

Hàm XLOOKUP hoàn toàn không gặp khó khăn khi tự điều chỉnh các tham số dựa vào thay đổi thêm bớt hàng trong bảng dữ liệu đang tham chiếu. Qua ví dụ này, ta có thể thấy sự linh hoạt đáng kể với công thức hàm tìm kiếm trong Excel mới này.

Ví dụ 2: Tìm kiếm một dải giá trị

Yêu cầu: Tìm các đầu điểm của học sinh Đạt và điền vào các ô G3:I3.

Cách làm:

Công thức hàm tìm kiếm trong Excel được sử dụng tại ô G3 dưới đây sẽ trả về cả 3 đầu điểm của học sinh Đạt vào lần lượt các ô G3, H3 và I3:

=XLOOKUP(F3,A2:A14,B2:D14)

Trong công thức hàm XLOOKUP trên, tham số return_array chứa nhiều hơn một cột. Vì vậy, khi tìm thấy giá trị cần khớp tại cột A, Excel sẽ trả về toàn bộ hàng tương ứng với giá trị đó.

Một điểm bạn cần lưu ý khi áp dụng hàm XLOOKUP trong trường hợp này chính là việc tất cả các ô hiển thị kết quả đều được áp dụng hàm, cho dù bạn không hề nhập hàm trực tiếp vào các ô này. Giả sử với ô H3 và ô I3 trong ví dụ: Khi bạn nhấn vào một trong hai ô sẽ thấy thanh công thức mờ đì, nghĩa là bạn không thể thay đổi công thức này.

Tất nhiên khi bạn xóa công thức đã nhập tại ô G2, toàn bộ kết quả hiển thị trong phạm vi ô này sẽ biến mất. Thao tác tiện lợi này chính là một bước tiến so việc bạn phải tạo từng công thức riêng khi sử dụng hàm VLOOKUP.

Ví dụ 3: Tìm kiếm giá trị theo 2 chiều

Yêu cầu: Tìm điểm Toán của học sinh tên Đạt và điền vào ô G3.

Cách làm:

Thay vì sử dụng công thức hàm XLOOKUP thông thường, chúng ta sẽ xây dựng công thức hàm tìm kiếm trong Excel theo 2 chiều, trong đó chúng ta vừa tìm giá trị tên học sinh ở cột A và tên môn học ở dòng 1. Việc sử dụng phương pháp tìm kiếm 2 chiều sẽ cho ta kết quả không phụ thuộc vào tên học sinh và tên môn học. Điều này nghĩa là chúng ta hoàn toàn có thể tùy ý thay đổi tên môn học hoặc tên học sinh mà hàm vẫn trả về kết quả đúng.

Công thức hàm tìm kiếm trong Excel 2 chiều với hàm XLOOKUP như sau:

=XLOOKUP(F3,B1:D1,XLOOKUP(G2,A2:A14,B2:D14))

Trong công thức trên, chúng ta sử dụng 2 hàm XLOOKUP. Hàm bên trong sẽ trả về kết quả là mảng {4.7,7.3,8.3} tương đương với tất cả các đầu điểm của học sinh Đạt. Kết quả này được sử dụng làm tham số return_array cho hàm XLOOKUP bên ngoài. Vì chúng ta đang tìm kiếm giá trị tương ứng với giá trị Điểm Toán (ô F3) nên hàm này lọc ra trong mảng return_array một giá trị để trả về ô G3.

Kết quả, chúng ta thu được con số 4.7 như trong hình.

Ví dụ 4: Không tìm thấy giá trị cần khớp

Hàm XLOOKUP đã được cập nhật tính năng xử lý lỗi với tham số thứ tư [if_not_found], cho phép bạn nhập một giá trị yêu cầu Excel trả về trong trường hợp hàm không tìm thấy giá trị cần khớp. Chúng ta sẽ thử tính năng này nhé.

Yêu cầu: Tìm điểm Toán của học sinh tên Mai và điền vào ô G3.

Cách làm:

Tại ô G3, chúng ta sẽ điền công thức hàm XLOOKUP như sau:

=XLOOKUP(G2,A2:A14,B2:B14,"Không tồn tại")

Như bạn thấy, chúng ta có thể xác định giá trị hàm trả về trong công thức hàm, thay vì lỗi #N/A trong trường hợp tương tự với các hàm VLOOKUP và HLOOKUP.

Ví dụ 5: Tìm kiếm giá trị trong nhiều phạm vi

Lợi ích của tham số [if_not_found] trong hàm XLOOKUP đặc biệt hữu ích khi bạn sử dụng hàm lồng nhau như trong ví dụ 3. Chúng ta hãy cùng tìm hiểu cách sử dụng hàm tìm kiếm trong Excel với nhiều hơn một bảng dữ liệu để thấy rõ điều này nhé.

Yêu cầu: Tìm điểm Toán của học sinh Uyên và điền vào ô B11.

Cách làm:

Để tham chiếu trong 2 bảng khác nhau, chúng ta sẽ cần đến công thức hàm XLOOKUP lồng nhau như dưới đây:

=XLOOKUP(B10,A2:A8,B2:B8,XLOOKUP(B10,F2:F8,G2:G8))

Trong công thức hàm tìm kiếm trong Excel trên, chúng ta đã sử dụng một công thức XLOOKUP cho tham số [if_not_found]. Điều này nghĩa là nếu hàm không tìm thấy giá trị cần khớp ở bảng 1, hàm sẽ tự động chuyển sang tìm kiếm với thao tác tương tự ở bảng 2.

Ví dụ 6: Tìm giá trị khớp cuối cùng

Với hàm XLOOKUP, chúng ta không còn phải tìm kiếm các công thức phức tạp để tìm giá trị cuối cùng trong bảng dữ liệu khớp với giá trị cần tìm nữa.

Yêu cầu: Tìm nhân viên mới nhất của công ty, ngày gia nhập tương ứng và điền thông tin vào bảng.

Cách làm:

Để tìm các nhân viên mới nhất của bộ phận kinh doanh, chúng ta sẽ điền công thức sau vào ô F3:

=XLOOKUP(F2,$B$2:$B$14,$A$2:$A$14,,,-1)

Để tìm ngày gia nhập của nhân viên mới nhất trong bộ phận kinh doanh, chúng ta sẽ sử dụng công thức dưới đây cho ô F4:

=XLOOKUP(F2,$B$2:$B$14,$C$2:$C$14,,,-1)

Vì hàm XLOOKUP được thiết kế với tính năng xác định chiều tìm kiếm (từ đầu đến cuối hoặc ngược từ cuối lên đầu), do đó chúng ta không hề gặp khó khăn khi tìm kiếm dữ liệu cuối cùng khớp với giá trị cần tham chiếu. Với dữ liệu theo chiều dọc, hàm VLOOKUP và HLOOKUP sẽ luôn luôn tìm kiếm từ đầu xuống cuối. Rõ ràng chúng ta có thể thấy được sự nổi trội của hàm XLOOKUP trong trường hợp này.

Xem thêm: Hướng dẫn cách dùng hàm VLOOKUP trong Excel qua các ví dụ từ cơ bản đến nâng cao

Ví dụ 7: Tìm giá trị xấp xỉ

Một điểm cải tiến khác trong cấu trúc hàm XLOOKUP là số kiểu khớp giá trị cần tìm. Trong khi hàm VLOOKUP và HLOOKUP chỉ có 2 kiểu, hàm MATCH có 3 kiểu, thì hàm XLOOKUP lại sở hữu đến 4 kiểu khớp như mình đã đề cập trong phần cú pháp của hàm.

4 loại match_mode của hàm tìm kiếm trong Excel XLOOKUP bao gồm:

• match_mode = 0 - Khớp chính xác đối tượng cần tìm. Nếu không tìm thấy giá trị khớp hoàn toàn, hàm trả về lỗi #N/A. Đây là kiểu khớp mặc định.
• match_mode = -1 - Khớp chính xác đối tượng cần tìm. Nếu không tìm thấy giá trị khớp hoàn toàn, hàm trả về giá trị nhỏ hơn gần nhất.
• match_mode = 1 - Khớp chính xác đối tượng cần tìm. Nếu không tìm thấy giá trị khớp hoàn toàn, hàm trả về giá trị lớn hơn gần nhất.
• match_mode = 2 - Khớp với một ký tự đại diện (wildcard)

Ví dụ 8: Tìm kiếm bảng ngang

Yêu cầu: Tìm điểm Toán của học sinh Uyên và điền vào ô B7.

Vì hàm XLOOKUP có thể trả về kết quả theo dạng mảng, chúng ta sẽ sử dụng công thức XLOOKUP(G2,$B$1:$D$1,$B$2:$D$14) để tìm ra các giá trị Điểm Toán trong bảng điểm. Sau đó, đưa kết quả vào trong tham số hàm MAX, chúng ta có được giá trị Điểm Toán cao nhất. Giá trị này trở thành lookup_value trong công thức hàm XLOOKUP lồng bên ngoài và trả về tên của học sinh có số điểm cao nhất trong bộ môn Toán.

Để đếm số học sinh đạt điểm cao hơn 8.0 trong môn Toán, chúng ta sử dụng công thức hàm COUNTIF như sau:

=COUNTIF(XLOOKUP(G2,$B$1:$D$1,$B$2:$D$14),">8.0")

Ví dụ 10: Tìm kiếm với ký tự đặc biệt

Ví dụ 11: Tìm giá trị cuối cùng của cột

=XLOOKUP("*",A2:A14,B2:B14,,2,-1)

Trong 2 công thức hàm XLOOKUP trên, chúng ta sử dụng ký tự đại diện "*" cho tham số value_lookup, nghĩa là giá trị đầu tiên hàm tìm thấy trong quá trình tham chiếu. Sau đó, thiết lập hướng tìm kiếm từ dưới lên. Vậy là chúng ta đã tạo được hàm tìm kiếm trong Excel để xác định dòng dữ liệu cuối cùng trong danh sách.

Xem thêm: Hướng dẫn sử dụng hàm MATCH tìm dòng cuối trong Excel

Tổng kết

Qua bài viết ngày hôm nay, chúng ta đã làm quen với một hàm LOOKUP hoàn toàn mới trong phiên bản Office 365 mang tên XLOOKUP. Đây là một giải pháp mới cho thao tác tìm kiếm dữ liệu trong Excel, khắc phục toàn bộ các nhược điểm của hàm VLOOKUP và HLOOKUP. Hy vọng 11 ví dụ trong bài đã giúp bạn nắm chắc cách ứng dụng hàm XLOOKUP trong các trường hợp cần thiết để nhanh chóng hoàn thành công việc của mình.

Để tìm thấy hàm XLOOKUP trong danh sách hàm, hãy đảm bảo bạn đang dùng phiên bản Office 365 nhé. Nếu bạn chưa cài đặt phiên bản này, hãy nhanh tay tải về tại bài viết hướng dẫn trên blog Gitiho. Ngoài ra, rất nhiều thủ thuật Excel hữu ích cũng đang chờ bạn khám phá đó. Cùng học với chúng mình nhé!

Gitiho xin cảm ơn bạn đọc và chúc bạn thành công!