Nếu bạn đã sử dụng Excel được một thời gian, có thể bạn đã dựa vào hàm VLOOKUP hoặc INDEX/MATCH vô số lần. Những ngày đấu tranh với những hạn chế của VLOOKUP và sự phức tạp của INDEX/MATCH đã qua. Làm quen với XLOOKUP—Chức năng 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 thân mới của bạn. Bạn sẽ thấy cách nó khiến các tác vụ tra cứu hàng ngày nhanh hơn, gọn gàng 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 có thể nhìn theo 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 của bạn—nếu cột tra cứu không phải là cột đầu tiên trong mảng bảng của bạn thì công thức sẽ không thành công. INDEX/MATCH giải quyết được vấn đề này nhưng khó đọc hơn vì nó yêu cầu lồng hai hàm, điều này có thể gây lộn xộn với các tập dữ liệu lớn.
XLOOKUP lật ngược điều này:nó tách phạm vi tra cứu khỏi phạm vi trả về, do đó bạn có thể nhìn sang trái, phải, lên hoặc xuống mà không gặp rắc rối.
Giả sử bạn có tập dữ liệu bán hàng và muốn tìm ID sản phẩm bằng cách tìm kiếm Tên sản phẩm.
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ề #N/A lỗi do VLOOKUP không thể nhìn sang bên trái (ID sản phẩm xuất hiện trước Tên sản phẩm trong trang tính). Để sử dụng VLOOKUP ở đây, bạn cần sắp xếp lại các cột—thường không khả thi vì nó có thể phá vỡ cấu trúc dữ liệu và các công thức tiếp theo của bạn.
CHỈ SỐ/MATCH:
- Chọn một ô và sử dụng tổ hợp INDEX/MATCH.
=INDEX(A2:A23, MATCH(H8, B2:B23, 0))
Công thức này trả về ID sản phẩm (P006). Nó hoạt động, nhưng bạn phải chỉ định cả hai phạm vi và đảm bảo MATCH chính xác (0 ), điều này làm tăng thêm tính chi tiết.
XLOOKUP:
XLOOKUP không quan tâm đến thứ tự cột. Bạn chỉ cần cho nó biết cần tìm gì và trả về 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 kiếm Tên sản phẩm và trả về ID sản phẩm tương ứng (P006)—mặc dù ID nằm ở bên trái. Cú pháp rõ ràng hơn, không cần lồng nhau.
Sử dụng thực tế:
Giả sử bảng điều khiển bán hàng của bạn cho phép người dùng nhập tên sản phẩm vào hộp tìm kiếm. Bạn có thể truy xuất ngay ID sản phẩm—hoặc thậm chí nhiều chi tiết (giá, kho, nhà cung cấp)—mà không cần sắp xếp lại các cột:
=XLOOKUP(H12, B2:B23, A2:E23)
Thao tác này sẽ trả về nhiều cột bằng cách "tràn" qua các ô liền kề, nhờ đó bạn có được tất cả thông tin sản phẩm chỉ trong một bước.
Lý do 2:Xử lý lỗi tích hợp (Không còn #N/A)
Khi không tìm thấy giá trị tra cứu, Excel sẽ trả về #N/A . Với VLOOKUP hoặc INDEX/MATCH, bạn thường gói công thức bằng IFERROR để xử lý vấn đề này một cách khéo léo—thêm các phần lồng nhau bổ sung.
XLOOKUP có if_not_found tích hợp sẵn đối số, do đó bạn có thể chỉ định thông báo thân thiện hoặc giá trị dự phòng ngay trong công thức.
Hãy tìm kiếm đơn giá sản phẩm có thể không tồn tại trong cơ sở dữ liệu (ví dụ:“Bộ sạc không dây”).
VLOOKUP + IFERROR:
- Chọn một ô và chèn công thức sau.
=IFERROR(VLOOKUP(H4,D2:F23,1,FALSE), "Not Found")
Cách này hiệu quả nhưng dài dòng và thêm một lớp lồng khác.
CHỈ SỐ/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")
Một lần nữa, nó hoạt động, nhưng trình bao bọc bổ sung làm cho công thức dài hơn.
XLOOKUP:
if_not_found của XLOOKUP đối số xử lý các giá trị bị thiếu một cách tinh tế.
- 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 kiếm Tên sản phẩm; nếu không tìm thấy, nó sẽ trả về “Không tìm thấy”. Nó ngắn gọn và tránh các chức năng bổ sung.
Lý do 3:Việc tìm kiếm bằng ký tự đại diện trở nên đơn giản
VLOOKUP hỗ trợ ký tự đại diện (* và ? ) ở chế độ khớp chính xác (FALSE ) để 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ợ các ký tự đại diện vì MATCH nhận ra chúng khi match_type là 0 . XLOOKUP làm cho ý định rõ ràng bằng match_mode của 2 để khớp ký tự đại diện, giúp cải thiện độ rõ ràng và giảm bớt sự 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 kết quả khớp chính xác bằng cách sử dụng ký tự đại diện và không yêu cầu dữ liệu được sắp xếp. Tuy nhiên, đối số “chỉ mục cột” có thể dễ vỡ nếu các cột di chuyển.
CHỈ SỐ/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ợ các ký tự đại diện thông qua MATCH . Nó đáng tin cậy, nhưng thiết lập hai phạm vi dài dòng hơn.
XLOOKUP:
match_mode của XLOOKUP làm cho việc tìm kiếm ký tự đại diện trở nên đơn giản.
- Chọn một ô và chèn công thức sau.
=XLOOKUP("Lap*", B2:B23, D2:D23, "No match", 2)
- “Vòng*”: khớp một phần (khớp với bất kỳ văn bản nào bắt đầu bằng “Vòng”)
- Mảng tra cứu:
B2:B23 - Mảng trả về:
D2:D23 - if_not_found: “Không khớp”
- 2: cho phép tìm kiếm ký tự đại diện
Công thức này trả về giá sản phẩm (ví dụ:$30 ) bằng cách sử dụng kết hợp ký tự đại diện—hoàn hảo cho tìm kiếm tên một phần, tra cứu SKU hoặc bất cứ lúc 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à các phiên bản mới hơn. Nếu bạn đang sử dụng phiên bản cũ hơn thì hiện tại bạn cần phải sử dụng VLOOKUP hoặc INDEX/MATCH.
Mẹo về ký tự đại diện:
Tải xuống sổ tay thực hành
Suy nghĩ cuối cùng
XLOOKUP không chỉ là một hàm tra cứu khác—nó là sự suy nghĩ lại về cách hoạt động của tra cứu trong Excel. Trong hướng dẫn này, chúng tôi đã chỉ ra ba lý do tại sao XLOOKUP là người bạn thân mới của bạn. Bằng cách kết hợp tính linh hoạt theo hướng, xử lý lỗi tích hợp và khớp ký tự đại diện rõ ràng, nó giúp tiết kiệm thời gian và ngăn ngừa 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!