Excel cung cấp các kỹ thuật tra cứu rộng rãi cho quản lý dữ liệu và phân tích dữ liệu. VLOOKUP thường được dùng để truy xuất dữ liệu, nhưng nó có giới hạn như cần cột tra cứu ở bên trái và xử lý lỗi không linh hoạt. Để vượt qua những hạn chế này, các hàm nâng cao như XLOOKUP và INDEX-MATCH-MATCH cung cấp sự linh hoạt, kiểm soát và hiệu quả hơn. Hãy cùng xem các kỹ thuật tra cứu nâng cao vượt qua VLOOKUP bằng cách sử dụng XLOOKUP và INDEX-MATCH-MATCH để tận dụng chúng hiệu quả.
Hãy xem xét một tập dữ liệu bán hàng để áp dụng các kỹ thuật tra cứu nâng cao bằng các ví dụ thực tế.
Kỹ thuật Tra cứu Nâng cao với XLOOKUP
XLOOKUP là một hàm đa năng trong Excel để tra cứu đơn cột hoặc đa cột. Nó có thể tìm kiếm theo bất kỳ hướng nào (trái-sang-phải, phải-sang-trái, dọc, ngang), cung cấp thông báo lỗi tùy chỉnh và không yêu cầu dữ liệu được sắp xếp để tra cứu. Hàm XLOOKUP chỉ khả dụng cho người dùng Excel 2021 và MS Office 365. Hàm XLOOKUP tự động cập nhật thay đổi dữ liệu của giá trị tra cứu.
Cú pháp:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value: Giá trị cần tìm kiếm.
- lookup_array: Phạm vi hoặc mảng để tìm kiếm bên trong.
- return_array: Phạm vi hoặc mảng từ đó nó sẽ trả về kết quả.
- [if_not_found]; [Tùy chọn]: Giá trị trả về nếu không tìm thấy khớp.
- [match_mode]; [Tùy chọn]: Chỉ định loại khớp. Bạn có thể chọn khớp chính xác, ký tự đại diện hoặc khớp gần đúng.
- 0 – Khớp chính xác (mặc định).
- 1 – Khớp chính xác hoặc lớn hơn tiếp theo.
- -1 – Khớp chính xác hoặc nhỏ hơn tiếp theo.
- 2 – Khớp ký tự đại diện.
- [search_mode]; [Tùy chọn]: Quyết định hướng tìm kiếm, như đầu-tới-cuối hoặc cuối-tới-đầu
- 1 – Tìm kiếm từ đầu đến cuối.
- -1 – Tìm kiếm từ cuối đến đầu.
- 2 – Tìm kiếm nhị phân (thứ tự tăng dần).
- -2 – Tìm kiếm nhị phân (thứ tự giảm dần).
1. Sử dụng XLOOKUP cho Tiêu chí Đơn
Hãy tìm số tiền bán hàng gần nhất với $100 từ tập dữ liệu bán hàng của chúng ta để xem khách hàng chi gần như $100 cho bất kỳ đơn hàng nào. Chèn công thức sau.
Công thức:
=XLOOKUP(100, G2:G71, A2:G71, "Không tìm thấy", 1)
Công thức này tìm kiếm 100 trong phạm vi G2:G71 và trả về hàng tương ứng từ A2:G71. Nếu giá trị gần nhất của 100 không được tìm thấy, nó sẽ hiển thị "Không tìm thấy".
Đầu ra:
1007 1/4/2024 Daniel Martinez East 39.99 3 119.97
2. Sử dụng XLOOKUP với Nhiều Tiêu chí
Bạn có thể thực hiện các tra cứu phức tạp hơn bằng cách nối nhiều tiêu chí sử dụng XLOOKUP. Hãy khám phá công thức.
Công thức:
=XLOOKUP("Melissa Lopez" & "West", C2:C71 & D2:D71, A2:G71)
Công thức này nối tên khách hàng và khu vực và tìm kiếm giá trị kết hợp trong các mảng tra cứu được nối. Nó truy xuất hàng tương ứng từ phạm vi được chọn.
Đầu ra:
1012 1/6/2024 Melissa Lopez West 79.99 2 159.98
INDEX-MATCH-MATCH cho Kỹ thuật Tra cứu Nâng cao
INDEX-MATCH-MATCH được sử dụng khi bạn cần tra cứu giá trị dựa trên cả tiêu chí hàng và cột, lý tưởng cho bảng dữ liệu 2D.
Cú pháp:
=INDEX(array, MATCH(row_lookup_value, row_lookup_array, 0), =MATCH(column_lookup_value, column_lookup_array, 0))
- array: Phạm vi ô chứa các giá trị bạn muốn truy xuất.
- MATCH(row_lookup_value, row_lookup_array, 0): Trả về số hàng.
- row_lookup_value: Giá trị cần tìm kiếm trong hàng.
- row_lookup_array: Phạm vi hàng để tìm kiếm bên trong.
- 0 – Khớp chính xác
- MATCH(column_lookup_value, column_lookup_array, 0): Trả về số cột.
- column_lookup_value: Giá trị cần tìm kiếm trong cột.
- column_lookup_array: Phạm vi cột để tìm kiếm bên trong.
- 0 – Khớp chính xác
1. Tra cứu 2D cho Hàng và Cột
Hãy xem số tiền bán hàng cho một khách hàng của một số tiền bán hàng cụ thể bằng công thức INDEX-MATCH-MATCH. Chèn công thức sau.
=INDEX(A2:G71, MATCH("John Smith", C2:C71, 0), MATCH("Số tiền bán", A1:G1, 0))
Công thức này tìm kiếm John Smith trong phạm vi C2:C71 và "Số tiền bán" trong A1:G1. Sau đó nó sẽ trả về giá trị giao nhau trong A2:G71.
Đầu ra:
99.98
2. Công thức INDEX-MATCH-MATCH Nâng cao cho Tra cứu 3D
Vì INDEX-MATCH-MATCH thường dùng cho tra cứu hai chiều, bạn có thể mở rộng nó thành tra cứu ba chiều bằng cách sử dụng công thức mảng với nhiều hàm MATCH. Nó lý tưởng cho tập dữ liệu lớn và khớp tiêu chí phức tạp, vượt trội hơn VLOOKUP về linh hoạt và khả năng.
Công thức:
=INDEX(G2:G71, MATCH(1, (C2:C71="John Smith") * (D2:D71="South") * (F2:F71=4), 0))
Công thức này tìm kiếm Số tiền bán bằng cách khớp ba tiêu chí: Tên khách hàng trong cột C, Khu vực trong cột D và Số lượng trong cột F. Các điều kiện được kết hợp bằng phép nhân như hàm AND, cho phép MATCH tìm hàng nơi tất cả tiêu chí được đáp ứng, và INDEX trả về giá trị tương ứng từ G2:G71.
Đầu ra:
129.99
Lợi ích của Việc Sử Dụng XLOOKUP và INDEX-MATCH
Tại sao Chọn XLOOKUP thay vì VLOOKUP?
- Tìm kiếm Hai chiều: XLOOKUP có thể tra cứu giá trị theo cả hai hướng, trong khi VLOOKUP chỉ có thể tìm kiếm từ trái sang phải.
- Không cần Số chỉ mục Cột: Không cần chỉ định số chỉ mục cột nên thay đổi cột sẽ không ảnh hưởng đến nó.
- Khớp Chính xác Mặc định: Mặc định XLOOKUP tìm kiếm khớp chính xác, giảm thiểu lỗi.
- Tìm kiếm Ký tự Đại diện trong XLOOKUP: Bạn có thể sử dụng ký tự đại diện như * và ? với XLOOKUP khi thực hiện tìm kiếm văn bản.
- Xử lý Lỗi: Bạn có thể đề cập đến việc trả về gì nếu không tìm thấy khớp.
Lợi ích của INDEX-MATCH so với VLOOKUP
- Cấu trúc Bảng Linh hoạt: INDEX-MATCH không bị ảnh hưởng bởi việc chèn hoặc xóa cột.
- Tra cứu Bên Trái: Khác với VLOOKUP, INDEX-MATCH có thể tra cứu bên trái.
- Hiệu suất: Hiệu quả hơn trong tập dữ liệu lớn vì MATCH nhanh hơn trong một số trường hợp so với việc tính toán lại VLOOKUP.
Kết luận
Các kỹ thuật tra cứu nâng cao như XLOOKUP và INDEX-MATCH-MATCH là thiết yếu cho xử lý dữ liệu nâng cao trong Excel. Các hàm này tự động cập nhật thay đổi dữ liệu. Các hàm này cung cấp sự linh hoạt, độ chính xác và hiệu suất vượt trội so với VLOOKUP truyền thống, khiến chúng trở nên quan trọng cho bất kỳ ai muốn nâng cao kỹ năng Excel của mình.
Nhận bài tập Excel nâng cao miễn phí kèm lời giải!