=XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7) trả về giá ở hàng có sản phẩm là E2 và cỡ là F2. Mỗi phép so sánh kiểm tra từng hàng, nhân chúng với nhau thì chỉ ra 1 ở hàng mà cả hai đều đúng, và XLOOKUP đi tìm số 1 đó. Cách này cần Excel 2021 hoặc Microsoft 365; phiên bản INDEX MATCH ở bên dưới chạy được trên mọi phiên bản.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Tea | Large | $3.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
Tea và Large gặp nhau ở hàng 5, nên G2 trả về 3.00, nhưng lấy từ một hàng khác. Thêm đối số thứ tư cho trường hợp không có hàng nào khớp cả hai: =XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7,"No such item").
Cách các điều kiện nhân với nhau hoạt động
A2:A7=E2 so sánh từng sản phẩm với E2 và trả về sáu giá trị TRUE hoặc FALSE. Nhân hai danh sách như vậy sẽ đổi TRUE thành 1 và FALSE thành 0, và một hàng chỉ bằng 1 khi nó bằng 1 ở cả hai danh sách. Cột D hiện danh sách đó, tràn (spill) ra từ một công thức duy nhất.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Both match | Product | Size | |
| 2 | Coffee | Small | $2.50 | 0 | Tea | Large | |
| 3 | Coffee | Large | $3.50 | 0 | |||
| 4 | Tea | Small | $2.00 | 0 | |||
| 5 | Tea | Large | $3.00 | 1 | |||
| 6 | Juice | Small | $3.00 | 0 | |||
| 7 | Juice | Large | $4.00 | 0 |
Chỉ D5 bằng 1. Đổi F2 hoặc G2 thì số 1 sẽ chuyển chỗ. Mỗi điều kiện thêm vào là thêm một *(range=value), và điều kiện không nhất thiết phải là so sánh bằng: *(C2:C7<3) thêm điều kiện "giá dưới 3". Mọi vùng phải trải trên cùng các hàng (A2:A7, B2:B7, C2:C7): nếu vùng trả về có kích thước khác với các vùng điều kiện, XLOOKUP trả về #VALUE!.
INDEX MATCH với nhiều điều kiện
Với Excel 2019 trở về trước, MATCH có thể tìm số 1 trong chính mảng đó, rồi INDEX trả về giá ở vị trí tìm được.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Coffee | Large | $3.50 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
Coffee và Large nằm ở vị trí 2 của mảng, và INDEX trả về $3.50. Trong Excel 2019 trở về trước, đây là công thức mảng: nhấn Ctrl+Shift+Enter (trên Mac là Cmd+Shift+Enter) thay vì Enter, và Excel sẽ hiện công thức trong cặp ngoặc nhọn. Chỉ nhấn Enter thì thường nhận về #N/A hoặc #VALUE!. Trong Excel 365, nhấn Enter là đủ. Dạng một điều kiện có ở trang INDEX và MATCH.
Nối các điều kiện thành một khóa
Cách còn lại là gộp hai điều kiện thành một bằng cách nối chúng. VLOOKUP cần các giá trị đã nối nằm trong một cột phụ ở đầu bảng (trang VLOOKUP có hướng dẫn cách này). XLOOKUP có thể nối các vùng ngay trong công thức, nên không cần cột phụ.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Juice | Large | $4.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
A2:A7&"|"&B2:B7 tạo ra sáu khóa như Juice|Large, và XLOOKUP tìm Juice|Large trong số đó: $4.00. Hãy đặt ký tự phân cách giữa các phần. Không có nó, "AB" và "C" nối lại thành "ABC" giống hệt "A" và "BC", và phép dò tìm có thể trả về sai hàng.
Nếu giá trị cần lấy là số và mỗi tổ hợp chỉ xuất hiện một lần, SUMIFS cho cùng kết quả mà không cần mảng: =SUMIFS(C2:C7,A2:A7,E2,B2:B7,F2). Hàm trả về 0 thay vì báo lỗi khi không có gì khớp, điều này có thể che mất một lỗi gõ sai.
Lấy mọi kết quả khớp với FILTER
XLOOKUP và INDEX MATCH trả về hàng khớp đầu tiên. Khi có nhiều hàng khớp và bạn muốn lấy hết, hãy dùng FILTER với cùng các điều kiện.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Matches | ||
| 2 | North | Laptop | Q1 | 120 | Q1 | 210 | |
| 3 | North | Phone | Q1 | 210 | Q2 | 225 | |
| 4 | South | Laptop | Q1 | 95 | Q3 | 240 | |
| 5 | North | Phone | Q2 | 225 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | North | Laptop | Q2 | 135 | |||
| 8 | North | Phone | Q3 | 240 |
Có ba hàng là North và Phone, nên F2 tràn quý và doanh số của chúng ra F2:G4. Đổi A3 thành South thì danh sách rút xuống còn hai. Nếu không có hàng nào khớp, FILTER trả về #CALC!; đối số thứ ba như "None" sẽ hiện chữ thay thế. Xem thêm các tùy chọn ở trang FILTER.
Luyện tập: ba điều kiện
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Region | South | |
| 2 | North | Laptop | Q1 | 120 | Product | Laptop | |
| 3 | North | Laptop | Q2 | 135 | Quarter | Q2 | |
| 4 | North | Phone | Q1 | 210 | Sales | ||
| 5 | South | Laptop | Q1 | 95 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | South | Laptop | Q2 | 110 | |||
| 8 | North | Phone | Q2 | 225 |
Đến lượt bạn: Trả về trong G4 doanh số của vùng ở G1, sản phẩm ở G2 và quý ở G3.
Câu hỏi thường gặp
Làm sao dùng XLOOKUP với nhiều điều kiện?
Nhân các phép so sánh, mỗi điều kiện một phép, rồi tìm số 1: =XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7). Mỗi phép so sánh cho TRUE hoặc FALSE ở từng hàng, tích chỉ bằng 1 khi tất cả đều TRUE, và XLOOKUP trả về hàng đầu tiên như vậy.
Làm sao dùng INDEX MATCH với hai điều kiện?
Đặt chính các điều kiện nhân với nhau đó vào trong MATCH: =INDEX(C2:C7,MATCH(1,(A2:A7=E2)*(B2:B7=F2),0)). Trong Excel 2019 trở về trước, xác nhận công thức bằng Ctrl+Shift+Enter (trên Mac là Cmd+Shift+Enter).
VLOOKUP có dùng được hai điều kiện không?
Không trực tiếp. Thêm một cột phụ ở đầu bảng để nối hai giá trị lại, ví dụ =A2&"|"&B2, rồi dò tìm giá trị đã nối: =VLOOKUP(E2&"|"&F2,helper_table,col,FALSE).
SUMIFS có thay được phép dò tìm hai điều kiện không?
Được, khi giá trị cần lấy là số và mỗi tổ hợp chỉ xuất hiện một lần: =SUMIFS(C2:C7,A2:A7,E2,B2:B7,F2). Hàm trả về 0 thay vì #N/A khi không có hàng nào khớp, và cộng dồn các giá trị nếu một tổ hợp xuất hiện hai lần.
Làm sao dò tìm với điều kiện OR?
Cộng các điều kiện thay vì nhân: (A2:A7="Tea")+(A2:A7="Juice") bằng 1 trở lên khi ít nhất một điều kiện đúng. Sau đó tìm giá trị lớn hơn 0, ví dụ =XLOOKUP(TRUE,((A2:A7="Tea")+(A2:A7="Juice"))>0,C2:C7).
