=INDEX(C2:C6,MATCH(F2,A2:A6,0)) mencari baris tempat F2 muncul di A2:A6 lalu menghasilkan nilai dari baris yang sama di C2:C6. MATCH menemukan posisinya, INDEX mengambil nilai di posisi itu. Rumus ini berfungsi di semua versi Excel, dan bisa mencari ke kiri.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | P-101 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | P-102 | |||
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
Ubah F2 menjadi Milk dan G2 menghasilkan $1,10. Ubah C2:C6 menjadi B2:B6 dan hasilnya berupa kategori.
Cara INDEX dan MATCH bekerja bersama
Rumus ini terdiri dari dua langkah dalam satu sel. Di sini keduanya dipisah ke sel yang berbeda, jadi kamu bisa melihat apa yang dihasilkan setiap bagiannya.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Look for | Bread | |
| 2 | Apple | Fruit | $1.20 | P-101 | Position | 4 | |
| 3 | Pear | Fruit | $1.50 | P-102 | Price | $2.40 | |
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
MATCH(G1,A2:A6,0) menghasilkan 4, karena Bread adalah item keempat di A2:A6. INDEX(C2:C6,4) menghasilkan item keempat di C2:C6, yaitu $2,40. Taruh MATCH di dalam INDEX sebagai pengganti G2 dan kamu mendapatkan rumus satu sel itu. Ada dua aturan yang membuatnya berfungsi:
- Kedua rentang harus dimulai di baris yang sama dan punya tinggi yang sama.
MATCH(...,A2:A6,0)menghitung dari baris 2, jadi INDEX harus membacaC2:C6, bukanC1:C6(yang akan menghasilkan baris di atasnya). - Akhiri MATCH dengan 0. Tanpa angka itu, MATCH melakukan pencocokan perkiraan yang menganggap kolom A sudah diurutkan, dan pada daftar nama, MATCH bisa menghasilkan posisi baris yang salah. Halaman MATCH membahas ketiga jenis pencocokannya.
Kalau nilainya tidak ada di daftar, MATCH menghasilkan #N/A, begitu juga seluruh rumusnya. =IFNA(INDEX(C2:C6,MATCH(F2,A2:A6,0)),"Not found") menampilkan teks sebagai gantinya.
Pencarian ke kiri
VLOOKUP mengembalikan kolom di sebelah kanan kolom yang dicari. INDEX dan MATCH tidak peduli urutannya: cari di kolom D, kembalikan kolom A.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Code | Product | |
| 2 | Apple | Fruit | $1.20 | P-101 | P-310 | Bread | |
| 3 | Pear | Fruit | $1.50 | P-102 | |||
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
P-310 menghasilkan Bread. Ketik P-205 di F2 untuk mendapatkan Carrot. Dengan VLOOKUP, kamu harus memindahkan kolom Code ke depan tabel lebih dulu.
Pencarian dua arah: INDEX dengan dua MATCH
INDEX menerima nomor baris dan nomor kolom. Berikan satu tabel utuh dan biarkan satu MATCH menemukan barisnya dan MATCH lain menemukan kolomnya.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar |
| 2 | North | 4,200 | 3,900 | 4,800 |
| 3 | South | 3,100 | 3,600 | 3,300 |
| 4 | East | 5,200 | 4,700 | 5,600 |
| 5 | West | 2,800 | 3,000 | 3,400 |
| 6 | Region | East | ||
| 7 | Month | Mar | ||
| 8 | Sales | 5,600 |
East adalah baris 3 di A2:A5 dan Mar adalah kolom 3 di B1:D1, jadi INDEX menghasilkan baris 3, kolom 3 dari B2:D5: 5.600. MATCH untuk baris mencari ke bawah di kolom pertama, MATCH untuk kolom mencari ke samping di baris header, dan kedua rentang itu sejajar dengan tabel B2:D5.
Kenapa INDEX MATCH lebih unggul dari VLOOKUP
=VLOOKUP(F2, A2:D6, 3, FALSE)
=INDEX(C2:C6, MATCH(F2, A2:A6, 0))
Keduanya menghasilkan harga. Perbedaannya terlihat saat lembar kerjanya berubah:
- Menyisipkan kolom. Sisipkan kolom di antara Category dan Price, dan VLOOKUP tetap meminta kolom 3, yang sekarang adalah kolom baru yang kosong. Excel menyesuaikan
C2:C6di versi INDEX menjadiD2:D6, dan rumusnya tetap berfungsi. - Mencari ke kiri. Seperti ditunjukkan di atas: VLOOKUP tidak bisa, INDEX MATCH bisa.
Di Excel 2021 dan Microsoft 365, XLOOKUP melakukan keduanya dalam satu fungsi dengan argumen yang lebih sederhana. INDEX MATCH tetap menjadi pilihan untuk file yang harus bisa dibuka di Excel 2019 atau versi yang lebih lama, dan bagian INDEX-nya berguna juga secara terpisah. Untuk pencarian dengan dua kondisi sekaligus, lihat pencarian dengan banyak kriteria.
Latihan: mencari ke kiri
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Code | Price | Stock | Product | Look for | Price | |
| 2 | P-101 | $1.20 | 40 | Apple | Milk | ||
| 3 | P-102 | $1.50 | 25 | Pear | |||
| 4 | P-205 | $0.80 | 60 | Carrot | |||
| 5 | P-310 | $2.40 | 15 | Bread | |||
| 6 | P-412 | $1.10 | 30 | Milk |
Giliranmu: Nama produknya ada di kolom terakhir. Di G2, hasilkan harga produk di F2 dengan INDEX dan MATCH.
Latihan: pencarian dua arah
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Math | Science | Art |
| 2 | Ana | 78 | 85 | 92 |
| 3 | Ben | 64 | 71 | 88 |
| 4 | Cara | 95 | 89 | 73 |
| 5 | Dev | 82 | 67 | 79 |
| 6 | ||||
| 7 | Student | Cara | ||
| 8 | Subject | Science | ||
| 9 | Score |
Giliranmu: Di B9, hasilkan nilai siswa di B7 untuk mata pelajaran di B8.
Pertanyaan yang sering diajukan
Bagaimana cara kerja INDEX MATCH?
MATCH mencari posisi sebuah nilai di satu kolom, lalu INDEX menghasilkan nilai di posisi itu dari kolom lain. Di =INDEX(C2:C6,MATCH("Pear",A2:A6,0)), MATCH menghasilkan 2 karena Pear adalah item kedua di A2:A6, lalu INDEX menghasilkan item kedua di C2:C6.
Kenapa memakai INDEX MATCH, bukan VLOOKUP?
Rumus ini bisa mengembalikan kolom di sebelah kiri kolom yang dicari, dan tidak rusak saat sebuah kolom disisipkan di dalam tabel (tidak ada nomor kolom yang bisa menjadi usang). Di Excel 2021 dan Microsoft 365, XLOOKUP memberikan kelebihan yang sama dalam satu fungsi.
Apa arti angka 0 di MATCH?
Angka itu meminta pencocokan persis. Tanpa angka itu, MATCH memakai match_type 1, yaitu pencocokan perkiraan yang mengharapkan kolomnya sudah diurutkan secara menaik, dan pada daftar yang tidak urut, MATCH bisa menghasilkan posisi baris yang salah.
Bagaimana cara melakukan pencarian dua arah dengan INDEX MATCH?
Berikan INDEX satu tabel utuh dan dua MATCH, satu untuk baris dan satu untuk kolom: =INDEX(B2:D5,MATCH("South",A2:A5,0),MATCH("Feb",B1:D1,0)) menghasilkan nilai di pertemuan baris South dan kolom Feb.
