Cari nilai kecocokan terdekat dengan beberapa kriteria
Dalam beberapa kasus, Anda mungkin perlu mencari nilai kecocokan terdekat atau perkiraan berdasarkan lebih dari satu kriteria. Dengan kombinasi INDEKS, MATCH dan IF fungsi, Anda dapat dengan cepat menyelesaikannya di Excel.
Bagaimana cara mencari nilai kecocokan terdekat dengan lebih dari satu kriteria?
Seperti yang ditunjukkan gambar di bawah ini, Anda perlu menemukan orang yang tepat untuk pekerjaan itu berdasarkan dua kriteria “jurusannya komputer"Dan" pengalaman kerja 15 tahun ”.
Note: Agar ini berfungsi dengan benar, jika ada jurusan rangkap, pengalaman kerja dari jurusan rangkap ini harus diurutkan dalam urutan menaik.
1. Pilih sel kosong untuk menampilkan hasilnya, lalu salin rumus di bawah ini ke dalamnya dan tekan Ctrl + perubahan + Enter kunci untuk mendapatkan hasil.
=INDEX(D3:D8,MATCH(G5,IF(B3:B8=G4,C3:C8),1))
Catatan: dalam rumus ini:
- D3: D8 adalah kisaran kolom berisi hasil yang Anda cari;
- G5 berisi kriteria kedua (pengalaman nomor 15) Anda akan mencari nilai berdasarkan;
- G4 berisi kriteria pertama (Komputer) yang akan Anda cari berdasarkan nilai;
- B3: B8 adalah rentang sel yang cocok dengan kriteria pertama;
- C3: C8 adalah kisaran sel yang cocok dengan kriteria kedua;
- Jumlah 1 adalah perkiraan pencarian, yang berarti jika nilai eksak tidak dapat ditemukan, ia akan menemukan nilai terbesar yang lebih kecil dari nilai pencarian;
- Rumus ini harus dimasukkan sebagai rumus array dengan Ctrl + perubahan + Enter kunci.
Bagaimana rumus ini bekerja
Rumus ini dapat dipecah menjadi beberapa komponen:
- IF(B3:B8=G4,C3:C8): fungsi IF di sini mengembalikan hasil sebagai {9;13;FALSE;FALSE;FALSE;FALSE}, yang berasal dari pengujian nilai di B3: B8 untuk melihat apakah nilai tersebut cocok dengan nilai di G4. Jika ada yang cocok, maka mengembalikan nilai yang sesuai, jika tidak, mengembalikan FALSE. Di sini menemukan dua kecocokan dan empat ketidakcocokan.
- Rumus array =MATCH(G5,{9;13;FALSE;FALSE;FALSE;FALSE},1): fungsi MATCH menemukan posisi angka 15 (nilai dalam G5) dalam rentang C3: C8. Karena angka 15 tidak dapat ditemukan, ini cocok dengan nilai terkecil berikutnya 13. Jadi hasilnya di sini adalah 2.
- Dan =INDEX(D3:D8,2): Fungsi INDEX mengembalikan nilai sel kedua dalam rentang D3: D8. Jadi hasil akhirnya adalah Amy.
Fungsi terkait
Fungsi IF Excel
Fungsi IF adalah salah satu fungsi paling sederhana dan paling berguna di buku kerja Excel. Ini melakukan tes logika sederhana yang bergantung pada hasil perbandingan, dan mengembalikan satu nilai jika hasilnya BENAR, atau nilai lain jika hasilnya SALAH.
Fungsi Excel MATCH
Fungsi Microsoft Excel MATCH mencari nilai tertentu dalam rentang sel, dan mengembalikan posisi relatif dari nilai ini.
Fungsi Excel INDEX
Fungsi INDEX mengembalikan nilai yang ditampilkan berdasarkan posisi tertentu dari rentang atau larik.
Terkait artikel
Rata-rata sel berdasarkan beberapa kriteria
Di Excel, sebagian besar dari kita mungkin terbiasa dengan fungsi COUNTIF dan SUMIF, mereka dapat membantu kita menghitung atau menjumlahkan nilai berdasarkan kriteria. Tapi pernahkah Anda mencoba menghitung rata-rata nilai berdasarkan satu atau lebih kriteria di Excel? Tutorial ini memberikan contoh dan rumus secara detail untuk membantu menyelesaikannya dengan mudah.
Klik untuk mengetahui lebih lanjut ...
Hitung sel jika salah satu dari beberapa kriteria terpenuhi
Tutorial ini membagikan cara menghitung sel jika mengandung X atau Y atau Z… dll. Di Excel.
Klik untuk mengetahui lebih lanjut ...
Hitung nilai unik berdasarkan beberapa kriteria
Artikel ini mengambil beberapa contoh untuk menghitung nilai unik berdasarkan satu atau beberapa kriteria dalam lembar kerja dengan metode rinci langkah demi langkah.
Klik untuk mengetahui lebih lanjut ...
Alat Produktivitas Kantor Terbaik
Kutools for Excel - Membantu Anda Menonjol Dari Kerumunan
Kutools for Excel Membanggakan Lebih dari 300 Fitur, Memastikan Apa yang Anda Butuhkan Hanya Dengan Sekali Klik...
Tab Office - Aktifkan Pembacaan dan Pengeditan dengan Tab di Microsoft Office (termasuk Excel)
- Satu detik untuk beralih di antara lusinan dokumen terbuka!
- Kurangi ratusan klik mouse untuk Anda setiap hari, ucapkan selamat tinggal pada tangan mouse.
- Meningkatkan produktivitas Anda sebesar 50% saat melihat dan mengedit banyak dokumen.
- Menghadirkan Tab Efisien ke Office (termasuk Excel), Sama Seperti Chrome, Edge, dan Firefox.
