Rumus Excel: Pencarian multi-kriteria dan mengubah urutan -

Daftar Isi

Formula umum

(=INDEX(rng1,MATCH(1,($A1=rng2)*(B$1=rng3),0)))

Ringkasan

Untuk melakukan pencarian multi-kriteria dan mengubah urutan hasil ke dalam tabel, Anda bisa menggunakan rumus array yang didasarkan pada INDEX dan MATCH. Pada contoh yang ditunjukkan, rumus di G5 adalah:

(=INDEX(amount,MATCH(1,($F5=location)*(G$4=date),0)))

Perhatikan bahwa rumus ini adalah rumus array dan harus dimasukkan dengan control + shift + enter.

Rumus ini juga menggunakan tiga rentang bernama: lokasi = B5: B13, jumlah = D5: D13, tanggal = C5: C13

Penjelasan

Inti dari rumus ini adalah INDEX, yang mengambil nilai dari rentang bernama "jumlah" (B5: B13):

=INDEX(amount,row_num)

di mana row_num dikerjakan dengan fungsi MATCH dan beberapa logika boolean:

MATCH(1,($F5=location)*(G$4=date),0)

Dalam potongan ini, lokasi di F5 dibandingkan dengan semua lokasi, dan tanggal di G4 dibandingkan dengan semua tanggal. Hasil dalam setiap kasus adalah larik nilai TRUE dan FALSE. Saat array ini dikalikan bersama, operasi matematika memaksa nilai TRUE dan FALSE menjadi satu dan nol, sehingga array pencarian yang masuk ke MATCH terlihat seperti ini:

(1;0;0;0;0;0;0;0;0)

MATCH diatur untuk mencocokkan 1 sebagai pencocokan tepat, dan mengembalikan posisi ke INDEX sebagai nomor baris. Angka 1 berfungsi untuk nilai pencarian karena array sekarang hanya berisi 1 dan 0, seperti yang ditunjukkan di atas.

F5 dan G4 dimasukkan sebagai referensi campuran sehingga rumus dapat disalin melalui tabel tanpa modifikasi.