9 1 1024x534 1

Trong lần cập nhật mới đây, Microsoft đã đưa cho người dùng thêm một số hàm mới, với mục đích nhằm khắc phục cũng như bổ sung thêm các tính năng mới cho Excel. Và hàm XLOOKUP là một trong những hàm xuất hiện trong lần cập nhật này. Vậy hàm này có những điểm gì nổi bật và thú vị hơn các hàm tìm kiếm khác như VLOOKUP, HLOOKUP,…Hãy cùng Học Excel Online tìm hiểu trong bài viết này.

Một số điểm thú vị về hàm XLOOKUP

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 và nếu như bạn đang cần thì có thể tham khảo ngay dưới đây.

Hàm tìm kiếm vlookup và hlookup trong excel

Thế XLOOKUP nó có gì hay ho hơn? Hãy cùng lướt qua bài viết này của Học Excel Online

Cú phát hàm XLOOKUP

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

mot-so-dieu-thu-vi-ve-ham-Xlookup-1

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 tìm kiếm VLOOKUP hay HLOOKUP thì mình nghĩ sẽ không khó khăn gì với việc đón nhận hà m XLOOKUP này. Các đối số gần như tuong tự. Vậy hàm XLOOKUP này nó làm được những gì?

Một số điểm thú vị của hàm XLOOKUP

Trong phần này Học Excel Online sẽ giới thiệu một số điểm thú vị của hàm XLOOKUP.

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

Ta đã biết thì một trong những hạn chế của hàm VLOOKUP là kết quả trả về của hàm 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.

Hướng dẫn sử dụng kết hợp vlookup và choose: một cách khác để tìm kiếm từ phải qua trái

hay nâng cao hơn thì bạn cần phải tìm tới hàm INDEX kết hợp với hàm MATCH:

Hàm index và hàm match trong excel – sự thay thế tốt hơn cả hàm vlookup

Thì với hàm XLOOKUP đã khắc phục được điều này, bạn xem ví dụ sau, địa thế căn cứ vào đơn giá để tìm tên hàng hoá :

mot-so-dieu-thu-vi-ve-ham-Xlookup-2

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

mot-so-dieu-thu-vi-ve-ham-Xlookup-3

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 hoàn toàn có thể trả về một mảng giá trị, đơn cử như sau :

mot-so-dieu-thu-vi-ve-ham-Xlookup-4

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 ở ô I6I8.

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

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

mot-so-dieu-thu-vi-ve-ham-Xlookup-5

Nếu như bạn chưa kịp cập nhật để có hàm này thì Học Excel Online gợi ý cho bạn để cũng có thể ra kết quả với việc tìm kiếm khi nối các giá trị như trên bằng hàm INDEX + MATCH:

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

Vừa rồi ta vừa triển khai tìm kiếm với nhiều điều kiện kèm theo khác nhau bằng cách nối những giá trị đó lại để tìm kiếm. Không những thế hàm XLOOKUP còn hoàn toàn có thể tìm kiếm cả mảng giá trị. Bạn tìm hiểu thêm ví dụ sau đây để hiểu được rõ hơn .

mot-so-dieu-thu-vi-ve-ham-Xlookup-6

Mình có sự dụng hàm UNIQUE – một hàm để vô hiệu giá trị trùng lặp và trả về list duy nhất .
Một chú ý quan tâm 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 bạn 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 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).

Sử dụng các hàm khác kết hợp với XLOOKUP

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

mot-so-dieu-thu-vi-ve-ham-Xlookup-7

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

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

Ở phần này, Học Excel Online sẽ ra mắt tới những bạn sự linh động của hàm XLOOKUP này ở hiệu quả trả về của nó. Ví dụ sau sẽ làm điều đó

8 1

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

Một số điểm thú vị khác

Sử dụng XLOOKUP lồng XLOOKUP

Ví dụ sau sẽ triển khai 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 tiên phong tới lúc hàng hoá C có phát sinh lần ở đầu cuối .

mot-so-dieu-thu-vi-ve-ham-Xlookup-8

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 tất cả chúng ta triển khai 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 tác dụng 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 mình thực thi chèn thêm cột vào giữa cột A và B thì hiệu quả ở ô D3 sử dụng hàm XLOOKUP không bị đổi khác còn với ô D2 sử dụng hàm VLOOKUP thì hiệu quả đã bị đổi khác ( dẫn tới sai ). Tương tự với hàm HLOOKUP, khi mình chèn thêm 1 dòng vào giữa dòng 7 và 8 thì hiệu quả ở ô H6 sử dụng hàm XLOOKUP không bị biến hóa còn ô H5 sử dụng hàm HLOOKUP thì tác dụng đã bị đổi khác ( dẫn tới sai ) .

mot-so-dieu-thu-vi-ve-ham-Xlookup-9

Ta cũng khắc phục được điều đó với hàm INDEX + MATCH, VLOOKUP + MATCH, HLOOKUP + MATCH, … .

Hàm hlookup, nên sử dụng hlookup hay index kết hợp match sẽ hay hơn

Kết luận

Như vậy qua bài viết này của Học Excel Online bạn đã có thể nắm được cách vận dụng hàm XLOOKUP – một hàm mới được Microsoft cập nhật cho Office 365 bản Insider. Về cơ bản có hàm sẽ có cách thức hoạt động tương đối giống với các hàm dò tìm mà ta đã biết như VLOOKUP, HLOOKUP,…Tuy nhiên cũng có một số điểm khác biệt, một số điểm mới, thú vị đặc biệt là linh hoạt hơn trong giá trị tìm kiếm cũng như là giá trị trả về. Điều này đã khắc phục được những hạn chế của những hàm dò tìm khác.

Với phần Comnent bên dưới, chúng tôi hy vọng sẽ nhận được thêm những góp ý cho bài viết cũng như nếu như bạn phát hiện ra các điều thú vị khác nữa về hàm này 😊

Giới thiệu hàm xlookup dành cho office 365 insiders

Hướng dẫn 3 cách tham chiếu ngược từ phải qua trái trong excel

Hướng dẫn sử dụng kết hợp vlookup và choose: một cách khác để tìm kiếm từ phải qua trái

Leave a Reply

Email của bạn sẽ không được hiển thị công khai.