Hàm XLOOKUP: Có thể bạn chưa biết hết tính năng và ứng dụng

Liên hệ QC

ptm0412

Bad Excel Member
Thành viên BQT
Administrator
Tham gia
4/11/07
Bài viết
13,699
Được thích
36,169
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant

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

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

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



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

2. Dò tìm theo dòng giống HLOOKUP

=XLOOKUP(B32,C17:N17,C23:N23)

1665295516793.png

3. Dò tìm ra nhiều kết quả

=XLOOKUP(G3,$B$2:$B$13,$A$2:$D$13)

1665297578444.png

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

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

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

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

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

- 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
- Tính các cột khác

1665300722445.png
 
Lần chỉnh sửa cuối:
THÍ DỤ ỨNG DỤNG XLOOKUP
1. Tính tổng, tính doanh thu cao nhất và mặt hàng có DT cao nhất

Mặc dù có thể dùng Pivot table để tính tổng (sum), doanh thu cao nhất (Max) rất gọn gàng, nhanh chóng, nhưng pivot table không lấy ra được tên mặt hàng có doanh thu cao nhất.

Tính tổng: tương tự như bài trên cho Food, fill xuống cho các category khác
=SUM(XLOOKUP($J47,$C$40:$C$51,D$40: D$51):XLOOKUP($J47,$C$40:$C$51,D$40: D$51,,,-1))

1665463993010.png

Tính Doanh thu cao nhất (dùng Max thay vì Sum)

=MAX(XLOOKUP($J47,$C$40:$C$51,D$40: D$51):XLOOKUP($J47,$C$40:$C$51,D$40: D$51,,,-1))

1665464138289.png

Lấy tên mặt hàng có doanh thu cao nhất: Dùng XLookup dò tìm doanh thu cao nhất trong vùng doanh thu xác định bởi category, tra ra mặt hàng cũng trong vùng đã xác định


=XLOOKUP(L47,XLOOKUP(J47,$C$40:$C$51,$D$40:$D$51):XLOOKUP(J47,$C$40:$C$51,$D$40:$D$51,,,-1),XLOOKUP(J47,$C$40:$C$51,$B$40:$B$51):XLOOKUP(J47,$C$40:$C$51,$B$40:$B$51,,,-1))

1665464372633.png


2. Tra bảng giá nhiều tiêu chí
Giả sử có bảng giá áo theo loại, kích thước và màu sắc như sau:

1665464546475.png

Và có đơn hàng như sau, tìm giá để tính giá trị đơn hàng


=XLOOKUP(J58&K58&L58,$B$56:$B$100&$C$56:$C$100&$D$56:$D$100,$E$56:$E$100)

1665464666933.png

3. Thống kê theo tháng bất kỳ của các mặt hàng và tính tổng
Chọn tháng bắt đầu ở H31 và tháng cuối ở J31

I33 =XLOOKUP(G33,$B$18:$B$29,XLOOKUP($H$31,$D$17:$N$17,$D$18:$N$29):XLOOKUP($J$31,$D$17:$N$17,$D$18:$N$29))

Tính tổng = Sum I33

H33=SUM(XLOOKUP(G33,$B$18:$B$29,XLOOKUP($H$31,$D$17:$N$17,$D$18:$N$29):XLOOKUP($J$31,$D$17:$N$17,$D$18:$N$29)))

hoặc đơn giản là =SUM(I33#)


1665465661124.png

Nếu chọn lại từ tháng 7 đến tháng 12 (Jul to Dec)

1665465728534.png

Nói chung, ứng dụng của hàm XLookup là rất nhiều, chưa kể kết hợp với những hàm khác.
 

File đính kèm

  • XLookupFunction.xlsx
    17.1 KB · Đọc: 283
Web KT

Group

DIỄN ĐÀN GIẢI PHÁP EXCEL
Back
Top Bottom