Cách dùng hàm XLOOKUP thay thế VLOOKUP và HLOOKUP trong Excel

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 để dò tìm dữ liệu. Vậy bạn có tò mò liệu XLOOKUP là gì mà có thể thay thế được 2 hàm tìm kiếm “huyền thoại” kia không?

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 trong Excel thay thế các hàm VLOOKUP và HLOOKUP cũ qua 11 ví dụ cụ thể nhé!

Cú pháp hàm XLOOKUP là gì?

Có phải bạn đang tự hỏi XLOOKUP là hàm gì mà có thể thay thế hoàn hảo cho VLOOKUP và HLOOKUP?

XLOOKUP đượ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 nhiều điều kiện 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 máy tính thì hãy cân nhắc tải về nhé.

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à gì?

• if_not_found - Văn bản 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ệ.

Xem thêm: Hướng dẫn sửa lỗi #VALUE! khi sử dụng hàm XLOOKUP trong Excel

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

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 VLOOKUP và HLOOKUP cũ mà bạn có thể khắc phục bằng hàm 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 trong Excel đượ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”.

Ngoài ra, có một phương pháp khác để 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. 

Cách dùng 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 mang tên XLOOKUP Excel. 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ị với hàm XLOOKUP

Đầu tiên, chúng ta sẽ học cách 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 trong Excel với công thức hàm tìm kiếm 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ới 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). Nhưng nếu sử dụng hàm VLOOKUP, bạn sẽ phải chọn toàn bộ bảng điểm (A1:D14). 

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)

Đây là lợi thế rõ ràng của việc có 2 tham số lookup_array và return_array riêng biệt giúp hàm XLOOKUP có thể tìm kiếm bên trái. 

Ngoài ra, hàm XLOOKUP trong Excel 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 cột/ hàng trong bảng dữ liệu đang tham chiếu. 

Ví dụ 2: XLOOKUP tìm kiếm một dải giá trị so với 1 giá trị của VLOOKUP/ HLOOKUP

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 (B2:D14). 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ị đó ở ô G3, H3, I3 ngay cả khi bạn không nhập công thức ở ô H3 và I3.

Tất nhiên khi bạn xóa công thức đã nhập tại ô G3, 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 với hàm XLOOKUP

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à vừa tìm tên môn học ở dòng 1. 

Đ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 trong Excel như sau:

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

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

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ụ 4: Hàm XLOOKUP cho bạn biết khi không tìm thấy giá trị 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 rằng bạn Mai không có trong danh sách điểm vì hàm báo “Không tồn tại”, thay vì lỗi chung chung #N/A khi dùng 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. Chúng ta hãy cùng tìm hiểu cách sử dụng hàm XLOOKUP 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 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

Với hàm XLOOKUP, chúng ta không còn phải sử dụng 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)

• Hàm XLOOKUP tìm kiếm giá trị trong ô F2 trong khoảng dữ liệu "B2:B14", và trả về giá trị liên quan từ khoảng "A2:A14". 
• Tham số "-1" đang được sử dụng để thực hiện tìm kiếm theo hướng ngược lại (tìm kiếm từ dưới lên).
• Dấu phẩy cách " , , ," trước số -1 là để bỏ qua các tham số không cần thiết như "match_mode", "search_mode", và "search_result_mode".

Để 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)

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). 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.

Ví dụ 7: Tìm giá trị xấp xỉ với hàm XLOOKUP

Trong khi hàm VLOOKUP và HLOOKUP chỉ có 2 kiểu khớp giá trị cần tìm, 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)

Điều đáng nói ở đây là bạn hoàn toàn khổng phải sắp xếp thứ tự dữ liệu như những gì phải làm với hàm VLOOKUP và HLOOKUP. Ngay cả khi bảng dữ liệu của bạn lộn xộn, hàm XLOOKUP trong Excel vẫn hoạt động chính xác.

Yêu cầu: Tìm số tiền thưởng của các nhân viên và điền vào cột C.

Cách làm:

Tại ô C2, chúng ta điền công thức hàm tìm kiếm trong Excel như sau:

=XLOOKUP(B2,$E$2:$E$6,$F$2:$F$6,0,-1)*B2

Sau đó, kéo công thức xuống các ô còn lại trong cột và chúng ta sẽ được kết quả như sau:

Công thức hàm XLOOKUP tại ô C2 sử dụng giá trị ô B2 để tham chiếu trong bảng phụ bên phải. Trong các tham số, chúng ta dùng kiểu khớp match_mode = -1, nghĩa là hàm sẽ tìm kiếm giá trị trùng khớp chính xác với giá trị ô B2, nếu không tìm thấy thì trả về giá trị nhỏ hơn gần nhất với giá trị cần khớp.

Ví dụ 8: Tìm kiếm bảng ngang với hàm XLOOKUP

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

Cách làm:

Thao tác tìm kiếm bảng ngang này không có gì khác biệt so với cách thức tìm kiếm bảng dọc. Chính vì vậy, chúng ta có thể dễ dàng xây dựng công thức hàm tìm kiếm với hàm XLOOKUP trong Excel như sau:

=XLOOKUP(B6,B1:N1,B2:N2)

Ví dụ 9: Tìm kiếm có điều kiện bằng hàm XLOOKUP

Yêu cầu: Tìm học sinh đạt điểm cao nhất ở các môn học, xác định số lượng điểm cao hơn 8.0 với mỗi bộ môn và điền vào bảng.

Cách làm:

Để thực hiện yêu cầu đề bài, chúng ta sẽ cần kết hợp hàm XLOOKUP trong Excel với một số hàm khác. Cụ thể hơn, để tìm ra học sinh đạt điểm cao nhất môn Toán, công thức của chúng ta như sau:

=XLOOKUP(MAX(XLOOKUP(G2,$B$1:$D$1,$B$2:$D$14)),XLOOKUP(G2,$B$1:$D$1,$B$2:$D$14),$A$2:$A$14)

Trong công thức trên:

• 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")

Trong công thức trên, hàm XLOOKUP xác định phạm vi cần đếm là các giá trị điểm Toán, sau đó đưa vào tham số hàm COUNTIF để đếm các giá trị lớn hơn 8.0.

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

Bạn có thể tìm kiếm giá trị dựa trên ký tự đặc biệt với hàm XLOOKUP tương tự như VLOOKUP và HLOOKUP, hay hàm INDEX MATCH.

Tuy nhiên, khi sử dụng hàm XLOOKUP, bạn cần thông báo rằng bạn sẽ sử dụng ký tự đặc biệt trong cú pháp hàm. Nếu không, Excel sẽ trả về lỗi.

Yêu cầu: Tìm điểm Toán của các học sinh có tên và điền vào bảng.

Cách làm:

Công thức hàm XLOOKUP chúng ta sẽ sử dụng tại ô G2 như sau:

=XLOOKUP("*"&F2&"*",$A$2:$A$14,$B$2:$B$14,,2)

Trong công thức hàm tìm kiếm trong Excel, chúng ta đã kết hợp ký tự đại diện "*" để hàm hiểu rằng giá trị cần tìm bao gồm giá trị tại ô G2 và các ký tự khác. 

Để thông báo với Excel về việc sử dụng ký tự đại diện, chúng ta sẽ nhập giá trị 2 cho tham số thứ năm của công thức.

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

Vì hàm XLOOKUP trong Excel cho phép tìm từ dưới lên trên, chúng ta có thể dễ dàng sử dụng hàm để tìm giá trị cuối cùng của một danh sách.

Yêu cầu: Tìm điểm Toán của học sinh cuối cùng trong danh sách lớp.

Cách làm:

Công thức hàm XLOOKUP được sử dụng để tìm tên học sinh cuối cùng trong danh sách lớp là:

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

Công thức hàm XLOOKUP được sử dụng để tìm điểm Toán của học sinh cuối cùng trong danh sách lớp là:

=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. 

Tổng kết

Có thể nói, hàm XLOOKUP trong Excel là một giải pháp mới cho thao tác tìm kiếm dữ liệu, 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.

Ngoài các bài giảng học hàm excel thì Gitiho còn có 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é!