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

Ngày nay, người dùng thường sử dụng hàm XLOOKUP trong Excel vì nó có nhiều sự tiện lợi. Tuy nhiên, không phải ai cũng biết cách sử dụng sao cho hiệu quả nhất. Vì vậy, chúng tôi sẽ hướng dẫn bạn cách dùng hàm XLOOKUP ngay trong bài viết này cùng một vài thủ thuật hay để kết hợp các hàm vô cùng đơn giản.

Hàm XLOOKUP trong Excel là gì?

XLOOKUP là một hàm tìm kiếm và trả về kết quả dựa trên nhiều tiêu chí khác nhau trong Excel. Nó có thể thực hiện tìm kiếm theo hàng hoặc cột. 

Hàm XLOOKUP trong Excel là một tính năng mới được ra mắt từ phiên bản Office 365. Nhà phát triển của Microsoft Excel đã xây dựng hàm XLOOKUP mang lại nhiều tính năng hơn cho người dùng.

Có thể nói, hàm mới này là một phiên bản cải tiến từ VLOOKUP và HLOOKUP. Bởi vì, nó khắc phục được những lỗi và là hàm bao gồm cả 2 tính năng của VLOOKUP và HLOOKUP. Thêm vào đó, cách dùng hàm XLOOKUP cũng đơn giản hơn so với các hàm tìm kiếm đã có trước đó.

Hãy xem tiếp bài viết này để biết cú pháp và cách dùng hàm XLOOKUP trong Excel hiệu quả nhất.

Cú pháp sử dụng hàm XLOOKUP trong Excel

Để sử dụng hàm XLOOKUP một cách hiệu quả nhất, bạn nên hiểu rõ các chi tiết trong cú pháp của hàm này. Cú pháp của nó như hình dưới đây:

Hàm có 3 tham số đầu tiên là bắt buộc, 3 tham số sau bạn có thể tùy chỉnh. Các tham số trong cú pháp có ý nghĩa như sau:

• Lookup_value: Giá trị mà bạn tìm kiếm.
• Lookup_array: Vùng/Phạm vi dùng để tìm kiếm.
• Return_array: Vùng/Phạm vi có chứa giá trị mà bạn muốn được trả về.
• [if_not_found] (không bắt buộc): Giá trị sẽ hiện thay thế khi không tìm thấy kết quả. Nếu bỏ qua bước này, giá trị thay thế sẽ hiện là #N/A.
• [match_mode] (không bắt buộc): Bạn đươcS tùy chọn cách thức so với giá trị tìm kiếm. Nó bao gồm các cách thức như:
• 0: Kết quả sẽ chính xác với giá trị tìm kiếm (được mặc định). Nếu không tìm thấy kết quả, kết quả thay thế được hiển thị là #N/A.
• -1: Vẫn tìm kiếm kết quả chính xác. Nếu không tìm thấy kết quả, nó sẽ tự động trả về giá trị nhỏ hơn gần nhất.
• 1: Tương tự như tùy chọn “-1”. Thế nhưng, kết quả trả về thay thế ở tùy chọn này là giá trị lớn hơn gần nhất.
• 2: Tìm kiếm theo các ký tự đặc biệt như *, ? và ~.
• [search_mode] (không bắt buộc): Đây cũng là tham số tùy chọn, bạn sẽ được chọn hướng tìm kiếm. Các tùy chọn bao gồm:
•  1: Nó sẽ tìm kiếm từ mục đầu tiên cho đến mục cuối cùng (được mặc định).
• -1: Trường hợp này, hướng tìm kiếm sẽ ngược lại là tìm từ mục cuối cùng đến mục đầu tiên.
•  2: Tìm nhị phân của “Lookup_array” và dữ liệu sẽ được sắp xếp tăng dần.
• -2: Giống với tùy chọn “2” nhưng dữ liệu sẽ được sắp xếp giảm dần.

Cách dùng hàm XLOOKUP ngày càng được sử dụng nhiều hơn so với các hàm tìm kiếm truyền thống. Hãy sang phần tiếp theo để biết lý do tại sao người dùng lại đánh giá cao XLOOKUP như vậy nhé!

Xem thêm hướng dẫn cách dùng hàm Hlookup trong Excel cực đơn giản và chi tiết tại đây!

Ưu và nhược điểm của XLOOKUP

Hàm XLOOKUP trong Excel có những cải tiến nổi trội hơn so với các hàm tìm kiếm trước đó là VLOOKUP hay HLOOKUP. Tuy nhiên, bất kỳ một hàm nào cũng có những ưu - nhược điểm của riêng nó. Do vậy, chúng tôi đã liệt kê chúng dưới đây để các bạn dễ hiểu hơn.

Ưu điểm:

• Với hàm HLOOKUP chỉ có thể tìm kiếm theo hàng và VLOOKUP tìm kiếm theo cột. Còn đối với XLOOKUP, bạn có thể sử dụng hàm theo cả hàng hoặc cột.
• Cho phép bạn tìm kiếm giá trị trong nhiều cột hoặc nhiều hàng cùng một lúc. Điều này giúp việc tìm kiếm được nhanh hơn và thuận tiện hơn.
• Các hàm tìm kiếm truyền thống như VLOOKUP cần đến 4 tham số. Còn XLOOKUP đã có thể hoạt động chỉ với 3 tham số.
• Tính năng tìm kiếm trở nên linh hoạt hơn nhờ có khả năng trả kết quả theo điều kiện.

Nhược điểm:

• Hàm mới này chỉ có thể hoạt động trên Office 365 trở lên hoặc Google Trang tính. Điều này có nghĩa là các phiên bản cũ hơn sẽ không thể sử dụng nó.
• Nếu lần đầu sử dụng, bạn sẽ thấy cách dùng hàm XLOOKUP phức tạp hơn so với VLOOKUP và HLOOKUP. Bởi vì, nó có quá nhiều tùy chọn khiến bạn khó nhớ.

Có thể thấy, những nhược điểm của hàm XLOOKUP là không đáng kể. Vì vậy, hàm này vẫn được xem là một hàm hữu ích và được sử dụng khá nhiều trong công việc.

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

Cách dùng hàm XLOOKUP không phức tạp như bạn nghĩ. Bạn chỉ cần 3 tham số bắt buộc là có thể thực hiện được. Tuy nhiên, trong một vài trường hợp bạn sẽ cần dùng đến 3 tham số còn lại để lọc được kết quả chính xác hơn. Vì vậy, để dễ hiểu chúng tôi sẽ chia làm 2 cấp độ sử dụng hàm XLOOKUP dưới đây.

Cách vận dụng hàm cơ bản

Đầu tiên, chúng tôi sẽ hướng dẫn bạn dùng hàm XLOOKUP cơ bản chỉ với 3 tham số bắt buộc. 

Tra cứu theo cột:

Ví dụ: Bạn cần trả giá trị trong cột H tương ứng với mã SKU về cột B. Ở trường hợp này, bạn cần quan tâm 3 tham số sau:

• Lookup_value: Mã SKU ở cột A.
• Lookup_array: Vùng tìm kiếm là dữ liệu có trong cột D.
• Return_array: Khu vực chứa dữ liệu được trả về là dữ liệu cột F.

Trong ví dụ này, công thức được sử dụng là: =XLOOKUP(A2;$D$2:$D$10;$F$2:$F$10)

Tra cứu theo hàng:

Với ví dụ tương tự như trên nhưng dữ liệu trình bày theo hàng. Ta có công thức áp dụng cho trường hợp này như sau: =XLOOKUP(B1;$B$6:$I$6;$B$8:$I$8)

Điều đặc biệt ở hàm XLOOKUP chính là bạn có thể tìm kiếm theo hàng hoặc cột chỉ với 1 cú pháp. Trước đây, bạn phải nhớ hàm của VLOOKUP cho cột và HLOOKUP cho hàng. Vậy là, bạn sẽ không cần phải nhớ quá nhiều hàm nữa.

Cách vận dụng hàm XLOOKUP nhiều điều kiện

Trường hợp, bạn cần tìm kiếm có điều kiện. Vậy hãy tham khảo cách dùng hàm XLOOKUP trong Excel với cú pháp đầy đủ ngay sau đây.

Ví dụ: Bạn cần trả về giá trị của Brand tương ứng với SKU ô A2. Với điều kiện tra cứu từ mục cuối cùng lên mục đầu tiên. Nếu không tìm thấy giá trị chính xác thì trả về giá trị lớn hơn gần nhất.

Bạn cần xác định các tham số như sau:

• Lookup_value:Ô A2.
• Lookup_array: Vùng dữ liệu cần tìm kiếm là mã SKU ở cột D.
• Return_array: Vùng dữ liệu trả về kết quả là Brand ở cột F.
• [if_not_found]: Tham số này chúng ta sẽ để trống, kết quả mặc định nếu không tìm thấy là #N/A.
• [match_mode]: Ở ví dụ này, yêu cầu là giá trị lớn hơn gần nhất nên nhập “1”.
• [search_mode]: Hướng tra cứu là từ mục cuối lên đầu, nên chúng ta chọn “-1”.

Từ đó, chúng ta áp dụng được công thức: =XLOOKUP(A2;$D$2:$D$10;$F$2:$F$10;;1;-1)

Ngoài ra, hàm XLOOKUP còn có thể kết hợp với các hàm khác trong Excel để tra cứu 1 giá trị nào đó mà bạn muốn. Để làm rõ hơn trường hợp này, hãy xem ví dụ dưới đây.

Ví dụ: Bạn cần tính tổng sản phẩm của Ly lúc nhập kho lần đầu và lần cuối. Với điều kiện trả về giá trị chính xác.

Thông thường, để tìm được tổng chúng ta sẽ sử dụng hàm SUM. Ở ví dụ này, điều kiện để tính tổng phức tạp hơn. Nếu dữ liệu lớn hơn, bạn phải tìm và cộng cách thủ công sẽ rất tốn thời gian. Vì vậy, để đơn giản hơn, chúng ta sẽ kết hợp thêm với hàm XLOOKUP để chọn ra số lượng nhập đầu và cuối của Ly. Cụ thể như sau:

=SUM(XLOOKUP(E2;$A$2:$A$10;$C$2:$C$10;;;-1);XLOOKUP(E2;$A$2:$A$10;$C$2:$C$10))

Nếu bạn biết cách kết hợp XLOOKUP cùng với các hàm khác, bảng Excel của bạn sẽ trở nên rất chuyên nghiệp. Đồng thời, bạn cũng sẽ giảm bớt được các thao tác tính.

Tóm lại, cách dùng hàm XLOOKUP có cú pháp khá khó nhớ đối với người mới. Bạn chỉ cần làm theo hướng dẫn trên vài lần là đã có thể thành thạo. Hy vọng bài viết này sẽ giúp thao tác trong Excel của bạn trở nên chuyên nghiệp hơn. Hãy chia sẻ bài viết để nhiều người được biết đến thông tin này nhé!