XLOOKUP với nhiều tiêu chí không phải là một chức năng được thiết kế riêng để làm như vậy trong Google Sheets. Tuy nhiên, chúng ta có thể thích ứng nó để xử lý nhiều tiêu chí bằng cách sử dụng IF và logic Boolean.
Trong khi XLOOKUP thông thường có thể lấy một hoặc nhiều khóa tìm kiếm, tìm chúng trong một cột của một phạm vi và trả về giá trị (các) tương ứng từ một cột khác, nó có thể không hỗ trợ trực tiếp việc tìm kiếm một khóa trong một cột và một khóa khác trong một cột khác, và cả hai cần phải khớp trong cùng một dòng.
Tình huống này, được biết đến là XLOOKUP với nhiều tiêu chí, có thể được giải quyết bằng cách thực thi IF và logic Boolean trong Google Sheets. Hãy khám phá một số ví dụ.
XLOOKUP với Nhiều Tiêu Chí: Khớp Chính Xác
Trong các cột A đến D, trong đó A1:D1 chứa nhãn trường, chúng ta có tên mặt hàng, ngày mua, tháng mua và giá. Tham khảo dữ liệu mẫu trong ảnh chụp màn hình dưới đây hoặc nhấp vào liên kết dưới hình ảnh để sao chép bảng mẫu.
Bảng Mẫu
Các mặt hàng, chẳng hạn như “Apple,” “Orange,” và “Mango,” xuất hiện nhiều lần trong bảng.
Bây giờ, giả sử chúng ta muốn tìm giá của trái xoài vào tháng hai bằng cách tìm kiếm từ trên xuống dưới trong bảng. Tiêu chí của chúng ta được lưu trữ trong ô F2 và G2, trong đó F2 chứa “Apple,” và G2 chứa “February.”
Dưới đây là công thức để sử dụng (hiển thị trong thanh công thức trong screenshot ở trên).
Công thức:
=ArrayFormula(XLOOKUP(1, (A2:A10=F2) * (C2:C10=G2), D2:D10, “”, 0, 1))
Phân tích Công thức:
Công thức này thực hiện theo cú pháp XLOOKUP: XLOOKUP(tìm_kiếm_khóa, phạm_vi_nhìn, phạm_vi_kết_quả, [giá_trị_thiếu], [chế_độ_khớp], [chế_độ_tìm_kiếm])
Trong đó:
- tìm_kiếm_khóa: 1
- phạm_vi_nhìn: (A2:A10=F2) * (C2:C10=G2)
- phạm_vi_kết_quả: D2:D10
- giá_trị_thiếu: “”
- chế_độ_khớp: 0 (khớp chính xác)
- chế_Độ_tìm_kiếm: 1 (Tìm kiếm từ giá trị đầu tiên đến giá trị cuối cùng; sử dụng -1 để tìm kiếm từ giá trị cuối cùng đến giá trị đầu tiên.)
Logic trong XLOOKUP với nhiều tiêu chí này rất đơn giản.
Phạm vi_nhìn trong XLOOKUP phải là một cột hoặc một hàng duy nhất, nhưng trong trường hợp của chúng ta, chúng tôi cần tìm kiếm trong hai cột: cột A chứa mặt hàng và cột C chứa tháng.
Để giải quyết điều này, chúng ta kết hợp cả hai cột tiêu chí thành một cột duy nhất bằng cách sử dụng (A2:A10=F2) * (C2:C10=G2) trong phạm_vi_nhìn. Việc sử dụng hàm ARRAYFORMULA với XLOOKUP là cần thiết vì logic Boolean này yêu cầu hỗ trợ công thức mảng.
- (A2:A10=F2): Trả về TRUE nơi mà tiêu chí đầu tiên (“Mango”) khớp; khác không trả về FALSE.
- (C2:C10=G2): Trả về TRUE nơi mà tiêu chí thứ hai (“February”) khớp; khác không trả về FALSE.
Việc nhân TRUE x TRUE là 1. Theo cách tương tự, phạm_vi_nhìn trả về một cột với giá trị 1 hoặc 0, và đó là lý do tại sao chúng tôi sử dụng 1 như tiêu chí.
XLOOKUP với Nhiều Tiêu Chí: Khớp Gần Đúng
Trong ví dụ trước, chúng tôi khám phá việc sử dụng XLOOKUP với nhiều tiêu chí để khớp chính xác.
Công thức có thể được áp dụng để tìm kiếm từ giá trị đầu tiên đến giá trị cuối cùng hoặc ngược lại trong bảng. Bằng cách thay đổi chế độ tìm kiếm bằng -1 trong công thức, bạn có thể thay đổi hướng tìm kiếm.
Bây giờ, hãy đi sâu vào một ví dụ liên quan đến khớp gần đúng, sử dụng chế độ khớp 1 hoặc -1 (thay vì 0 trong ví dụ trước).
Giả sử chúng ta muốn tìm giá của trái xoài (F2) nhận vào ngày 02/02/2024 (G2) hoặc, nếu không có biên nhận, vào ngày gần nhất mới nhất có sẵn. Làm thế nào chúng ta có thể đạt được điều này?
Công thức:
=ArrayFormula(XLOOKUP(G2, IF(A2:A10=”Mango”, B2:B10), D2:D10, “”, 1, 1))
Đây đại diện cho một ví dụ về XLOOKUP với nhiều tiêu chí và khớp gần đúng trong Google Sheets.
Trong tình huống này, search_key là tiêu chí ngày trong ô G2, và result_range là D2:D10.
Phạm_vi_nhìn được cung cấp bởi IF(A2:A10=”Mango”, B2:B10), cho ra một cột với ngày tương ứng của mục Mango và FALSE trong tất cả các hàng khác.
Trong cột này (các giá trị được trả về bởi kiểm tra logic IF), XLOOKUP có thể thực hiện khớp gần đúng của khóa tìm kiếm, đó là ngày trong ô G2.
Bây giờ, để tìm giá của trái xoài (F2) nhận vào ngày 02/02/2024 (G2) hoặc, nếu không có biên nhận, vào ngày gần nhất sớm nhất có sẵn, thay thế chế độ khớp 1 bằng -1 trong công thức:
=ArrayFormula(XLOOKUP(G2, IF(A2:A10=”Mango”, B2:B10), D2:D10, “”, -1, 1))
Ở đây, bạn cũng có thể điều hướng công thức để tìm kiếm từ dưới lên trên bằng cách thay thế đối số cuối cùng trong công thức bằng -1.
XLOOKUP với Nhiều Tiêu Chí: Khớp Một Phần
Đối với một khớp một phần, chúng ta cần sử dụng chế độ khớp 2 trong XLOOKUP. Tuy nhiên, trong XLOOKUP với nhiều tiêu chí, việc sử dụng trực tiếp chế độ khớp 2 không khả thi.
Thay vào đó, chúng ta có thể sử dụng REGEXMATCH hoặc SEARCH khi kết hợp nhiều cột tìm kiếm thành một cột duy nhất. Tôi thích sử dụng SEARCH vì nó có vẻ đơn giản hơn.
Hãy xem xét một tình huống trong đó chúng ta có các nhãn loại hạng cho các loại trái cây trong cột A. Thay vì mục “Mango,” chúng ta có các biến thể như “Mango MG-A,” “Mango MG-EQ,” “Mango OG-M,” v.v.
Bây giờ, nếu chúng ta muốn tìm giá của trái xoài nhận vào tháng một, công thức sẽ là:
Công thức:
=ArrayFormula(XLOOKUP(1, SEARCH(F2, A2:A10) * (C2:C10=G2), D2:D10, “”, 0, 1))
XLOOKUP với nhiều tiêu chí này hoạt động như sau:
- search_key là 1, cho thấy việc áp dụng logic Boolean trong phạm_vi_nhìn.
- phạm_vi_nhìn là SEARCH(F2, A2:A10) * (C2:C10=G2). Ở đây, SEARCH trả về 1 bất cứ khi nào nó khớp một phần với tiêu chí đầu tiên trong cột mục. Phần thứ hai, (C2:C10=G2), khớp với tháng trong cột tháng và trả về TRUE hoặc FALSE.
- Khi nhân, công thức trả về 1 hoặc 0 và chúng tôi đang tìm kiếm 1.
Trong ngữ cảnh của bảng này, nếu bạn muốn tìm giá của trái xoài nhận vào ngày 02/02/2024 hoặc ngày gần nhất mới nhất, bạn có thể sử dụng công thức sau:
=ArrayFormula(XLOOKUP(G2, IF(SEARCH(F2, A2:A10)=1, B2:B10), D2:D10, “”, 1, 1))
Điều này tuân theo logic của IF, mà chúng ta đã sử dụng trong khớp gần đúng. Trong trường hợp đó, chúng tôi đã sử dụng IF(A2:A10=”Mango”, B2:B10) như phạm_vi_nhìn. Ở đây, chúng tôi kết hợp hàm SEARCH và sử dụng IF(SEARCH(F2, A2:A10)=1, B2:B10) như phạm_vi_nhìn.
Tài liệu tham khảo
XLOOKUP là một chủ đề rộng lớn. Sau khi bạn đã tìm hiểu về XLOOKUP với nhiều tiêu chí, bạn có thể khám phá thêm các mẹo bổ sung bằng cách làm theo các hướng dẫn dưới đây.
- XLOOKUP Visible (Filtered) Data in Google Sheets
- XLOOKUP Nth Match Value in Google Sheets
- Nested XLOOKUP Function in Google Sheets
- VLOOKUP and XLOOKUP: Sự khác biệt quan trọng trong Google Sheets
