Tutorial Excel: Pencarian dua arah dengan perkiraan INDEX dan MATCH

Daftar Isi

Pintasan terkait

Dalam video ini, kita akan melihat cara membuat pencarian dua arah dengan INDEX dan MATCH, menggunakan perkiraan kecocokan.

Di sini kami memiliki kalkulator biaya sederhana, yang mencari biaya berdasarkan lebar dan tinggi material. Kecocokan harus berupa perkiraan. Misalnya, jika lebarnya 250, dan tingginya 325, hasil yang benar adalah $ 1.800.

Jika lebarnya 450, dan tingginya tetap di 325, hasil yang benar adalah $ 3.600.

Kita bisa membangun rumus yang melakukan pencarian ini menggunakan INDEX dan MATCH.

Pertama, mari kita membuat INDEX berfungsi sebagai bukti konsep, dengan melakukan hardcode bagian dari rumus. Ini adalah cara yang bagus untuk memastikan Anda memiliki ide yang tepat, sebelum memulai.

Jadi, dengan data di tabel kita sebagai larik, dan dengan lebar 450 dan tinggi 325, INDEX akan membutuhkan nomor baris 3, dan nomor kolom 4, untuk mengambil nilai yang benar di tabel . Ini berfungsi dengan baik tetapi tentu saja itu tidak akan berubah karena nilainya di-hardcode.

Jadi selanjutnya, mari kita siapkan fungsi MATCH yang kita butuhkan untuk menghitung nilai-nilai ini.

Untuk mendapatkan nilai lebar, yaitu nomor baris di INDEX, kita akan menggunakan nilai pencarian dari M7, dan nilai di kolom B, sebagai array pencarian. Untuk jenis pencocokan, kami ingin menggunakan 1 untuk perkiraan kecocokan karena nilai diurutkan dalam urutan menaik. Hasilnya adalah 4.

Untuk mendapatkan tinggi, yang merupakan kolom di dalam INDEX, kita akan kembali menggunakan MATCH dengan nilai dari M8, nilai ketinggian dari baris 6. Sekali lagi, jenis pencocokan diatur lagi ke 1 untuk perkiraan kecocokan. Hasilnya adalah 3.

Sekarang jika saya mengubah lebar menjadi 350 dan tinggi menjadi 550, kita mendapatkan hasil yang baru.

Nilai-nilai ini persis seperti yang kami butuhkan untuk INDEX. Jadi sekarang saya hanya akan menyalin dan menempelkan fungsi MATCH ke dalam rumus INDEX asli.

Lebar masuk untuk nomor baris.

Dan tinggi masuk untuk nomor kolom.

Kami sekarang memiliki pencarian dinamis yang menghitung biaya dengan benar berdasarkan lebar dan tinggi, yang merupakan perkiraan pencocokan.