Hàm XLOOKUP: Có thể bạn chưa biết hết tính năng
Hàm XLOOKUP xuất hiện đã lâu, các bạn đã dùng nhiều, có khi dùng nhiều hơn tôi (vì tôi đã nghỉ việc, mà bản chất công việc của tôi lại không phải là dùng Excel). Nội trong cú pháp hàm:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Không hẳn bạn nào cũng dùng hết các tham số, chưa nói đến dùng hết các giá trị có sẵn của tham số.
Bạn nói Xlookup hay hơn Vlookup vì có thể tìm từ phải qua trái, và ứng dụng thay cho vlookup, nhưng bạn có biết là Xlookup cũng có thể ứng dụng thay cho HLookup nữa không?
Tiếp theo, nếu bạn biết Xlookup vừa có thể tìm theo cột như Vlookup, vừa tìn theo dòng như HLookup, vậy bạn đã thử tìm trong bảng tra 2 chiều, vừa tra dòng vừa tra cột, lấy kết quả như hàm Index chưa?
Còn nữa, nếu bạn biết Xlookup có thể tìm xuôi lấy giá trị đầu, tìm ngược để lấy giá trị cuối, vậy bạn có biết cách lấy nguyên khối kết quả tìm được không?
Hãy đọc 3 mục A, B, C dưới đây xem bạn thuộc nhóm nào nhé.
A. Các tính năng nhiều người đã biết:
1. Hàm có thể dò tìm từ trái qua phải, từ phải qua trái, chèn cột vào giữa mà không bị lỗi công thức như VLOOKUP thông thường.
Thí dụ tìm từ phải qua trái:
=XLOOKUP(E2,C2:C13,B2:B13)
![1665294084115.png 1665294084115.png](https://www.giaiphapexcel.com/diendan/data/attachments/238/238866-66887555bcc768e441de595610dbb899.jpg)
2. Tìm từ dưới lên trên:
Sử dụng tham số Search mode = -1
=XLOOKUP(E3,C2:C13,B2:B13,,,-1)
![1665294659115.png 1665294659115.png](https://www.giaiphapexcel.com/diendan/data/attachments/238/238867-13cc835ae7523b78563fc9034e58f43a.jpg)
3. Giá trị nếu tìm không thấy
Sử dụng tham số If not Found
=XLOOKUP(E4,C2:C13,B2:B13,"None",,-1)
![1665295817487.png 1665295817487.png](https://www.giaiphapexcel.com/diendan/data/attachments/238/238869-6f925dcda849d0087083c0d07593cb73.jpg)
B. Tính năng không phải ai cũng biết
1. Tìm theo ký tự đại diện
Sử dụng ký tự *, ?, ~ và tham số Match mode = 2
=XLOOKUP(E5,$C$2:$C$13,$B$2:$B$13,"None",2,-1)
![1665296226899.png 1665296226899.png](https://www.giaiphapexcel.com/diendan/data/attachments/238/238870-06a978c3ec8055fa03aa5056672508ee.jpg)
2. Dò tìm theo dòng giống HLOOKUP
=XLOOKUP(B32,C17:N17,C23:N23)
![1665295516793.png 1665295516793.png](https://www.giaiphapexcel.com/diendan/data/attachments/238/238868-2d8186109e63a770410cef1b6bb24274.jpg)
3. Dò tìm ra nhiều kết quả
=XLOOKUP(G3,$B$2:$B$13,$A$2:$D$13)
![1665297578444.png 1665297578444.png](https://www.giaiphapexcel.com/diendan/data/attachments/238/238873-c2139e3802d1160813240197e91b28f9.jpg)
C. Tính năng có thể rất ít người biết
1. Dò tìm như Index
=XLOOKUP(B32,C17:N17,XLOOKUP(A32,B18:B29,C18:N29))
![1665296869958.png 1665296869958.png](https://www.giaiphapexcel.com/diendan/data/attachments/238/238871-90aa7a0a9e55fd499ffbf169d4125272.jpg)
2. Liệt kê hết kết quả tìm thấy
(Dữ liệu phải được sort theo Category, liệt kê hết các mặt hàng thuộc category Beverage, đơn vị tính và giá)
=XLOOKUP(H5,E2:E13,B2: D13):XLOOKUP(H5,E2:E13,B2: D13,,,-1)
![1665298835519.png 1665298835519.png](https://www.giaiphapexcel.com/diendan/data/attachments/238/238876-86baf94bd55624b2d76aaa2b765c7dad.jpg)
3. Liệt kê kết quả dò tìm 2 chiều
(Dữ liệu phải được sort theo Category, liệt kê hết các giá trị tháng 7 của Beverage)
=XLOOKUP(F32,D17:O17,XLOOKUP(E32,C18:C29,D18:O29):XLOOKUP(E32,C18:C29,D18:O29,,,-1))
![1665298331674.png 1665298331674.png](https://www.giaiphapexcel.com/diendan/data/attachments/238/238875-b1f98b2f800077ad710c34d1b407ecda.jpg)
4. Tính tổng các kết quả tìm thấy
=SUM(XLOOKUP(G32,D17:O17,XLOOKUP(F32,C18:C29,D18:O29):XLOOKUP(F32,C18:C29,D18:O29,,,-1)))
![1665299223595.png 1665299223595.png](https://www.giaiphapexcel.com/diendan/data/attachments/238/238877-c2d5ac5f74d51a1dadb9237b66ac416b.jpg)
5. Dò tìm ra kết quả 2 chiều
- Liệt kê mặt hàng theo category (như trên)
=XLOOKUP(J39,C40:C51,B40:B51):XLOOKUP(J39,C40:C51,B40:B51,,,-1)
![1665300472047.png 1665300472047.png](https://www.giaiphapexcel.com/diendan/data/attachments/238/238879-780b90e2d3ad717793cd97fbd1c1e092.jpg)
- Tính doanh số các mặt hàng, sử dụng dấu #
=XLOOKUP($J40#,$B$40:$B$51,XLOOKUP(K39,$D$39:$H$39,$D$40:$H$51))
![1665300633359.png 1665300633359.png](https://www.giaiphapexcel.com/diendan/data/attachments/238/238880-5216a088c7c049c49bb762aca66f1825.jpg)
- Tính các cột khác
![1665300722445.png 1665300722445.png](https://www.giaiphapexcel.com/diendan/data/attachments/238/238881-307455d182165fd4191b355a66ba327c.jpg)
Lần chỉnh sửa cuối: