Khám phá hàm XLOOKUP - công cụ tìm kiếm mạnh mẽ của Excel

Tạm quên VLOOKUP hay HLOOKUP đi vì bạn có thể sử dụng hàm XLOOKUP - một công cụ tìm kiếm cực kỳ mạnh mẽ đã có mặt trên Excel. Cùng khám phá những điều thú vị về XLOOKUP ngay nhé!

Chúng ta đã biết đến các hàm dò tìm như VLOOKUP, HLOOKUP. Những hàm này trước giờ hầu như đã được sử dụng khác phổ biến. Hiện tại, trong Excel đã có thêm một công cụ tìm kiếm khác là hàm XLOOKUP với nhiều tính năng thú vị. Thế XLOOKUP nó có gì hay ho hơn các hàm khác? Chúng ta sẽ cùng tìm hiểu ngay sau đây nhé!

Xem thêm: Tuyệt chiêu sử dụng hàm XLOOKUP thay thế VLOOKUP và HLOOKUP cực đỉnh

Cú pháp hàm XLOOKUP trong Excel

Hàm XLOOKUP sẽ có 5 đối số, tuy nhiên trong đó chỉ có 3 đối số đầu tiên là bắt buộc còn 2 đối số sau là tuỳ chọn, cụ thể như sau:

Trong đó:

• lookup_value: Là giá trị tìm kiếm
• lookup_array: Là vùng (mảng) tìm kiếm
• return_array: Là vùng (mảng) mà ở đó bạn muốn trả về kết quả
• [match_mode]: Là lựa chọn chế độ khớp giá trị khi tìm kiếm
• [search_mode]: Là lựa chọn chế độ tìm kiếm

Lưu ý:

• lookup_value: Có thể là 1 giá trị hoặc 1 mảng giá trị bạn muốn tìm kiếm
• lookup_array và return_array: Cần khớp nhau về kích thước
• [match_mode]: Mặc định là tìm kiếm chính xác (0 – Exact match)
• [search_mode]: Mặc định là tìm kiếm từ đầu đến cuối (1 – Search first-to-last)

Nếu như bạn nắm được các hàm VLOOKUP hay HLOOKUP thì việc sử dụng hàm XLOOKUP sẽ không có gì khó khăn hết nhé. Chúng ta sẽ cùng tìm hiểu về công dụng của hàm XLOOKUP ngay dưới đây nhé!

9 điểm thú vị của hàm XLOOKUP trong Excel

Chúng ta sẽ cùng khám phá những điều thú vị đáng chú ý của hàm XLOOKUP nhé!

Hàm XLOOKUP có thể tìm kiếm “bên TRÁI”

Đối với những bạn thường xuyên dùng hàm VLOOKUP trong Excel thì chắc đã biết hạn chế của hàm này là chỉ có thể là 1 cột nào đó nằm bên phải của cột đầu tiên trong bảng dò, tương tự với HLOOKUP, ta cũng chỉ có thể trả về kết quả là 1 hàng nào đó nằm dưới hàng đầu tiên trong bảng dò. Mặc dù cũng có một số cách để xử lý được việc này VD như kết hợp thêm hàm CHOOSE, tuy nhiên nó khá phức tạp và sẽ không tối ưu trong nhiều trường hợp. Với hàm XLOOKUP đã khắc phục được điều này, bạn xem ví dụ sau, căn cứ vào đơn giá để tìm tên hàng hoá:

Xem thêm: Giới thiệu hàm XLOOKUP dành cho Office 365 Insiders

Hàm XLOOKUP có thể tìm kiếm từ cuối lên đầu (Search last to first)

Tiếp theo, vói hàm XLOOKUP ta có thể linh động trong tìm kiếm dữ liệu, đơn cử là ta có thể tìm kiếm từ dưới lên. Điêu này cũng khắc phục được những hạn chế của hàm VLOOKUP hay HLOOKUP.

Với đối số thứ 5 của hàm là -1 (Search last-to-first).

Với cùng hàng hoá 1, công thức ở ô E3 có tham số thứ 5 là 1 (Search first-to-last) thì sẽ tìm từ đầu đến cuối trong lookup_array (tương tự hàm VLOOKUP).

Còn công thức ở ô E5 có tham số thứ 5 là -1 (Search last-to-first) thì hàm sẽ thực hiện bắt đầu tìm kiếm từ cuối lên đầu của lookup_array (Điều này hàm VLOOKUP không thể làm theo một cách thông thường).

Hàm XLOOKUP trả về 1 mảng giá trị

Hàm XLOOKUP cũng có thể trả về một mảng giá trị, cụ thể như sau:

Công thức ở ô I4 như sau:

=XLOOKUP(H2,A4:A7,A4:F7)

Hàm sẽ thực hiện tìm kiếm giá trị trong ô H2 trong vùng A4:A7 và vùng trả về kết quả ở đây là vùng từ A4:F7. Tương tự với các công thức ở ô I6 và I8.

Xem thêm: Phân biệt sự khác nhau giữa hàm XLOOKUP và VLOOKUP trong Excel

Nối các giá trị tìm kiếm trong hàm XLOOKUP

Hàm XLOOKUP sẽ cho phép người dùng nối chuỗi trong giá trị tìm kiếm, vùng tìm kiếm cũng như vùng kết quả. Khi bạn muốn tìm kiếm với nhiều điều kiện khác nhau bạn có thể nối các giá trị đó lại để tìm kiếm. Có thể thấy với hàm XLOOKUP bạn hoàn toàn linh hoạt trong việc tìm kiếm giá trị để có được kết quả mong muốn.

Tìm kiếm một mảng giá trị với hàm XLOOKUP

Ở phần trên, chúng ta đã thực hiện tìm kiếm với nhiều điều kiện khác nhau bằng cách nối các giá trị đó lại để tìm kiếm. Nhưng đó chưa phải là tất cả, hàm XLOOKUP còn có thể tìm kiếm cả mảng giá trị. Bạn tham khảo ví dụ sau đây để hiểu rõ nhé:

Ở đây chúng mình đã sử dụng hàm UNIQUE – một hàm để loại bỏ giá trị trùng lặp và trả về danh sách duy nhất.

Một lưu ý khi tìm kiếm theo cả một mảng giá trị là: Kết quả trả về là 1 mảng giá trị theo chiều dọc (tức gồm nhiều hàng nhưng chỉ có 1 cột).

Từ đây chúng ta có thể rút ra là:

• Hàm XLOOKUP có thể về 1 mảng giá trị theo chiều ngang (tức theo hàng trong Excel, gồm 1 hàng và có nhiều cột).
• Hàm XLOOKUP cũng có thể trả về 1 mảng giá trị theo chiều dọc (tức nhiều hàng và 1 cột).

Bạn có thể hiểu mảng trả về này là mảng 1 chiều, hoặc 2 chiều nhưng trong 2 chiều này phải có 1 chiều với kích thước bằng 1. Hàm XLOOKUP sẽ không trả về được 1 mảng giá trị gồm nhiều hàng và nhiều cột (>=2 hàng và >=2 cột), bạn có thể thấy điều đó qua ví dụ trên (ô I12).

Xem thêm: Cách viết công thức VLOOKUP đơn giản, dễ hiểu qua các bước ví dụ

Kết hợp hàm XLOOKUP với các hàm khác của Excel

Excel cho phép bạn kết hợp các hàm lồng vào nhau một cách rất linh hoạt, bạn hoàn toàn có thể tuỳ biến công thức theo ý của mình để có được kết quả mong muốn. Hàm XLOOKUP cũng không ngoại lệ.

Vận dụng việc hàm XLOOKUP có thể trả về cả mảng giá trị trên, ta có thể tính tổng số lượng của những hàng hoá phát sinh vào lần cuối cùng.

Nối chuỗi linh hoạt trong kết quả trả về của hàm XLOOKUP

Hãy theo dõi ví dụ sau đây, bạn sẽ thấy được sự linh hoạt trong kết quả trả về của hàm XLOOKUP:

Với hàm trên bạn có thể thấy kết quả trả về không chỉ là những con số khô khan nữa. Vận dụng việc hàm trả về cả mảng giá trị ta kết hợp thêm các chuỗi TEXT lồng vào trong kết quả trả về để xem số lượng và thành tiền phát sinh lần đầu tiên cũng như cuối cùng của các hàng hoá có phát sinh.

Xem thêm: Hàm Vlookup dò tìm từ phải qua trái trong Excel

Hàm XLOOKUP lồng XLOOKUP thì sẽ như thế nào?

Có lẽ nhiều bạn đã biết cách dùng hàm VLOOKUP lồng HLOOKUP trong Excel. Bây giờ chúng ta sẽ thử cách dùng hàm XLOOKUP lồng nhau xem có gì thú vị nhé. Ví dụ sau sẽ thực hiện tính tổng số lượng của hàng hoá tính từ lúc hàng hoá C có phát sinh đầu tiên tới lúc hàng hoá C có phát sinh lần cuối cùng.

Khắc phục được lỗi chèn dòng, cột trong hàm VLOOKUP và HLOOKUP

Một điểm hạn chế nữa của hàm VLOOKUP cũng như HLOOKUP là khi chúng ta thực hiện chèn thêm 1 cột (với hàm VLOOKUP) hay chèn thêm 1 dòng (với hàm HLOOKUP) thì lúc này kết quả trả về của hàm sẽ bị sai. Hàm XLOOKUP đã khắc phục được điều đó.

Trong ví dụ dưới, khi chúng mình thực hiện chèn thêm cột vào giữa cột A và B thì kết quả ở ô D3 sử dụng hàm XLOOKUP không bị thay đổi còn với ô D2 sử dụng hàm VLOOKUP thì kết quả đã bị thay đổi (dẫn tới sai). Tương tự với hàm HLOOKUP, khi chúng mình chèn thêm 1 dòng vào giữa dòng 7 và 8 thì kết quả ở ô H6 sử dụng hàm XLOOKUP không bị thay đổi còn ô H5 sử dụng hàm HLOOKUP thì kết quả đã bị thay đổi dẫn tới xảy ra lỗi sai.

Xem thêm: Hướng dẫn cách phân biệt lệnh VLOOKUP và lệnh HLOOKUP trong Excel

Kết luận

Như vậy là chúng ta đã cùng tìm hiểu về những điều thú vị của hàm XLOOKUP - một công cụ tìm kiếm mạnh mẽ của Excel. Sự ra đời của XLOOKUP đã khắc phục được khá nhiều điểm yếu của VLOOKUP và HLOOKUP. Tốc độ làm việc của bạn sẽ được nâng cao hơn rất nhiều khi sử dụng XLOOKUP. Hàm dò tìm này được cập nhật trên Office 365 bản Insider. Nếu trong máy bạn chưa có phiên bản này thì hãy cài đặt để trải nghiệm nhé.

Nếu bạn muốn được học tất tần tật kiến thức về 150+ hàm trong Excel, ứng dụng thực tế vào nhiều ngành nghề khác nhau thì hãy tham gia khóa học dưới đây:

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

Khóa học được xây dựng bởi các chuyên gia tin học văn phòng đã có nhiều năm kinh nghiệm giảng dạy cho nhân viên của các tập đoàn lớn như Vietinbank, VP Bank. Viettel, Mobifone, FPT Software,... Hệ thống bài học được xây dựng theo lộ trình từ cơ bản đến nâng cao, phù hợp với mọi trình độ. Các bài học sẽ được cập nhật thêm cung cấp kiến thức mới dựa theo những cập nhật của Excel. Học viên chỉ cần đăng ký một lần duy nhất là được học trọn đời, không phải trả thêm chi phí cho việc xem các bài học mới cập nhật. 

Trong quá trình học bạn có thể trao đổi với giảng viên thông qua mục Hỏi - Đáp và sẽ được phản hồi trong vòng 24h. Hoàn thành toàn bộ các bài học lý thuyết, thực hành và tham khảo thêm ebook được tặng miễn phí theo khóa học bạn sẽ nắm trọn những thủ thuật Excel hay nhất để ứng dụng cho công việc hàng ngày.

Hãy đăng ký ngay để vừa trải nghiệm học tập tuyệt vời vừa trang bị kiến thức hữu ích các bạn nhé!