Cách dùng hàm XLOOKUP trong Excel hiệu quả, dễ dàng

Hàm XLOOKUP được ra đời với mục đích thay thế cho hàm VLOOKUP mà chúng ta vẫn hay sử dụng. Khắc phục được một vài hạn chế của hàm VLOOKUP và mang đến một số công dụng hiệu quả hơn. Tuy nhiên, vì đây là một hàm mới nên nhiều người vẫn chưa hiểu rõ về nó cũng cách sử dụng cơ bản. Đừng lo lắng bài viết dưới đây sẽ cung cấp đến bạn một vài kiến thức hữu ích về hàm XLOOKUP.

Hàm XLOOKUP trong Excel là gì?

Vậy hàm XLOOKUP trong Excel là gì? Vì sao hàm XLOOKUP lại được xuất hiện và nhận được sự đánh giá cao như vậy? Đây là một hàm mới được Microsoft cho ra mắt để thực hiện những tính năng giống như VLOOKUP. Nó sẽ khắc phục được những lỗi mà VLOOKUP không thể thực hiện được. Đồng thời hàm này còn mang đến cho người dùng nhiều tính năng tiện ích hơn.

Thêm vào đó, XLOOKUP còn có thể được thay thế cho hàm HLOOKUP. Ví dụ hàm XLOOKUP mới này có thể nhìn sang trái, cho phép chỉ định phạm vi ô. Bên cạnh đó còn có thể tự dò tìm kết quả một cách chính xác nhất. Những điều này hầu như VLOOKUP hay HLOOKUP không thể thực hiện được tốt như vậy.

Cách truy cập vào hàm XLOOKUP

Chỉ những ai dùng chương trình Insiders thì mới truy cập được vào hàm XLOOKUP. Chương trình Insiders cho phép người dùng sử dụng tính năng mới nhất của Excel. Và người dùng Office 365 đều sẽ được Microsoft cho quyền sử dụng XLOOKUP. Mặc khác đối với những ai dùng phiên bản Office 2010, 2013, 2016 và 2019 thì hàm XLOOKUP sẽ không khả dụng.

Vì thế nếu bạn muốn sử dụng được XLOOKUP thì thiết bị phải được nâng cấp lên Microsoft 365. Đối với ai đang sử dụng Microsoft 365 thì bạn sẽ dễ dàng truy cập được vào hàm XLOOKUP. Tùy chọn hàm này sẽ được bật lên trong Excel, với Office 365 Online thì XLOOKUP cũng có sẵn.

Cú pháp của hàm XLOOKUP

Dưới đây là cú pháp cụ thể của hàm XLOOKUP trong Excel mà bạn nên biết:

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

Các giá trị trong hàm XLOOKUP sẽ thể hiện những ý nghĩa như sau đây.

lookup_value (bắt buộc): Giá trị mà bạn đang muốn tìm.

lookup_array (bắt buộc): Mảng/phạm vi nào đó mà bạn muốn tìm kiếm giá trị.

return_array (bắt buộc): Mảng/dải ô mà bạn muốn lấy giá trị.

[if_not_found] (không bắt buộc): Giá trị trả về khi không thể tìm thấy kết quả phù hợp.

[match_mode] (không bắt buộc): Bạn được phép chỉ định loại đối sánh mà bạn đang tìm:

• 0: Tìm kết quả chính xác, đồng thời giá trị cũng phải khớp chính xác với lookup_array (mặc định).
• -1: Tìm kết quả phù hợp và chính xác. Nếu không tìm được kết quả chính xác, trở về giá trị nhỏ hơn.
• 1: Tìm kiếm kết quả chính xác. Nếu không tìm được giá trị chính xác phù hợp thì trả về giá trị lớn hơn.
• 2: Đối sánh từng phần với nhau, dùng các ký tự đại diện đặc biệt như *, ? và ~.

[search_mode] (không bắt buộc): Dùng để chỉ định thứ tự tìm kiếm:

• 1: Tìm kiếm từ mục đầu tiên đến mục cuối cùng (mặc định).
• -1: Tìm kiếm giá trị từ mục cuối cùng đến mục đầu tiên.
• 2: Tìm kiếm nhị phân của lookup_array, sắp xếp theo các giá trị tăng dần. Trường hợp sắp xếp không đúng thì sẽ không hợp lệ.
• -2: Tìm kiếm nhị phân của lookup_array, sắp xếp các giá trị giảm dần. Nếu không sắp xếp đúng sẽ dẫn đến kết quả bị sai.

Những ưu điểm và nhược điểm của hàm XLOOKUP

Để biết chi tiết hơn về đặc điểm của hàm XLOOKUP, bạn hãy xem qua các thông tin dưới đây. Sẽ giúp bạn có được cái nhìn cụ thể hơn về ưu, nhược điểm trước khi sử dụng hàm này.

Ưu điểm của hàm XLOOKUP Excel

• Hàm XLOOKUP có thể hoạt động, sử dụng được ở cả chiều dọc và chiều ngang.
• Nếu hàm VLOOKUP và INDEX MATCH cần đến 4 đối số thì hàm XLOOKUP chỉ cần có 3 đối số.
• Bạn có thể dễ dàng sử dụng các ký tự đại diện để kiểm tra từng phần.
• Thực hiện được thuận tiện việc tra cứu theo thứ tự giảm dần.
• Với hàm INDEX và hàm MATCH bạn sẽ phải sử dụng hai hàm nhưng với XLOOKUP thì bạn chỉ cần sử dụng một hàm.

Nhược điểm của hàm XLOOKUP Excel

• Khi mới bắt đầu việc sử dụng XLOOKUP thì các đối số có thể khá phức tạp.
• Sẽ gây mất nhiều thời gian thực hiện hơn, vì có nhiều ô hiển thị trong bảng tính.
• Người dùng cần phải nhớ kỹ phạm vi tra cứu cũng như phạm vi trả về.

Cách dùng hàm XLOOKUP trong Excel

Để biết được cách sử dụng hàm XLOOKUP chi tiết nhất, bạn hãy xem qua ví dụ cụ thể ngay dưới đây. Ví dụ yêu cầu trả lại các bộ phận/giá trị từ cột F cho mỗi ID có tại cột A.

Đây là một ví dụ về tra cứu đối sánh thường gặp ở hàm XLOOKUP và hàm chỉ yêu cầu 3 loại thông tin. Như hình bên dưới thì ta thấy XLOOKUP có 6 đối số. Tuy nhiên, chỉ dùng đến 3 đối số đầu tiên để thực hiện, do đó bạn hãy quan tâm chủ yếu đến chúng.

• Lookup_value: Chính là những giá trị mà bạn đang tìm kiếm.
• Lookup_array: Vị trí tìm kiếm là ở đâu.
• Return_array: Phạm vi, khu vực sẽ chứa giá trị được trả về.

Đối với ví dụ này thì công thức chính xác sẽ được áp dụng như sau: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Sau đây là những cách sử dụng và lợi thế của hàm XLOOKUP mà bạn nên biết.

Không có thêm số chỉ mục cột

Hàm XLOOKUP sẽ cho phép người sử dụng chọn được phạm vi giá trị được trả về. Còn đối với VLOOKUP thì bạn sẽ không thực hiện được việc này. Thêm vào đó, đối với những ô đã chọn thì XLOOKUP cho phép bạn xem được dữ liệu. Đồng thời còn giúp hạn chế được các lỗi về công thức khi thực hiện thêm cột mới. Do đó, bạn có thể thấy XLOOKUP sẽ hàm sử dụng thuận tiện và hiệu quả cho công việc.

Khớp tuyệt đối theo mặc định

Khi người dùng sử dụng XLOOKUP sẽ được mặc định là khớp tuyệt đối. Việc này có tác dụng giúp giảm đối số thứ bốn và hỗ trợ người dùng mới ít bị mắc lỗi. Nhờ vậy mà ta sẽ thấy được rằng XLOOKUP yêu cầu ít câu hỏi hơn so với VLOOKUP. Đồng thời XLOOKUP cũng mang đến cho người dùng sự thân thiện và tiện lợi.

XLOOKUP có thể tìm đến bên trái

Đối với XLOOKUP thì ta có thể tìm đến bên trái một cách dễ dàng. Vì XLOOKUP có thể chọn phạm vi tra cứu linh hoạt hơn VLOOKUP. Đồng thời thứ tự của những cột trong bảng lúc này cũng không cần thiết. Ở ví dụ ngay bên dưới đây bạn sẽ phải tìm ID (cột E) rồi trả về chỗ tên người (cột D). Công thức bạn nên sử dụng như sau:

=XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Tra cứu phạm vi

Hãy lấy một ví dụ cụ thể như: Người dùng đang muốn trả lại chiết khấu dựa vào số tiền đã được chi tiêu. Với trường hợp này chúng ta sẽ không tìm một giá trị nhất định. Mà thay vào đó là biết được giá trị của cột B nằm trong mức nào của cột E. Nhằm để xác định được mức chiết khấu chính xác nhất.

So với VLOOKUP thì XLOOKUP có thể tìm ra được kết quả phù hợp và gần đúng hơn. Ở hàm này bạn sẽ có thể tùy chọn tìm được kết quả gần nhất nhỏ hơn (-1). Hoặc là tìm được kết quả gần nhất và lớn hơn (1). Bên cạnh đó bạn còn có thể lựa chọn để dùng những ký tự đại diện (2) đặc biệt.

Công thức sẽ cho ra giá trị gần nhất nhỏ hơn giá trị bạn đang tìm nếu không tìm ra được kết quả khớp nhất. Công thức cụ thể như sau: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,-1)

Trong hình có một lỗi ở ô C7 nên sẽ trả về lỗi #N/A, nếu đúng thì chiết khấu là 0%. Tuy nhiên do 64 quá nhỏ nên không đạt được mức nhận chiết khấu.

Để lỗi ở ô C7 được khắc phục bạn hãy nhập một hàng mới ngay tại cuối bảng. Bạn tạo công thức và mở rộng phạm vi đã dùng bằng việc click vào và kéo thả các góc.

Như vậy, công thức đã được sửa lỗi và giá trị chiết khấu đã được hoàn thiện chính xác nhất.

XLOOKUP thay thế được hàm HLOOKUP

Với nhiều công dụng vô cùng hữu ích nên hàm XLOOKUP có thể thay thế được hàm HLOOKUP. Như vậy, ta có thể thấy XLOOKUP có thể thay thế được hai hàm. Điều này góp phần giúp mang đến cho người dùng sự thuận tiện và làm việc hiệu quả hơn. Về hàm HLOOKUP, đây là hàm thực hiện tra cứu theo hàng ngang. Vì thế, hàm này được sử dụng chủ yếu trong việc tìm kiếm theo hàng. Tuy hàm này không được phổ biến và thông dùng bằng hàm VLOOKUP. Nhưng sẽ hữu dụng khi xuất hiện trường hợp tiêu đề ở cột A và dữ liệu ở hàng 4,5.

Còn khi sử dụng hàm XLOOKUP thì bạn sẽ thực hiện được việc tra cứu ở hai chiều. Bạn sẽ sử dụng tra cứu được ngay ở cột hoặc theo hàng đều được. Lấy ví dụ này bạn có thể thấy công thức dùng cho việc trả về giá trị liên quan đến tên ở cột A2. Bắt đầu nhìn vào hàng 4 tìm tên rồi trả giá trị từ hàng 5.

Công thức: =XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP có thể nhìn từ dưới lên

Bình thường để có thể thấy được sự hiện diện đầu tiên của giá trị thì bạn sẽ cần nhìn từ trên xuống dưới. Với XLOOKUP thì sẽ có một đối số thứ 5 chính là search_mode. Sử dụng đối số này thì bạn sẽ có thể thực hiện việc tìm kiếm giá trị từ dưới lên trên. Từ đó bạn sẽ thấy được sự xuất hiện cuối cùng của một giá trị nào đó.

Ở ví dụ ngay dưới đây, bạn cần phải tìm giá trị tồn kho của sản phẩm nằm ở cột A. Đồng thời bảng này sẽ được sắp xếp theo mục ngày tháng. Bạn muốn trả về giá trị tồn kho ở lần cuối cùng được kiểm tra.

Ở đối số thứ 5 của trong hàm XLOOKUP sẽ xuất hiện bốn lựa chọn như trong hình. Bạn sẽ thực hiện lựa chọn Search last-to-first. Công thức cho ví dụ này cụ thể như sau:

=XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,-1)

Bạn hãy lưu ý trong công thức này thì đối số thứ bốn sẽ được bỏ qua. Vì đây chính là lựa chọn mà bạn muốn nó mặc định là khớp tuyệt đối.

Qua bài viết ở trên, Sforum vừa gửi đến bạn những thông tin rất hữu ích về hàm XLOOKUP. Góp phần giúp bạn có được những kiến thức cơ bản về cách sử dụng hàm XLOOKUP trong công việc. Hy vọng bạn sẽ có thể sử dụng hàm này một cách hiệu quả và tối ưu nhất. Nếu còn thắc mắc nào cần được giải đáp về hàm XLOOKUP, bạn hãy để lại bình luận bên dưới nhé.