Trong Excel, Hàm XLOOKUP mới sẽ thay thế cho hàm VLOOKUP, giúp phát huy được tính năng của hàm VLOOKUP và bổ sung thêm một số chức năng khác, đồng thời khắc phục lỗi của hàm VLOOKUP này. Vậy cú pháp hàm XLOOKUP là gì, sử dụng thế nào?
Ở lần cập nhật mới đây, Microsoft đã bổ sung và mang đến cho người dùng thêm một số hàm mới với mục đích khắc phục, bổ sung thêm các tính năng mới cho Excel. Trong những hàm đó, hàm XLOOKUP là một trong những cái tên được nhắc đến trong lần cập nhật mới nhất này.
Hàm XLOOKUP sẽ thay thế hàm VLOOKUP
1. XLOOKUP là gì?
Hàm XLOOKUP là hàm mới, đây là giải pháp mà sắp tới Microsoft sẽ chính thức đưa vào sử dụng để thay thế cho hàm VLOOKUP nhằm khắc phục một số hạn chế mà VLOOKUP đang gặp phải. Ngoài ra, XLOOKUP còn được sử dụng để thay thế hàm HLOOKUP, chẳng hạn XLOOKUP có thể nhìn sang trái, mặc định dò tìm kết quả chính xác và cho phép chỉ định phạm vi là ô thay vì số cột, trong khi đó VLOOKUP không hề dễ sử dụng và cũng không được linh hoạt như vậy.
Hiện tại, hàm XLOOKUP chỉ cho phép người dùng chương trình Insiders, do đó với bất cứ ai đang sử dụng Insiders thì đều có thể cập nhật, trải nghiệm và sử dụng ngay hàm XLOOKUP này. Thời gian tới, Microsoft sẽ sớm hoàn thiện và phát hành hàm này cho tất cả người dùng Office 365.
2. Hướng dẫn dùng hàm XLOOKUP trong Excel
1. Cú pháp hàm XLOOKUP
Cú pháp của hàm XLOOKUP như sau:
Hàm XLOOKUP sẽ có 5 đối số, trong đó chỉ có 3 đối số đầu tiên là bắt buộc còn lại 2 đối số sau là tuỳ chọn, cụ thể là:
- 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à ở đó người dùng 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
Ngoài ra, các bạn lưu ý thêm những thông tin sau:
- lookup_value: Có thể sẽ là 1 giá trị hoặc là 1 mảng giá trị bạn muốn tìm kiếm
- lookup_array và return_array: Cần được 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)
Như vậy, với hàm XLOOKUP mới này cơ bản các đối số gần tương tự với các hàm tìm kiếm VLOOKUP hay HLOOKUP, do vậy nếu các bạn nắm chắc cú pháp và cách sử dụng của 2 hàm này thì với XLOOKUP chắc chắn bạn sẽ dễ dàng tiếp cận và nắm bắt hơn.
2. Cách dùng hàm XLOOKUP
Hàm XLOOKUP có thể tìm đến bên trái
Như đã biết thì hạn chế của hàm VLOOKUP là chỉ cho 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ự, hàm HLOOKUP 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ò. Bạn cũng có thể khắc phục bằng cách sử dụng kết hợp thêm 1 số hàm khác như hàm CHOOSE chẳng hạn, hoặc nâng cao hơn, bạn có thể kết hợp hàm INDEX và hàm MATCH chẳng hạn.
Với hàm XLOOKUP hạn chế này đã được khắc phục, bạn có thể xem ví dụ cụ thể dưới đây để dễ hình dung. Ví dụ là bạn căn cứ vào đơn giá để tìm tên hàng hóa:
Hàm XLOOKUP có thể tìm kiếm từ cuối lên đầu (search last to first)
Dùng hàm XLOOKUP bạn có thể linh động trong việc tìm kiếm dữ liệu, ví dụ như bạ có thể tìm kiếm từ dưới lên. Việc tìm kiếm này cũng khắc phục được những hạn chế mà hàm VLOOKUP hay HLOOKUP còn gặp phải.
Với đối số thứ 5 của hàm sẽ là -1 (Search last-to-first).
Như vậy, với cùng Hàng hoá 1, thì công thức ở ô E3 có tham số thứ 5 là 1 (Search first-to-last) thì sẽ dò tìm từ đầu đến cuối trong lookup_array (nó tương tự như hàm VLOOKUP).
Tiếp theo là công thức ở ô E5 sẽ có tham số thứ 5 là -1 (Search last-to-first) do đó hàm sẽ thực hiện dò tìm kiếm từ cuối lên đầu của lookup_array (việc này thì hàm VLOOKUP không thể làm theo một cách thông thường như thế này được).
Hàm XLOOKUP trả về 1 mảng giá trị
Hàm XLOOKUP giúp bạn trả về một mảng giá trị, ví dụ:
Công thức ở ô I4 là:
=XLOOKUP(H2,A4:A7,A4:F7)
Như vậy, hàm sẽ thực hiện tìm kiếm giá trị trong ô H2 trong vùng từ A4:A7 và vùng trả về kết quả ở đây sẽ là vùng từ A4:F7. Tương tự, bạn sẽ thực với các công thức trong ô I6 và ô I8.
Hàm XLOOKUP nối các giá trị tìm kiếm
Hàm XLOOKUP còn 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 kết quả. Khi muốn tìm kiếm với nhiều điều kiện khác nhau, các bạn có thể nối các giá trị đó lại để thực hiện việc tìm kiếm. Hãy xem ví dụ minh họa trong ảnh dưới:
Hàm XLOOKUP tìm kiếm một mảng giá trị
Hàm XLOOKUP còn giúp người dùng thực hiện tìm kiếm cả mảng giá trị. Bạn xem ví dụ dưới đây để hiểu và dễ hình dung hơn:
Trong ví dụ trên có sử dụng hàm UNIQUE để loại bỏ giá trị trùng lặp, đồng thời trả về danh sách duy nhất.
Khi tìm kiếm một mảng giá trị thì kết quả trả về là 1 mảng giá trị theo chiều dọc (tức sẽ gồm nhiều hàng nhưng chỉ có 1 cột). Do đó bạn sẽ có 1 số đúc kết nhỏ là:
- Hàm XLOOKUP có thể trả 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 sẽ nhiều hàng và 1 cột).
- Mảng trả về này có thể 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ể xem lại cụ thể hơn qua ví dụ trên (ô I12) ở trên.
Sử dụng các hàm khác kết hợp với XLOOKUP
Hàm XLOOKUP cũng cho phép bạn kết hợp các hàm với nhau, sử dụng lồng ghép các hàm với nhau linh hoạt.
Ví dụ, bạn có thể dùng hàm XLOOKUP để trả về cả mảng giá trị như mô tả trong ảnh dưới, bạn có thể tính tổng số lượng của những hàng hóa phát sinh trong 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àm XLOOKUP giúp bạn nối chuỗi linh hoạt trong kết quả trả về. Bạn có thể thấy kết quả trả về không chỉ là những con số mà sẽ kết hợp, vận dụng hàm trả về cả mảng giá trị kết hợp với các chuỗi Text lồng trong kết quả trả về. Ví dụ như trong hình dưới bạn sẽ thấy việc lồng ghép, nối chuỗi linh hoạt trong kết quả trả về xem số lượng và thành tiền phát sinh lần đầu cũng như cuối cùng của hàng hóa phát sinh.
Dùng XLOOKUP lồng XLOOKUP
Bạn xem ví dụ minh họa ở dưới sẽ biết việc dùng XLOOKUP lồng XLOOKUP. Giả sử bạn cần tính tổng số lượng của hàng hóa tính từ lúc hàng hóa C có phát sinh đầu tiên cho đến lúc hàng hóa C có phát sinh lần cuối cùng.
XLOOKUP khắc phục lỗi chèn dòng, cột trong VLOOKUP và HLOOKUP
Dùng hàm VLOOKUP hay HLOOKUP khi người dùng muố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ì kết quả trả về của hàm sẽ bị sai. Nhưng nếu sử dụng hàm XLOOKUP thì sẽ khắc phục được lỗi này.
Ví dụ:
- Bạn muốn chèn thêm cột vào giữa cột A và B thì kết quả ở D3 khi dùng hàm XLOOKUP sẽ không bị thay đổi. Còn ô D2 khi sử dụng hàm VLOOKUP thì kết quả đã bị thay đổi và dẫn đến kết quả là sai.
- Tương tự, nếu dùng hàm HLOOKUP, khi bạn chèn thêm 1 dòng vào giữa dòng 7 và 8 -> kết quả ở ô H6 khi dùng hàm XLOOKUP không bị thay đổi. Còn ô H5 khi bạn dùng hàm HLOOKUP -> kết quả đã bị thay đổi và dẫn đến kết quả bị sai.
https://thuthuat.taimienphi.vn/ham-xlookup-trong-excel-su-dung-nhu-the-nao-53513n.aspx
Hàm XLOOKUP trong Excel là hàm mới, là sự kế thừa khắc phục những hạn chế mà cả hai hàm VLOOKUP và HLOOKUP còn mắc phải. Với một loạt tính năng và cách dùng cùng với các ví dụ trên đây phần nào giúp các bạn thấy những ưu điểm mà hàm XLOOKUP mang đến. Hãy trải nghiệm và sử dụng ngay hàm này nếu bạn đang dùng bản Insider cho Office 365 nhé!