=XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7) mengembalikan harga dari baris tempat produknya E2 dan ukurannya F2. Setiap perbandingan memeriksa semua baris, perkaliannya hanya bernilai 1 kalau keduanya benar, dan XLOOKUP mencari angka 1 itu. Rumus ini butuh Excel 2021 atau Microsoft 365; versi INDEX MATCH di bawah bekerja di semua versi.
| 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 dan Large bertemu di baris 5, jadi G2 mengembalikan 3.00 lagi, dari baris yang lain. Tambahkan argumen keempat untuk kasus ketika tidak ada baris yang cocok dengan keduanya: =XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7,"No such item").
Cara kerja kondisi yang dikalikan
A2:A7=E2 membandingkan setiap produk dengan E2 dan mengembalikan enam nilai TRUE atau FALSE. Mengalikan dua daftar seperti itu mengubah TRUE menjadi 1 dan FALSE menjadi 0, dan sebuah baris hanya bernilai 1 kalau bernilai 1 di kedua daftar. Kolom D menampilkan daftar itu, yang spill dari satu rumus.
| 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 |
Hanya D5 yang bernilai 1. Ubah F2 atau G2, maka angka 1 itu berpindah. Setiap kriteria tambahan cukup satu *(range=value) lagi, dan kondisinya tidak harus berupa kesamaan: *(C2:C7<3) menambahkan syarat "harga di bawah 3". Setiap rentang harus mencakup baris yang sama (A2:A7, B2:B7, C2:C7): kalau rentang hasilnya berbeda ukuran dengan kondisinya, XLOOKUP mengembalikan #VALUE!.
INDEX MATCH dengan banyak kriteria
Untuk Excel 2019 dan versi lebih lama, MATCH bisa mencari angka 1 di array yang sama dan INDEX mengembalikan harga dari posisi tersebut.
| 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 dan Large ada di posisi 2 dalam array, dan INDEX mengembalikan $3.50. Di Excel 2019 dan versi lebih lama, ini adalah rumus array: tekan Ctrl+Shift+Enter (Cmd+Shift+Enter di Mac), bukan Enter, dan Excel menampilkannya di dalam kurung kurawal. Menekan Enter biasa di sana umumnya menghasilkan #N/A atau #VALUE!. Di Excel 365, Enter saja sudah cukup. Bentuk dengan satu kriteria ada di halaman INDEX dan MATCH.
Gabungkan kriteria menjadi satu kunci
Cara lainnya adalah mengubah dua kriteria menjadi satu dengan menggabungkannya. VLOOKUP butuh nilai gabungan itu di kolom bantu di awal tabel (halaman VLOOKUP menunjukkan versi tersebut). XLOOKUP bisa menggabungkan rentangnya di dalam rumus, jadi tidak perlu kolom bantu.
| 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 membuat enam kunci seperti Juice|Large, dan XLOOKUP menemukan Juice|Large di antaranya: $4.00. Taruh pemisah di antara bagian-bagiannya. Tanpa pemisah, "AB" dan "C" menjadi "ABC" yang sama dengan gabungan "A" dan "BC", dan lookup bisa mengembalikan baris yang salah.
Kalau nilai yang kamu cari berupa angka dan setiap kombinasi hanya muncul sekali, SUMIFS memberi jawaban yang sama tanpa array sama sekali: =SUMIFS(C2:C7,A2:A7,E2,B2:B7,F2). Rumus ini mengembalikan 0, bukan error, kalau tidak ada yang cocok, dan itu bisa menyembunyikan salah ketik.
Kembalikan semua hasil yang cocok dengan FILTER
XLOOKUP dan INDEX MATCH mengembalikan baris pertama yang cocok. Kalau beberapa baris cocok dan kamu ingin semuanya, pakai FILTER dengan kondisi yang sama.
| 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 |
Tiga baris berisi North dan Phone, jadi F2 menampilkan kuartal dan penjualannya dengan spill ke F2:G4. Ubah A3 menjadi South, maka daftarnya berkurang menjadi dua. Kalau tidak ada baris yang cocok, FILTER mengembalikan #CALC!; argumen ketiga seperti "None" menampilkan teks sebagai gantinya. Pilihan lainnya ada di halaman FILTER.
Latihan: tiga kriteria
| 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 |
Giliranmu: Kembalikan di G4 penjualan untuk wilayah di G1, produk di G2 dan kuartal di G3.
Pertanyaan yang sering diajukan
Bagaimana cara memakai XLOOKUP dengan banyak kriteria?
Kalikan satu perbandingan untuk setiap kriteria lalu cari angka 1: =XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7). Setiap perbandingan memberi TRUE atau FALSE per baris, hasil perkaliannya hanya 1 kalau semuanya TRUE, dan XLOOKUP mengembalikan baris pertama yang seperti itu.
Bagaimana cara INDEX MATCH dengan dua kriteria?
Pakai kondisi yang dikalikan tadi di dalam MATCH: =INDEX(C2:C7,MATCH(1,(A2:A7=E2)*(B2:B7=F2),0)). Di Excel 2019 dan versi lebih lama, konfirmasi dengan Ctrl+Shift+Enter (Cmd+Shift+Enter di Mac).
Apakah VLOOKUP bisa memakai dua kriteria?
Tidak secara langsung. Tambahkan kolom bantu di awal tabel yang menggabungkan kedua nilai, misalnya =A2&"|"&B2, lalu cari nilai gabungannya: =VLOOKUP(E2&"|"&F2,helper_table,col,FALSE).
Apakah SUMIFS bisa menggantikan lookup dengan dua kriteria?
Bisa, kalau nilainya berupa angka dan setiap kombinasi hanya muncul sekali: =SUMIFS(C2:C7,A2:A7,E2,B2:B7,F2). Rumus ini mengembalikan 0, bukan #N/A, kalau tidak ada baris yang cocok, dan menjumlahkan nilainya kalau sebuah kombinasi muncul dua kali.
Bagaimana cara lookup dengan kriteria OR?
Jumlahkan kondisinya, jangan dikalikan: (A2:A7="Tea")+(A2:A7="Juice") bernilai 1 atau lebih kalau salah satunya benar. Cari nilai yang lebih besar dari 0, misalnya dengan =XLOOKUP(TRUE,((A2:A7="Tea")+(A2:A7="Juice"))>0,C2:C7).
