Excel cung cấp các kỹ thuật tra cứu mở rộng để quản lý dữ liệu và phân tích dữ liệu. VLOOKUP thường được sử dụng để truy xuất dữ liệu nhưng nó có những hạn chế như cần cột tra cứu ở bên trái và xử lý lỗi thiếu linh hoạt. Để khắc phục những điều này, các hàm nâng cao như XLOOKUP và INDEX-MATCH-MATCH mang đến sự linh hoạt, khả năng kiểm soát và hiệu quả cao hơn. Hãy chỉ cho bạn các kỹ thuật tra cứu nâng cao ngoài VLOOKUP bằng cách sử dụng XLOOKUP và INDEX-MATCH-MATCH để tận dụng chúng một cách 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ách sử dụng các ví dụ thực tế.
Kỹ thuật tra cứu nâng cao XLOOKUP
XLOOKUP là một hàm linh hoạt trong Excel để tra cứu một hoặc nhiều cột. Nó có thể tìm kiếm theo bất kỳ hướng nào (từ trái sang phải, từ 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 sắp xếp dữ liệu để tra cứu. Chức năng 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 các 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: Dải ô hoặc mảng mà 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 kết quả khớp.
- [match_mode]; [Tùy chọn]: Chỉ định loại trận đấu. Bạn có thể chọn kết hợp chính xác, ký tự đại diện hoặc kết hợp gần đúng.
- 0 – Khớp chính xác (mặc định).
- 1 – Chính xác hoặc lớn hơn tiếp theo.
- -1 – Chính xác hoặc nhỏ hơn tiếp theo.
- 2 – Trận đấu ký tự đại diện.
- [chế độ tìm kiếm]; [Tùy chọn]: Quyết định hướng tìm kiếm, chẳng hạn như từ đầu đến cuối hoặc từ cuối đến đầ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 là 100 USD từ tập dữ liệu bán hàng của chúng tôi để xem khách hàng chi gần 100 USD 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 không tìm thấy số gần nhất trong số 100, nó sẽ hiển thị “Không tìm thấy”.
Đầu ra:
1007 4/1/2024 Daniel Martinez Đông 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 tra cứu phức tạp hơn bằng cách nối nhiều tiêu chí bằng XLOOKUP. Hãy cùng khám phá công thức.
Công thức:
=XLOOKUP(“Melissa Lopez” &“Tây”, C2:C71 &D2:D71, A2:G71)
Công thức này nối tên và khu vực của khách hàng rồi tìm kiếm giá trị kết hợp trong các mảng tra cứu được nối. Nó lấy giá trị tương ứng từ phạm vi đã chọn.
Đầu ra:
1012 6/1/2024 Melissa Lopez Tây 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 các giá trị dựa trên tiêu chí cả hàng và cột, lý tưởng cho các bảng dữ liệu 2D.
Cú pháp:
=INDEX(mảng, MATCH(row_lookup_value, row_lookup_array, 0), =MATCH(column_lookup_value, cột_lookup_array, 0))
- mảng: 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 các hàng.
- row_lookup_array: Phạm vi hàng để tìm kiếm bên trong.
- 0 – Kết hợp chính xác
- MATCH(cột_lookup_value, cột_lookup_array, 0): Trả về số cột.
- cột_lookup_value: Giá trị cần tìm trong các cột.
- cột_lookup_mảng: Phạm vi cột để tìm kiếm bên trong.
- 0 – Kết hợ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 của một khách hàng với số tiền bán hàng cụ thể bằng cách sử dụ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 hàng”, 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 hàng” 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 để tra cứu 3D
Vì INDEX-MATCH-MATCH thường dành cho tra cứu hai chiều nên bạn có thể mở rộng nó sang tra cứu ba chiều bằng cách sử dụng công thức mảng có nhiều hàm MATCH. Nó lý tưởng cho các tập dữ liệu lớn và đối sánh tiêu chí phức tạp, vượt trội hơn VLOOKUP về tính 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 hàng bằng cách khớp ba tiêu chí:Tên khách hàng ở cột C, Khu vực ở cột D và Số lượng ở cột F. Các điều kiện được kết hợp bằng phép nhân dưới dạng hàm AND, cho phép MATCH tìm hàng đáp ứng tất cả tiêu chí và INDEX trả về giá trị tương ứng từ G2:G71.
Đầu ra:
129,99
Ưu điểm 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ó số chỉ mục cột :Không cần chỉ định số chỉ mục cột nên việc thay đổi cột sẽ không ảnh hưởng.
- Kết hợp chính xác mặc định :Theo mặc định, XLOOKUP tìm kiếm kết quả 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 các 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 những gì cần trả lại nếu không tìm thấy kết quả phù hợ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ông giống như VLOOKUP, INDEX-MATCH có thể nhìn sang bên trái.
- Hiệu suất :Hiệu quả hơn trong các 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 rất cần thiết để xử lý dữ liệu nâng cao trong Excel. Các chức năng này tự động cập nhật những thay đổi về dữ liệu. Các hàm này mang lại 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 đối với bất kỳ ai muốn nâng cao kỹ năng Excel của mình.
Nhận MIỄN PHÍ Bài tập Excel nâng cao có Giải pháp!