Buzz
Hai cách dễ dàng để tìm kiếm dữ liệu trong Excel với nhiều điều kiện hơn
Những Cơ Bản về Việc Sử Dụng XLOOKUP với Nhiều Tiêu Chí
Công thức XLOOKUP cơ bản là =XLOOKUP(lookup_value, lookup_array, result_array). Nếu bạn muốn sử dụng XLOOKUP để tìm kiếm nhiều tiêu chí, bạn có thể vượt qua việc công thức XLOOKUP chỉ có một 'lookup_value' bằng cách sử dụng logic Boolean (được khuyến khích) hoặc nối chuỗi (phù hợp với các công thức đơn giản nhất).
Các Bước
Nhiều Tiêu Chí với Logic Boolean
Hiểu cách Logic Boolean hoạt động với công thức. Thông thường, XLOOKUP chỉ có thể tìm kiếm một giá trị. Tuy nhiên, bạn có thể sử dụng Logic Boolean trong công thức XLOOKUP, tạo ra một mảng tìm kiếm tạm thời với các giá trị 1 (TRUE) và 0 (FALSE) để trả về kết quả khớp với tất cả các tiêu chí tìm kiếm.
- Ví dụ: Bạn có một bảng tính với các cột cho các loại bánh, kích thước bánh, và giá cả. Bạn muốn tạo ra một công thức XLOOKUP với logic Boolean để tìm giá của một chiếc bánh dựa trên loại và kích thước bạn nhập vào.
- Công thức XLOOKUP cơ bản sử dụng Logic Boolean để tìm kiếm nhiều tiêu chí nhìn như sau:
=XLOOKUP(1, (lookup_array1=lookup_value1) * (lookup_array2=lookup_value2) * (insert one array for each lookup_value), return_array)
Create "Criteria" and "Result" sections in your spreadsheet. The "Criteria" section will be used to input lookup values into your formula, and the "Result" section is where you will actually put your XLOOKUP formula to get the intended result.
- Example: Your spreadsheet has 3 columns (cake flavors, cake sizes, and prices) that are in columns A, B, and C. Skip column D, then put the "Criteria" and "Result" sections in columns E and F as follows:
E F 1 Criteria 2 Flavor (you will enter a flavor here) 3 Size (you will enter a size here) 4 5 Result 6 Price (the formula will return a price here)
Chọn ô bên phải của ô được đánh dấu trong phần 'Kết quả'. Ô này là nơi bạn sẽ nhập công thức XLOOKUP.
- Ví dụ: Trong ví dụ, ô được đánh dấu Giá trong phần 'Kết quả' là E6, vì vậy bạn nên chọn F6 (một ô bên phải).
Nhập =XLOOKUP(1, vào ô. So sánh điều này với công thức XLOOKUP cơ bản (=XLOOKUP(lookup_value, lookup_array, result_array)), số 1 trong công thức Logic Boolean của XLOOKUP được coi là lookup_value.
- Lý do bạn đặt 1 làm giá trị tìm kiếm là vì '1' tương ứng với 'TRUE.' Khi bạn nhập tiêu chí tìm kiếm vào công thức của mình, XLOOKUP sẽ kiểm tra tiêu chí đó so với các mảng trong công thức và tạo ra một mảng các giá trị TRUE và FALSE.
- Các giá trị này sau đó được chuyển đổi thành số, và XLOOKUP sẽ tìm kiếm số 1 đầu tiên trong mảng đó (nói cách khác, kết quả đầu tiên khớp với tất cả các tiêu chí tìm kiếm).
Thêm tiêu chí tìm kiếm của bạn với Logic Boolean. Đối với mỗi tiêu chí tìm kiếm, bạn nên có một mảng tìm kiếm tương ứng. Các cặp này nên được viết dưới dạng (mảng_tìm_kiếm)=(giá_trị_tìm_kiếm). Phân tách mỗi cặp bằng dấu sao (*), và thêm một dấu phẩy sau cùng của tập hợp mảng/giá trị.
- Mảng tìm kiếm của bạn sẽ là phạm vi ô chứa thông tin bạn muốn tìm kiếm cho giá trị tìm kiếm cụ thể đó.
- Giá trị tìm kiếm của bạn sẽ tham chiếu đến một trong các ô dưới phần 'Tiêu Chí' bạn tạo ra ở Bước 2.
- Ví dụ: Các mảng tìm kiếm của bạn sẽ là nội dung của các cột 'loại' và 'kích thước'. Mỗi cột có 9 dòng dữ liệu, vì vậy mảng loại là A2:A10 và mảng kích thước là B2:B10. Các giá trị tìm kiếm của bạn sẽ là F2 (cho loại) và F3 (cho kích thước) vì chúng là các ô ngay bên phải của nhãn Loại và Kích Thước trong phần 'Tiêu Chí'.
- Với thông tin này, bạn có thể thêm các tập hợp mảng/giá trị vào công thức của mình, nó trông như sau cho đến nay:
=XLOOKUP(1, (A2:A10=F2) * (B2:B10=F3),
- Với thông tin này, bạn có thể thêm các tập hợp mảng/giá trị vào công thức của mình, nó trông như sau cho đến nay:
Thêm mảng kết quả của bạn. Đây là phạm vi các ô mà bạn muốn lấy kết quả của mình từ. Sau khi thêm mảng kết quả của bạn vào công thức, thêm một ) để hoàn thành công thức.
- Ví dụ: Bạn muốn lấy kết quả từ cột 'giá,' vì vậy mảng kết quả của bạn là C2:C10. Công thức hoàn chỉnh của bạn với mảng kết quả trông như sau:
=XLOOKUP(1, (A2:A10=F2) * (B2:B10=F3), C2:C10)
Kiểm tra công thức của bạn bằng cách nhập một số giá trị tìm kiếm trong phần 'Tiêu Chí.' Trong ví dụ, bạn sẽ nhập một loại bánh vào ô F2 và một kích thước bánh vào ô F3, và công thức sẽ trả về giá tương ứng cho sự kết hợp đó trong ô F6.
- Nếu công thức của bạn không trả về kết quả đúng hoặc nó hiển thị một lỗi, hãy kiểm tra kỹ xem tất cả các tham chiếu ô trong công thức của bạn có chính xác không. Excel sẽ màu các tham chiếu ô trong công thức khi bạn đang chỉnh sửa chúng, vì vậy bạn có thể dễ dàng nhận thấy nếu một trong các tham chiếu của bạn là không chính xác.
Nhiều Tiêu Chí với Ghép Nối
Hiểu công dụng của ghép nối trong công thức. Ghép nối sử dụng toán tử & để kết hợp các giá trị. Sử dụng ghép nối, bạn có thể vượt qua việc công thức XLOOKUP chỉ chấp nhận một giá trị tìm kiếm bằng cách kết hợp hai giá trị lại với nhau.
- Ví dụ: Bạn có một bảng tính với các cột cho tên thú cưng, chủ nhân thú cưng, và thông tin liên lạc. Bạn muốn tạo ra một công thức XLOOKUP với ghép nối để tìm thông tin liên lạc của một chủ nhân thú cưng nếu bạn nhập tên của họ và thú cưng của họ.
- Công thức XLOOKUP cơ bản sử dụng ghép nối để tìm kiếm nhiều tiêu chí nhìn như sau:
=XLOOKUP(lookup_value1 & lookup_value2 & [insert each lookup value], lookup_array1 & lookup_array2 & [insert each lookup array], return_array)
Create "Criteria" and "Result" sections in your spreadsheet. The "Criteria" section will be used to input lookup values into your formula, and the "Result" section is where you will actually put your XLOOKUP formula to get the intended result.
- Example: Your spreadsheet has 3 columns (pet names, owner's names, and contact information) that are in columns A, B, and C. Skip column D, then put the "Criteria" and "Result" sections in columns E and F as follows:
E F 1 Criteria 2 Pet Name (you will enter a pet's name here) 3 Owner Name (you will enter an owner's name here) 4 5 Result 6 Contact (the formula will return the contact information here)
Xác định thông tin bạn cần cho mỗi đối số. Trong một công thức XLOOKUP, bạn có ba đối số: một giá trị tìm kiếm, một mảng tìm kiếm, và một mảng kết quả. Khi bạn sử dụng ghép nối, giá trị tìm kiếm và mảng tìm kiếm sẽ được ghép nối bằng cách đặt & giữa mỗi giá trị hoặc mảng.
- Giá trị tìm kiếm sẽ tham chiếu đến các ô bên cạnh nhãn tiêu chí trong phần 'Tiêu Chí' bạn tạo ở bước 2.
- Mảng tìm kiếm sẽ tham chiếu đến các phạm vi ô chứa các tiêu chí tìm kiếm.
- Mảng kết quả sẽ là một phạm vi ô mà bạn lấy kết quả từ.
- Ví dụ: Trong bảng tính của bạn, các giá trị tìm kiếm của bạn là ô F2 (cho tên thú cưng) và ô F3 (cho tên chủ nhân). Bạn có 9 dòng dữ liệu trong bảng tính của mình (từ dòng 2 đến dòng 10) và tiêu chí tìm kiếm của bạn tham chiếu thông tin từ cột A và cột B. Cột C là nơi bạn lấy kết quả từ.
- Bạn xác định rằng giá trị tìm kiếm của bạn là F2 & F3, mảng tìm kiếm của bạn là A2:A10 & B2:B10, và mảng kết quả của bạn là C2:C10.
Chọn ô bên phải của ô được ghi nhãn trong phần 'Kết Quả.' Đây là nơi bạn sẽ nhập công thức XLOOKUP.
- Ví dụ: Trong ví dụ, ô được ghi nhãn Liên Hệ trong phần 'Kết Quả' là ô E6, vì vậy bạn nên chọn F6 (một ô bên phải).
Viết một công thức XLOOKUP bằng các chuỗi đã được ghép nối. Lấy thông tin bạn đã xác định ở Bước 3 và kết hợp chúng vào một công thức XLOOKUP.
- Trong ví dụ, công thức XLOOKUP của bạn sẽ trông như sau:
=XLOOKUP(F2 & F3, A2:A10 & B2:B10, C2:C10)
Thử nghiệm công thức của bạn bằng cách nhập một số giá trị tìm kiếm trong phần 'Tiêu Chí.' Trong ví dụ, bạn sẽ nhập tên thú cưng vào ô F2 và tên chủ nhân vào ô F3, và công thức sẽ trả về thông tin liên hệ tương ứng cho sự kết hợp đó trong ô F6.
- Nếu công thức của bạn không trả về kết quả đúng hoặc nó hiển thị một lỗi, hãy kiểm tra kỹ xem tất cả các tham chiếu ô trong công thức của bạn có chính xác không. Excel sẽ màu các tham chiếu ô trong các công thức khi bạn đang chỉnh sửa chúng, vì vậy bạn có thể dễ dàng nhận thấy nếu một trong các tham chiếu của bạn là không chính xác.
Mẹo
-
XLOOKUP chỉ hoạt động trong các phiên bản Excel mới. Tuy nhiên, INDEX MATCH hoạt động theo cách tương tự, và có thể được sử dụng trong bất kỳ phiên bản Excel nào. Tìm hiểu cách sử dụng INDEX MATCH trong bài viết này của Mytour.
-
VLOOKUP tương tự như XLOOKUP, nhưng chúng không thể thay thế cho nhau. Điều này là vì VLOOKUP chỉ có thể tìm kiếm sang phải của mảng tìm kiếm, trong khi XLOOKUP có thể tìm kiếm sang trái hoặc sang phải.
