Kami memiliki daftar pesanan dengan nomor dan nama barang. Saya ingin, misalnya, menarik keluar dari meja dengan nomor pesanan semua barang yang termasuk di dalamnya. Kurang lebih seperti ini:
Fitur luar biasa VLOOKUP (VLOOKUP) dalam situasi seperti itu hanya akan membantu sebagian, karena mampu mengekstrak data hanya dengan kecocokan pertama yang ditemukan, yaitu hanya akan memberi kita Apel. Untuk menemukan dan mengekstrak semua item dari tabel, lebih baik menggunakan rumus array. Seperti yang ini:
=INDEKS($B$2:$B$16;PALING SEDIKIT(IF($ E $ 2=A2: A16;GARIS(B2:B16)-1;»»);GARIS()-5))
Itu harus dimasukkan sebagai berikut:
- pilih sel tempat hasilnya akan ditampilkan (dalam contoh kami, ini adalah rentang D6:D20)
- masukkan (salin rumus ke sel pertama) dari rentang
- pers Ctrl + perubahan + Enter
Pengurangan satuan dalam fragmen STRING(B2:B16)-1 dilakukan karena header tabel. Untuk alasan yang sama, untuk mengimbangi pergeseran dalam rentang yang dihasilkan relatif terhadap yang asli, angka lima dalam fragmen dikurangi STRING()-5
Untuk menyembunyikan #NUM! kesalahan yang akan muncul di sel kosong dalam rentang yang dihasilkan D6: D20, Anda dapat menggunakan fungsi pemeriksaan kesalahan IF dan EOSH, mengganti rumus kami dengan yang sedikit lebih kompleks:
=JIKA(EOSH(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)));»»;ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)))
Di Excel 2007, fungsi IFERROR yang lebih nyaman muncul – ini memungkinkan Anda untuk menyelesaikan masalah dengan lebih ringkas:
=KESALAHAN(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5));»»)
PS
Dalam versi bahasa Inggris Excel, fungsi-fungsi ini akan terlihat seperti ini:
=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))
=IF(ISERR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))),»»,INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)))
=IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)),»»)
- Menggunakan Fungsi VLOOKUP untuk Menemukan Data dalam Tabel
- Versi yang disempurnakan dari fungsi VLOOKUP2 yang dapat mencari di kolom mana pun dan bukan hanya nilai pertama
- Fungsi VLOOKUP2 dan VLOOKUP3 dari add-on PLEX
- Apa rumus array dan kegunaannya?