Excel Lookup Formula dengan Beberapa Kriteria

Dengan menggunakan rumus array di Excel kita dapat membuat rumus pencarian yang menggunakan beberapa kriteria untuk menemukan informasi dalam database atau tabel data.

Rumus larik melibatkan penumpukan fungsi MATCH di dalam fungsi INDEX .

Tutorial ini mencakup contoh langkah demi langkah untuk membuat rumus pencarian yang menggunakan beberapa kriteria untuk menemukan pemasok Widget titanium dalam database sampel.

Mengikuti langkah-langkah dalam topik tutorial di bawah ini memandu Anda dalam membuat dan menggunakan rumus yang terlihat pada gambar di atas.

01 09

Memasukkan Data Tutorial

Langkah pertama dalam tutorial adalah memasukkan data ke dalam lembar kerja Excel.

Untuk mengikuti langkah-langkah dalam tutorial masukkan data yang ditunjukkan pada gambar di atas ke sel - sel berikut.

• Masukkan kisaran data teratas ke dalam sel D1 ke F2
• Masukkan rentang kedua ke dalam sel D5 hingga F11

Baris 3 dan 4 dibiarkan kosong untuk mengakomodasi rumus larik yang dibuat selama tutorial ini.

Tutorial tidak menyertakan pemformatan yang terlihat pada gambar, tetapi ini tidak akan memengaruhi cara kerja rumus pencarian.

Informasi tentang opsi pemformatan yang serupa dengan yang terlihat di atas tersedia dalam Tutorial Memformat Excel Dasar ini.

02 09

Memulai Fungsi INDEX

Fungsi INDEX adalah salah satu dari beberapa di Excel yang memiliki beberapa bentuk. Fungsi ini memiliki Formulir Array dan Formulir Referensi .

Formulir Array mengembalikan data aktual dari database atau tabel data, sementara Formulir Referensi memberi Anda referensi sel atau lokasi data dalam tabel.

Dalam tutorial ini kita akan menggunakan Formulir Array karena kita ingin mengetahui nama pemasok untuk widget titanium daripada referensi sel ke pemasok ini dalam database kami.

Setiap formulir memiliki daftar argumen berbeda yang harus dipilih sebelum memulai fungsi.

Langkah-langkah Tutorial

1. Klik pada sel F3 untuk membuatnya menjadi sel aktif . Di sinilah kita akan memasuki fungsi bertingkat.
2. Klik pada tab Rumus dari menu pita .
3. Pilih Lookup dan Referensi dari pita untuk membuka daftar drop-down fungsi.
4. Klik pada INDEX dalam daftar untuk memunculkan kotak dialog Pilih Argumen .
5. Pilih opsi array, row_num, col_num di kotak dialog.
6. Klik OK untuk membuka kotak dialog fungsi INDEX.

03 09

Memasukkan INDEX Function Array Argument

Argumen pertama yang diperlukan adalah argumen Array. Argumen ini menentukan rentang sel yang akan dicari untuk data yang diinginkan.

Untuk tutorial ini, argumen ini akan menjadi basis data contoh kami.

Langkah-langkah Tutorial

1. Dalam kotak dialog fungsi INDEX, klik pada baris Array .
2. Sorot sel D6 ke F11 di lembar kerja untuk memasukkan rentang ke kotak dialog.

04 09

Memulai Fungsi MATCH Bersarang

Ketika bersarang satu fungsi di dalam yang lain tidak mungkin untuk membuka kotak dialog fungsi kedua atau bertingkat untuk memasukkan argumen yang diperlukan.

Fungsi bertingkat harus diketik sebagai salah satu argumen dari fungsi pertama.

Dalam tutorial ini, fungsi MATCH yang bersarang dan argumennya akan dimasukkan ke dalam baris kedua dari kotak dialog fungsi INDEX - baris Row_num .

Penting untuk dicatat bahwa, ketika memasukkan fungsi secara manual, argumen fungsi dipisahkan satu sama lain dengan koma "," .

Memasukkan Argumen Lookup_value Fungsi MATCH

Langkah pertama dalam memasuki fungsi MATCH bersarang adalah memasukkan argumen Lookup_value .

Lookup_value adalah lokasi atau referensi sel untuk istilah pencarian yang ingin kami tandingkan dalam basis data.

Biasanya, Lookup_value hanya menerima satu kriteria atau istilah pencarian. Untuk mencari beberapa kriteria, kita harus memperluas Lookup_value .

Ini dilakukan dengan menggabungkan atau menggabungkan dua atau lebih referensi sel bersama-sama menggunakan simbol ampersand " & ".

Langkah-langkah Tutorial

1. Dalam kotak dialog fungsi INDEX, klik baris Row_num .
2. Ketik nama fungsi yang cocok diikuti dengan braket bulat terbuka " ( "
3. Klik pada sel D3 untuk memasukkan referensi sel ke dalam kotak dialog.
4. Ketik ampersand " & " setelah referensi sel D3 untuk menambahkan referensi sel kedua.
5. Klik pada sel E3 untuk memasukkan referensi sel kedua ini ke dalam kotak dialog.
6. Ketik koma "," setelah referensi sel E3 menyelesaikan entri dari argumen Lookup_value fungsi MATCH.
7. Biarkan kotak dialog fungsi INDEX terbuka untuk langkah berikutnya dalam tutorial.

Pada langkah terakhir dari tutorial, Lookup_values ​​akan dimasukkan ke dalam sel D3 dan E3 dari lembar kerja.

05 09

Menambahkan Lookup_array untuk Fungsi MATCH

Langkah ini mencakup penambahan argumen Lookup_array untuk fungsi MATCH yang bersarang.

Lookup_array adalah rentang sel yang fungsi MATCH akan mencari untuk menemukan argumen Lookup_value yang ditambahkan pada langkah sebelumnya dari tutorial.

Karena kami telah mengidentifikasi dua bidang pencarian dalam argumen Lookup_array, kami harus melakukan hal yang sama untuk Lookup_array . Fungsi MATCH hanya mencari satu array untuk setiap istilah yang ditentukan.

Untuk memasukkan beberapa array, kami kembali menggunakan ampersand " & " untuk menggabungkan array bersama-sama.

Langkah-langkah Tutorial

Langkah-langkah ini harus dimasukkan setelah koma dimasukkan pada langkah sebelumnya pada baris Row_num dalam kotak dialog fungsi INDEX.

1. Klik baris Row_num setelah koma untuk menempatkan titik penyisipan di akhir entri saat ini.
2. Sel sorotan D6 ke D11 di lembar kerja untuk memasukkan rentang. Ini adalah array pertama yang berfungsi untuk mencari.
3. Ketik ampersand " & " setelah referensi sel D6: D11 karena kami ingin fungsi untuk mencari dua larik.
4. Sorot sel E6 ke E11 di lembar kerja untuk memasukkan rentang. Ini adalah array kedua yang berfungsi untuk mencari.
5. Ketik koma "," setelah referensi sel E3 untuk menyelesaikan entri dari argumen Lookup_array fungsi MATCH.
6. Biarkan kotak dialog fungsi INDEX terbuka untuk langkah berikutnya dalam tutorial.

06 09

Menambahkan jenis Pencocokan dan Menyelesaikan Fungsi MATCH

Argumen ketiga dan terakhir dari fungsi MATCH adalah argumen Match_type.

Argumen ini memberi tahu Excel cara mencocokkan Lookup_value dengan nilai di Lookup_array. Pilihannya adalah: 1, 0, atau -1.

Argumen ini opsional. Jika dihilangkan, fungsi menggunakan nilai default 1.

• jika Match_type = 1 atau dihilangkan: MATCH menemukan nilai terbesar yang kurang dari atau sama dengan Lookup_value. Data Lookup_array harus diurutkan dalam urutan menaik.
• jika match_type = 0: MATCH menemukan nilai pertama yang persis sama dengan Lookup_value. Data Lookup_array dapat disortir dalam urutan apa pun.
• jika Match_type = -1: MATCH menemukan nilai terkecil yang lebih besar dari atau sama dengan Lookup_value. Data Lookup_array harus disortir dalam urutan menurun.

Langkah-langkah Tutorial

Langkah-langkah ini harus dimasukkan setelah koma dimasukkan pada langkah sebelumnya pada baris Row_num dalam kotak dialog fungsi INDEX.

1. Mengikuti koma pada baris Row_num , ketikkan nol " 0 " karena kita ingin fungsi yang disarangkan untuk mengembalikan pencocokan tepat ke istilah yang kita masukkan dalam sel D3 dan E3.
2. Ketikkan tanda kurung tutup putaran " ) " untuk melengkapi fungsi MATCH.
3. Biarkan kotak dialog fungsi INDEX terbuka untuk langkah berikutnya dalam tutorial.

07 09

Kembali ke Fungsi INDEX

Sekarang setelah fungsi MATCH selesai, kita akan pindah ke baris ketiga dari kotak dialog yang terbuka dan masukkan argumen terakhir untuk fungsi INDEX.

Argumen ketiga dan terakhir ini adalah argumen Column_num yang memberi tahu Excel nomor kolom dalam rentang D6 hingga F11 di mana ia akan menemukan informasi yang kita inginkan dikembalikan oleh fungsi tersebut. Dalam hal ini, pemasok untuk widget titanium .

Langkah-langkah Tutorial

1. Klik pada baris Column_num di kotak dialog.
2. Masukkan angka tiga " 3 " (tanpa tanda kutip) pada baris ini karena kami mencari data di kolom ketiga rentang D6 hingga F11.
3. Jangan Klik OK atau tutup kotak dialog fungsi INDEX. Ini harus tetap terbuka untuk langkah selanjutnya dalam tutorial - membuat rumus array .

08 09

Membuat Rumus Array

Sebelum menutup kotak dialog kita perlu mengubah fungsi nested kita menjadi rumus array .

Formula array adalah apa yang memungkinkannya untuk mencari beberapa istilah dalam tabel data. Dalam tutorial ini kami ingin mencocokkan dua istilah: Widget dari kolom 1 dan titanium dari kolom 2.

Membuat rumus array di Excel dilakukan dengan menekan tombol CTRL , SHIFT , dan ENTER pada keyboard secara bersamaan.

Efek menekan tombol-tombol ini bersama-sama adalah untuk mengelilingi fungsi dengan kurung kurawal: {} menunjukkan bahwa itu sekarang menjadi rumus array.

Langkah-langkah Tutorial

1. Dengan kotak dialog yang telah selesai masih terbuka dari langkah sebelumnya dari tutorial ini, tekan dan tahan tombol CTRL dan SHIFT pada keyboard lalu tekan dan lepaskan tombol ENTER .
2. Jika dilakukan dengan benar, kotak dialog akan tertutup dan kesalahan # N / A akan muncul di sel F3 - sel tempat kita memasuki fungsi.
3. Kesalahan # N / A muncul di sel F3 karena sel D3 dan E3 kosong. D3 dan E3 adalah sel tempat kami memberi tahu fungsi untuk menemukan Lookup_values ​​pada langkah 5 dari tutorial. Setelah data ditambahkan ke dua sel ini, kesalahan akan digantikan oleh informasi dari database .

09 09

Menambahkan Kriteria Pencarian

Langkah terakhir dalam tutorial adalah menambahkan istilah pencarian ke lembar kerja kami.

Seperti yang disebutkan pada langkah sebelumnya, kami ingin mencocokkan istilah Widget dari kolom 1 dan Titanium dari kolom 2.

Jika, dan hanya jika, rumus kami menemukan kecocokan untuk kedua istilah dalam kolom yang sesuai dalam basis data, akankah ia mengembalikan nilai dari kolom ketiga.

Langkah-langkah Tutorial

1. Klik pada sel D3.
2. Ketik Widget dan tekan tombol Enter pada keyboard.
3. Klik pada sel E3.
4. Ketik Titanium dan tekan tombol Enter pada keyboard.
5. Nama pemasok Widgets Inc. akan muncul di sel F3 - lokasi fungsi karena merupakan satu-satunya pemasok yang terdaftar yang menjual Titanium Widgets.
6. Ketika Anda mengklik pada sel F3 fungsi lengkap
   {= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   muncul di bilah rumus di atas lembar kerja .

Catatan: Dalam contoh kami hanya ada satu pemasok untuk widget titanium. Jika ada lebih dari satu pemasok, pemasok yang tercantum pertama dalam database dikembalikan oleh fungsi.