Nếu bạn đã dùng Excel một thời gian, bạn có thể đã dựa vào VLOOKUP hoặc INDEX/MATCH vô số lần. Những ngày đấu tranh với giới hạn của VLOOKUP và độ phức tạp của INDEX/MATCH đã qua. Gặp gỡ XLOOKUP—hàm tra cứu hiện đại của Excel linh hoạt, đơn giản và đủ mạnh để thay thế cả hai.
Trong hướng dẫn này, chúng tôi sẽ chia sẻ ba lý do tại sao XLOOKUP là người bạn mới tốt nhất của bạn. Bạn sẽ thấy nó làm cho các tác vụ tra cứu hàng ngày nhanh hơn, sạch hơn và thông minh hơn—không cần giải pháp thay thế.
Cú Pháp:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Hàm XLOOKUP là hàm tra cứu hiện đại của Excel tìm giá trị trong một cột (hoặc hàng) và trả về giá trị liên quan từ cột (hoặc hàng) khác. Nó được thiết kế để thay thế VLOOKUP và INDEX/MATCH bằng cách đơn giản và linh hoạt hơn.
Lý Do 1: XLOOKUP Tra Cứu Ở Bất Kỳ Hướng Nào (Trái Hoặc Phải)
VLOOKUP chỉ có thể tìm kiếm bên phải cột tra cứu—nếu cột tra cứu không phải cột đầu tiên trong table array, công thức thất bại. INDEX/MATCH giải quyết điều này nhưng khó đọc hơn vì cần lồng hai hàm, có thể rối rắm với tập dữ liệu lớn.
XLOOKUP đảo ngược điều này: nó tách biệt phạm vi tra cứu khỏi phạm vi trả về, nên bạn có thể tra trái, phải, lên, hoặc xuống phiền toái.
Giả sử bạn có tập dữ liệu bán hàng và muốn tìm Product ID bằng cách tìm Product Name.
VLOOKUP:
- Chọn một ô và chèn công thức sau.
=VLOOKUP(H5,A2:F23,1,FALSE)
Công thức này trả về lỗi #N/A vì VLOOKUP không thể tra trái (Product ID xuất hiện trước Product Name trong sheet). Để dùng VLOOKUP ở đây, bạn cần sắp xếp lại cột—thường không khả thi vì có thể phá vỡ cấu trúc dữ liệu và công thức downstream.
INDEX/MATCH:
- Chọn một ô và dùng combo INDEX/MATCH.
=INDEX(A2:A23, MATCH(H8, B2:B23, 0))
Công thức này trả về Product ID (P006). Nó hoạt động, nhưng bạn phải chỉ định cả hai phạm vi và đảm bảo MATCH là chính xác (0), làm tăng độ dài.
XLOOKUP:
XLOOKUP không quan tâm thứ tự cột. Bạn chỉ nói nó tìm gì và lấy giá trị từ đâu.
- Chọn một ô và chèn công thức XLOOKUP động.
=XLOOKUP(H12, B2:B23, A2:A23)
Công thức này tìm Product Name và trả về Product ID tương ứng (P006)—dù ID ở bên trái. Cú pháp gọn gàng, không cần lồng nhau.
Ứng Dụng Thực Tế:
Giả sử dashboard bán hàng cho người dùng gõ tên sản phẩm trong ô tìm kiếm. Bạn có thể tức thì truy xuất Product ID—hoặc thậm chí nhiều chi tiết (giá, tồn kho, nhà cung cấp)—mà không cần sắp xếp lại cột:
=XLOOKUP(H12, B2:B23, A2:E23)
Điều này trả về nhiều cột bằng cách "tràn" qua các ô liền kề, nên bạn nhận được tất cả thông tin sản phẩm trong một bước.
Lý Do 2: Xử Lý Lỗi Tích Hợp Sẵn (Không Còn #N/A)
Khi giá trị tra cứu không tìm thấy, Excel trả về #N/A. Với VLOOKUP hoặc INDEX/MATCH, bạn thường bọc công thức với IFERROR để xử lý nhẹ nhàng—thêm lớp lồng nhau.
XLOOKUP có đối số if_not_found tích hợp sẵn, nên bạn có thể chỉ định thông điệp thân thiện hoặc giá trị dự phòng ngay trong công thức.
Hãy tìm giá đơn vị sản phẩm có thể không tồn tại trong cơ sở dữ liệu (ví dụ: "Wireless Charger").
VLOOKUP + IFERROR:
- Chọn một ô và chèn công thức sau.
=IFERROR(VLOOKUP(H4,D2:F23,1,FALSE), "Not Found")
Điều này hoạt động, nhưng nó rườm rà và thêm lớp lồng nhau khác.
INDEX/MATCH + IFERROR:
- Chọn một ô và chèn công thức sau.
=IFERROR(INDEX(D2:D23, MATCH(H8, B2:B23, 0)), "Not Found")
Lại hoạt động, nhưng wrapper thêm làm công thức dài hơn.
XLOOKUP:
Đối số if_not_found của XLOOKUP xử lý giá trị thiếu thanh lịch.
- Chọn một ô và chèn công thức sau.
=XLOOKUP(H12, B2:B23, D2:D23, "Not Found")
Công thức này tìm Product Name; nếu không tìm thấy, trả về "Not Found." Nó súc tích và tránh hàm thêm.
Lý Do 3: Tìm Kiếm Ký Tự Hoang Dã (Wildcard) Đơn Giản
VLOOKUP hỗ trợ wildcard (* và ?) ở chế độ khớp chính xác (FALSE) cho tra cứu văn bản, nhưng cú pháp không phải lúc nào cũng rõ ràng. INDEX/MATCH cũng hỗ trợ wildcard vì MATCH nhận diện chúng khi match_type là 0. XLOOKUP làm rõ ý định với match_mode là 2 cho khớp wildcard, cải thiện sự rõ ràng và giảm bất ngờ.
Hãy tưởng tượng bạn chỉ nhớ một phần tên sản phẩm—có thể nó bắt đầu bằng "Lap"—và bạn muốn tìm giá của nó.
VLOOKUP:
- Chọn một ô và chèn công thức sau.
=VLOOKUP("Lap*", B2:D23, 3, FALSE)
Điều này có thể hoạt động với khớp chính xác dùng wildcard và không yêu cầu dữ liệu đã sắp xếp. Tuy nhiên, đối số "column index" có thể mong manh nếu cột di chuyển.
INDEX/MATCH:
- Chọn một ô và chèn công thức sau.
=INDEX(D2:D23, MATCH("Lap*", B2:B23, 0))
Điều này cũng hỗ trợ wildcard qua MATCH. Nó tin cậy, nhưng thiết lập hai phạm vi rườm rà hơn.
XLOOKUP:
match_mode của XLOOKUP làm cho tìm kiếm wildcard đơn giản.
- Chọn một ô và chèn công thức sau.
=XLOOKUP("Lap*", B2:B23, D2:D23, "No match", 2)
- "Lap*": khớp một phần (khớp bất kỳ văn bản nào bắt đầu bằng "Lap")
- Lookup array:
B2:B23 - Return array:
D2:D23 - if_not_found: "No match"
- 2: bật tìm kiếm wildcard
Công thức này trả về giá sản phẩm (ví dụ: $30) dùng khớp wildcard—hoàn hảo cho tìm kiếm tên một phần, tra cứu SKU, hoặc bất cứ khi nào bạn không có giá trị chính xác.
Lưu Ý Quan Trọng: XLOOKUP có sẵn trong Microsoft 365, Excel 2021 và phiên bản sau. Nếu bạn dùng phiên bản cũ, bạn cần dùng VLOOKUP hoặc INDEX/MATCH cho đến nay.
Mẹo Wildcard:
| Ký Hiệu | Ý Nghĩa | Ví Dụ | Khớp |
|---|---|---|---|
| * | Bất kỳ số lượng ký tự | "Lap*" | Laptop Stand |
| ? | Một ký tự đơn | "P00?" | P001, P002, v.v. |
| ~ | Thoát wildcard | "Power~*" | Tìm "Power*" đúng đen |
Tải Workbook Thực Hành
Suýt Quên
XLOOKUP không chỉ là một hàm tra cứu khác—nó là sự suy nghĩ lại về cách tra cứu nên hoạt động trong Excel. Trong hướng dẫn này, chúng tôi chỉ ba lý do tại sao XLOOKUP là người bạn mới tốt nhất của bạn. Bằng cách kết hợp linh hoạt hướng, xử lý lỗi tích hợp, và khớp wildcard rõ ràng, nó tiết kiệm thời gian và ngăn chặn lỗi công thức. Một khi bạn thử, bạn sẽ không bao giờ quay lại.
Nhận MIỄN PHÍ Bài Tập Excel Nâng Cao Có Giải Pháp!