Hàm XLOOKUP trong Excel: Cách dùng và ví dụ thực tế

Nếu đã từng sử dụng VLOOKUP trong Excel, có thể bạn đã gặp những tình huống khá khó chịu:

• Muốn tìm dữ liệu nằm bên trái cột tra cứu.
• Thêm một cột mới khiến công thức trả về sai dữ liệu.
• Phải ngồi đếm xem cột cần lấy kết quả là cột thứ 3, thứ 4 hay thứ 7.
• Hoặc phải kết hợp INDEX và MATCH chỉ để thực hiện một phép tra cứu linh hoạt hơn.

XLOOKUP được Microsoft phát triển để giải quyết nhiều hạn chế của các hàm tra cứu truyền thống.

Thay vì yêu cầu:

“Tìm trong bảng này rồi trả về cột thứ 4.”

XLOOKUP cho phép bạn nói rõ:

“Tìm giá trị này trong cột A và trả về dữ liệu tương ứng trong cột D.”

Công thức nhờ đó dễ đọc và dễ quản lý hơn.

Trong bài viết này, chúng ta sẽ tìm hiểu hàm XLOOKUP trong Excel là gì, cách sử dụng XLOOKUP từ cơ bản đến nâng cao, đồng thời thực hành với các tình huống thường gặp như tra cứu sản phẩm, tìm nhân viên, dò dữ liệu giữa hai Sheet, tìm từ phải sang trái và XLOOKUP nhiều điều kiện.

Hàm XLOOKUP trong Excel là gì?

XLOOKUP là hàm tra cứu trong Excel cho phép tìm một giá trị trong một vùng và trả về dữ liệu tương ứng từ một vùng khác.

Ví dụ bạn có bảng:

Bạn nhập:

SP003

XLOOKUP có thể tìm mã này trong cột Mã SP và trả về:

Bàn phím

hoặc:

750.000

tùy cột kết quả được chỉ định.

Nếu đã biết VLOOKUP, bạn có thể hình dung XLOOKUP theo cách đơn giản:

VLOOKUP: Tìm trong một bảng → trả về cột thứ N.

XLOOKUP: Tìm trong vùng A → trả về giá trị tương ứng từ vùng B.

Đây là khác biệt rất quan trọng.

Cú pháp hàm XLOOKUP

Cú pháp:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Nhìn qua có vẻ dài, nhưng trong phần lớn trường hợp bạn chỉ cần ba đối số đầu tiên.

lookup_value

Giá trị cần tìm.

Ví dụ:

E2

Nếu E2 chứa SP003, Excel sẽ tìm SP003.

lookup_array

Vùng chứa giá trị cần tìm.

Ví dụ:

A2:A100

return_array

Vùng chứa dữ liệu cần trả về.

Ví dụ:

C2:C100

Công thức:

=XLOOKUP(E2,A2:A100,C2:C100)

Có thể đọc bằng tiếng Việt:

Tìm E2 trong A2:A100 và trả về dữ liệu tương ứng trong C2:C100.

Đây là cách dễ nhất để hiểu XLOOKUP.

Ví dụ XLOOKUP cơ bản

Giả sử bảng:

Tại E2 nhập:

SP003

Muốn trả về tên sản phẩm:

=XLOOKUP(E2,A2:A5,B2:B5)

Kết quả:

Bàn phím

Muốn trả về giá:

=XLOOKUP(E2,A2:A5,C2:C5)

Kết quả:

750.000

Không cần đếm cột.

Không cần chọn toàn bộ A:C.

Chỉ cần xác định:

Tìm ở đâu → trả kết quả ở đâu.

XLOOKUP khác VLOOKUP như thế nào?

Đây là câu hỏi rất phổ biến.

Giả sử muốn tìm giá sản phẩm.

VLOOKUP

=VLOOKUP(E2,A2:C100,3,FALSE)

XLOOKUP

=XLOOKUP(E2,A2:A100,C2:C100)

VLOOKUP yêu cầu:

• Giá trị cần tìm.
• Toàn bộ bảng.
• Số thứ tự cột.
• Kiểu tìm kiếm.

XLOOKUP cơ bản yêu cầu:

• Giá trị cần tìm.
• Cột cần tìm.
• Cột cần trả về.

Điều này giúp công thức dễ đọc hơn.

XLOOKUP mặc định tìm chính xác

Với VLOOKUP, người mới thường phải nhớ:

FALSE

để tìm chính xác.

Ví dụ:

=VLOOKUP(E2,A2:C100,3,FALSE)

Trong XLOOKUP, exact match là hành vi mặc định.

Vì vậy:

=XLOOKUP(E2,A2:A100,C2:C100)

đã phù hợp với nhiều trường hợp tìm chính xác như:

• Mã sản phẩm.
• Mã nhân viên.
• ID khách hàng.
• Mã đơn hàng.
• Mã sinh viên.

Điều này giúp giảm một lỗi khá phổ biến của người mới sử dụng VLOOKUP.

XLOOKUP xử lý dữ liệu không tìm thấy

Giả sử bạn tìm:

SP010

nhưng danh mục chỉ có SP001–SP004.

Công thức:

=XLOOKUP(E2,A2:A5,C2:C5)

có thể trả về lỗi #N/A nếu không có kết quả phù hợp.

XLOOKUP cung cấp đối số [if_not_found].

Ví dụ:

=XLOOKUP(E2,A2:A5,C2:C5,"Không tìm thấy")

Nếu SP010 không tồn tại:

Không tìm thấy

Điều này rất tiện vì trong nhiều trường hợp bạn không cần bọc thêm IFERROR chỉ để xử lý tình huống không tìm thấy.

XLOOKUP giữa hai Sheet

Đây là ứng dụng cực kỳ phổ biến trong công việc.

Giả sử:

Sheet1 – Đơn hàng

Sheet2 – Danh mục

Tại Sheet1, bạn có thể nhập:

=XLOOKUP(A2,Sheet2!A:A,Sheet2!C:C,"Không tìm thấy")

Ý nghĩa:

Tìm mã trong A2.

↓

Tìm mã đó trong cột A của Sheet2.

↓

Trả về giá tương ứng trong cột C.

↓

Nếu không có → hiển thị “Không tìm thấy”.

Sau đó kéo công thức xuống các dòng còn lại.

Đây là cách rất thuận tiện để đối chiếu dữ liệu giữa nhiều Sheet.

XLOOKUP có cần cố định vùng bằng dấu $ không?

Nếu sử dụng:

=XLOOKUP(E2,A2:A100,C2:C100)

và kéo công thức xuống, các vùng có thể dịch chuyển.

Trong nhiều bảng dữ liệu cố định, bạn nên sử dụng:

=XLOOKUP(E2,$A$2:$A$100,$C$2:$C$100,"Không tìm thấy")

Khi kéo xuống:

E2 → E3 → E4…

nhưng:

$A$2:$A$100

và:

$C$2:$C$100

vẫn giữ nguyên.

Bạn có thể sử dụng phím F4 khi chỉnh tham chiếu trong công thức để chuyển đổi kiểu tham chiếu phù hợp.

XLOOKUP tìm từ phải sang trái

Đây là một ưu điểm lớn so với VLOOKUP truyền thống.

Giả sử bảng:

Bạn có mã:

NV002

và muốn trả về:

Trần Bình

VLOOKUP thông thường không thuận tiện vì cột kết quả nằm bên trái cột dò.

XLOOKUP:

=XLOOKUP(E2,B2:B4,A2:A4)

Excel tìm E2 trong cột B và trả về dữ liệu tương ứng trong cột A.

Kết quả:

Trần Bình

Không cần đảo bảng.

Không cần tạo cột phụ.

Không cần INDEX + MATCH chỉ để thực hiện việc tra cứu trái.

XLOOKUP trả về nhiều cột

Giả sử bảng:

Bạn nhập mã:

NV002

và muốn trả về cùng lúc:

Tên + Phòng ban + Lương

Trong Excel hỗ trợ dynamic arrays, có thể sử dụng:

=XLOOKUP(F2,A2:A4,B2:D4)

Kết quả có thể spill sang nhiều ô:

Bình | Sales | 18.000.000

Đây là khả năng rất hữu ích khi xây dựng bảng tra cứu hoặc dashboard.

XLOOKUP nhiều điều kiện

Giả sử bạn có bảng giá:

Bạn muốn tìm:

SP001 + Đà Nẵng

Một cách có thể sử dụng là tạo mảng điều kiện:

=XLOOKUP(1,(A2:A100=E2)*(B2:B100=F2),C2:C100,"Không tìm thấy")

Trong đó:

A2:A100=E2

kiểm tra điều kiện mã sản phẩm.

B2:B100=F2

kiểm tra khu vực.

Khi cả hai đúng:

TRUE * TRUE = 1

XLOOKUP tìm 1 và trả về giá tương ứng.

Đây là một kỹ thuật rất mạnh khi cần tra cứu theo nhiều tiêu chí.

Tuy nhiên, với bảng lớn hoặc mô hình phức tạp, hãy ưu tiên công thức dễ đọc và dễ bảo trì; FILTER hoặc một cột khóa rõ ràng đôi khi có thể phù hợp hơn.

XLOOKUP với ký tự đại diện

XLOOKUP hỗ trợ chế độ tìm kiếm sử dụng wildcard thông qua match_mode.

Các wildcard quen thuộc gồm:

* → đại diện cho nhiều ký tự.

? → đại diện cho một ký tự.

Ví dụ bạn muốn tìm tên chứa từ:

Laptop

Có thể sử dụng:

=XLOOKUP("*Laptop*",A2:A100,B2:B100,"Không tìm thấy",2)

Trong đó:

2

ở match_mode cho phép wildcard match.

Tính năng này hữu ích khi dữ liệu chứa chuỗi dài hơn giá trị bạn đang tìm.

Match Mode trong XLOOKUP

Đối số thứ năm:

[match_mode]

giúp kiểm soát cách Excel xác định kết quả.

Các chế độ gồm:

0 → Exact match.

-1 → Exact match hoặc giá trị nhỏ hơn tiếp theo.

1 → Exact match hoặc giá trị lớn hơn tiếp theo.

2 → Wildcard match.

Mặc định là:

0

Vì vậy với phần lớn bài toán mã sản phẩm hoặc ID, bạn không cần nhập match_mode.

Ví dụ XLOOKUP tìm mức chiết khấu

Giả sử doanh nghiệp có bảng:

Nhân viên đạt doanh số:

65.000.000

Bạn muốn lấy mức gần nhất thấp hơn.

Có thể sử dụng XLOOKUP với chế độ exact match hoặc next smaller item:

=XLOOKUP(D2,A2:A5,B2:B5,"Không tìm thấy",-1)

Kết quả:

5%

Đây là ví dụ cho thấy XLOOKUP không chỉ dùng để tìm mã chính xác.

Search Mode trong XLOOKUP

Đối số cuối cùng:

[search_mode]

quy định cách Excel tìm kiếm.

Một số giá trị quan trọng:

1 → tìm từ đầu đến cuối.

-1 → tìm từ cuối lên đầu.

2 → binary search trên dữ liệu sắp xếp tăng dần.

-2 → binary search trên dữ liệu sắp xếp giảm dần.

Trong công việc thông thường, 1 và -1 dễ áp dụng nhất.

XLOOKUP tìm kết quả cuối cùng

Giả sử một khách hàng có nhiều giao dịch:

Nếu dùng XLOOKUP thông thường:

=XLOOKUP(E2,A2:A5,C2:C5)

Excel tìm từ đầu và có thể trả về giao dịch đầu tiên của KH001.

Nhưng nếu muốn kết quả xuất hiện cuối cùng:

=XLOOKUP(E2,A2:A5,C2:C5,"Không tìm thấy",0,-1)

-1 ở search_mode yêu cầu tìm từ cuối lên đầu.

Kết quả với KH001:

6.000.000

Đây là ứng dụng rất hữu ích với:

• Giá gần nhất.
• Trạng thái cuối.
• Giao dịch mới nhất trong bảng đã được tổ chức phù hợp.
• Lần cập nhật cuối cùng.

XLOOKUP kết hợp IF

XLOOKUP có thể kết hợp với IF để xây dựng logic.

Ví dụ:

Nếu ô E2 trống, không thực hiện tìm kiếm.

Nếu có mã, mới XLOOKUP.

=IF(E2="","",XLOOKUP(E2,A2:A100,C2:C100,"Không tìm thấy"))

Điều này giúp bảng sạch hơn khi người dùng chưa nhập dữ liệu.

XLOOKUP kết hợp IFERROR

XLOOKUP đã có [if_not_found], vì vậy trong trường hợp chỉ cần xử lý “không tìm thấy”, thường không cần IFERROR.

Ví dụ:

=XLOOKUP(E2,A2:A100,C2:C100,"Không tìm thấy")

Tuy nhiên, IFERROR vẫn có thể hữu ích nếu công thức lớn hơn có khả năng phát sinh những loại lỗi khác.

Ví dụ:

=IFERROR(XLOOKUP(...),"Có lỗi dữ liệu")

Nhưng hãy thận trọng.

Nếu IFERROR che toàn bộ lỗi, bạn có thể không phát hiện được vấn đề thực sự trong dữ liệu.

XLOOKUP kết hợp SUM

XLOOKUP thường trả về giá trị tra cứu, còn nếu bạn cần cộng tất cả các dòng thỏa điều kiện, SUMIF/SUMIFS có thể phù hợp hơn.

Ví dụ bạn muốn:

Tổng doanh số của KH001

Nếu KH001 xuất hiện 20 lần, đừng cố ép XLOOKUP làm nhiệm vụ tổng hợp.

Sử dụng:

=SUMIF(A:A,E2,C:C)

Đây là một nguyên tắc quan trọng khi học Excel:

Không phải vì XLOOKUP mạnh mà mọi bài toán đều nên dùng XLOOKUP.

XLOOKUP và INDEX MATCH

Trước XLOOKUP, INDEX + MATCH là lựa chọn rất phổ biến để khắc phục hạn chế của VLOOKUP.

Ví dụ:

=INDEX(C2:C100,MATCH(E2,A2:A100,0))

XLOOKUP tương ứng:

=XLOOKUP(E2,A2:A100,C2:C100)

XLOOKUP thường dễ đọc hơn.

Tuy nhiên, INDEX + MATCH vẫn đáng học vì:

• Xuất hiện trong rất nhiều workbook cũ.
• Hữu ích khi làm việc với những phiên bản Excel không hỗ trợ XLOOKUP.
• Giúp bạn hiểu sâu hơn cách tra cứu dữ liệu.

VLOOKUP vs XLOOKUP: nên dùng hàm nào?

Nếu đang sử dụng phiên bản Excel hỗ trợ XLOOKUP, đây thường là lựa chọn rất tốt cho những bài toán tra cứu mới.

Nhưng vẫn nên biết VLOOKUP vì bạn chắc chắn có thể gặp nó trong các file Excel được tạo trước đây.

Tại sao Excel không có XLOOKUP?

Nếu nhập:

=XLOOKUP(

nhưng Excel không nhận hàm, một nguyên nhân có thể là phiên bản Excel bạn đang sử dụng không hỗ trợ XLOOKUP.

XLOOKUP có trong các phiên bản Microsoft 365 và nhiều phiên bản Excel hiện đại, nhưng không tồn tại trong một số bản Excel cũ.

Trong trường hợp đó, bạn có thể sử dụng:

• VLOOKUP.
• HLOOKUP.
• INDEX + MATCH.

Nếu thường xuyên trao đổi workbook với người khác, hãy kiểm tra khả năng tương thích trước khi sử dụng XLOOKUP trên toàn bộ file.

Những lỗi XLOOKUP thường gặp

1. #N/A

Thường do không tìm thấy giá trị.

Kiểm tra:

• Giá trị có tồn tại không?
• Có khoảng trắng thừa không?
• Number và Text có cùng kiểu không?

2. Lookup Array và Return Array không tương ứng

Ví dụ:

Lookup:

A2:A100

Return:

C2:C50

Hai vùng không cùng kích thước.

Hãy kiểm tra lại cấu trúc.

3. Dữ liệu số và Text

Ví dụ một bên là:

1001

một bên là text:

"1001"

Nhìn giống nhau nhưng có thể gây lỗi tra cứu.

4. Khoảng trắng

Dữ liệu import từ website hoặc hệ thống khác có thể chứa khoảng trắng hoặc ký tự không mong muốn.

5. Dùng sai Match Mode

Đừng sử dụng approximate match nếu bài toán cần tìm mã chính xác.

10 ứng dụng XLOOKUP trong công việc

1. Tra giá sản phẩm

Mã sản phẩm → Giá.

2. Tra tên sản phẩm

Mã → Tên.

3. Tra thông tin nhân viên

Mã NV → Phòng ban.

4. Tra lương

Mã NV → Lương.

5. Tra khách hàng

Mã KH → Tên/SĐT.

6. Đối chiếu hai danh sách

Kiểm tra dữ liệu từ hai bảng.

7. Tra trạng thái đơn hàng

Mã đơn → Trạng thái.

8. Tìm bản ghi cuối cùng

Mã khách hàng → Giá trị mới nhất theo thứ tự dữ liệu.

9. Tra nhiều điều kiện

Sản phẩm + khu vực → Giá.

10. Tạo bảng tra cứu tự động

Nhập một mã → trả về nhiều thông tin liên quan.

Bài tập XLOOKUP cho người mới

Tạo bảng:

Bài 1

Nhập:

SP03

Dùng XLOOKUP trả về:

Bàn phím

Bài 2

Nhập SP04 và trả về:

4.500.000

Bài 3

Nhập SP10.

Hiển thị:

Không tìm thấy

thay vì #N/A.

Bài 4

Nhập mã và trả về cùng lúc:

Tên + Nhóm + Giá.

Bài 5

Đưa bảng danh mục sang Sheet2 rồi thực hiện tra cứu từ Sheet1.

Bài 6

Đảo vị trí cột Mã sang bên phải Tên và thử dùng XLOOKUP để tra từ phải sang trái.

Nếu hoàn thành được 6 bài này mà không nhìn công thức mẫu, bạn đã hiểu khá tốt XLOOKUP cơ bản.

Mẹo nhớ công thức XLOOKUP

Đừng học thuộc toàn bộ cú pháp ngay.

Trước tiên hãy nhớ:

=XLOOKUP(CẦN_TÌM,TÌM_Ở_ĐÂU,LẤY_KẾT_QUẢ_Ở_ĐÂU)

Ví dụ:

=XLOOKUP(E2,A:A,C:C)

Đọc:

Tìm E2 → trong cột A → lấy kết quả ở cột C.

Sau khi thành thạo ba phần này, học tiếp:

if_not_found

↓

match_mode

↓

search_mode

Cách học từng lớp sẽ dễ hơn nhiều so với cố nhớ sáu đối số ngay từ đầu.

Khi nào không nên dùng XLOOKUP?

XLOOKUP rất mạnh nhưng không phải công cụ duy nhất.

Không nên cố sử dụng XLOOKUP khi bài toán thực chất là:

Tính tổng theo điều kiện

→ SUMIF/SUMIFS.

Đếm theo điều kiện

→ COUNTIF/COUNTIFS.

Trả về tất cả bản ghi thỏa điều kiện

→ FILTER thường phù hợp hơn.

Phân tích bảng dữ liệu lớn

→ Có thể cần PivotTable, Power Query hoặc công cụ khác.

Học Excel tốt không phải nhớ thật nhiều công thức.

Mà là biết:

Bài toán này nên sử dụng công cụ nào?

Lộ trình học hàm tra cứu Excel

Nếu mới bắt đầu, bạn có thể học theo thứ tự:

1. VLOOKUP

Hiểu nguyên lý tra cứu.

↓

2. XLOOKUP

Học cách tra cứu linh hoạt hơn.

↓

3. INDEX + MATCH

Hiểu phương pháp tra cứu truyền thống mạnh mẽ.

↓

4. FILTER

Trả về nhiều kết quả.

↓

5. SUMIFS / COUNTIFS

Tổng hợp theo nhiều điều kiện.

↓

6. PivotTable

Phân tích dữ liệu.

↓

7. Power Query

Làm sạch và tự động hóa quy trình xử lý dữ liệu.

Đây sẽ thực tế hơn việc học các hàm Excel hoàn toàn ngẫu nhiên.

Kết luận

XLOOKUP là một trong những hàm tra cứu rất hữu ích trong Excel hiện đại.

Công thức cơ bản:

=XLOOKUP(lookup_value,lookup_array,return_array)

Có thể hiểu đơn giản:

Tìm gì → tìm ở đâu → trả về dữ liệu ở đâu.

So với VLOOKUP, XLOOKUP giải quyết được nhiều hạn chế như:

• Không cần đếm số thứ tự cột.
• Có thể tìm từ phải sang trái.
• Mặc định tìm chính xác.
• Có thể xử lý trường hợp không tìm thấy ngay trong hàm.
• Có thể tìm từ cuối lên đầu.
• Có thể trả về nhiều cột trong các phiên bản Excel hỗ trợ dynamic arrays.

Tuy nhiên, đừng chỉ đọc công thức.

Hãy mở Excel và tự tạo một bảng gồm:

Mã sản phẩm → Tên → Giá.

Sau đó thử lần lượt:

Tra giá → tra tên → tra từ phải sang trái → tra 2 Sheet → xử lý không tìm thấy → nhiều điều kiện.

Khi tự làm được những tình huống đó, bạn không còn chỉ “biết công thức XLOOKUP”.

Bạn đã bắt đầu biết cách ứng dụng XLOOKUP để giải quyết dữ liệu thực tế trong Excel.

Câu hỏi thường gặp về XLOOKUP