Rumus VLOOKUP dengan dua Table Array di Excel
Daftar Isi
Urutannya adalah jika data yang dicari tidak ada di tabel referensi satu, maka data akan dicari di tabel referensi 2 dan jika tidak ditemukan maka hasil yang di tampilkan adalah #N/A atau kosong.
Untuk lebih memahaminya kita akan berlatih dengan contoh data seperti berikut ini:
[ Contoh File Excelnya bisa di unduh disini ]
Pada gambar diatas karena pada sel D13 dimasukkan angka 8 maka pada sel D14 hasilnya Monica dan sel D15 hasilnya Jakarta, data tersebut diambil dari Data Tabel Referensi 2 karena angka 8 ada di Tabel Referensi 2, dan jika pada sel D13 dimasukkan angka 1 s.d 5 maka data yang ditampilkan adalah data dari Tabel Referensi 1.
Untuk membuat VLOOKUP dengan 2 Tabel Referensi caranya adalah sebagai berikut:
VLOOKUP dg 2 Table Array di Excel 2013
Untuk membuat rumus VLOOKUP dengan 2 Tabel Array di Excel 2013 caranya sangat mudah yaitu dengan manfaatkan fungsi IFNA penulisannya adalah sebagai berikut:
=IFNA(Rumus_Vlookup_1;IFNA(Rumus_Vlookup_2);""))
Keterangan:Rumus_Vlookup_1 : rumus VLOOKUP dengan table_array dari Tabel Referensi 1
Rumus_Vlookup_2 : rumus VLOOKUP dengan table_array dari Tabel Referensi 2
Jika diterapkan pada tabel contoh seperti gambar diatas, maka rumus pada sel D14 adalah:
=IFNA(VLOOKUP(D13;$B$5:$D$9;2;FALSE);IFNA(VLOOKUP(D13;$F$5:$H$9;2;FALSE);""))
Sedangkan untuk rumus pada sel D15 tinggal mengubah col_index_num-nya dari 2 menjadi 3.
VLOOKUP dg 2 Table Array di Excel 2010 ke bawah
Seperti yang sudah saya terangkan sebelumnya pada artikel 3 cara menghilangkan #N/A pada fungsi VLOOKUP Excel bahwa fungsi IFNA hanya terdapat di Excel 2013, karena itu jika rumus diatas tadi diterapkan di Excel 2010 ke bawah ( 2007, 2003) hasilnya akan error dan rumusnya berubah menjadi seperti ini:
=_xlfn.IFNA(VLOOKUP(D13;$B$5:$D$9;2;FALSE) dan seterusnya
untuk masalah tersebut kita bisa menggunakan rumus lain untuk membuat rumus VLOOKUP dengan dua Table Array yaitu dengan penggabungan fungsi IF dan ISNA serta IFERROR atau ISERROR (untuk Excel 2003)..Penulisan rumusnya adalah sebagai berikut:
=IFERROR(IF(ISNA(Rumus_Vlookup_1);Rumus_Vlookup_2;Rumus_Vlookup_1);"")
Sehingga jika diterapkan pada contoh tabel pada gambar diatas yaitu pada sel D14 maka rumusnya seperti ini:
=IFERROR(IF(ISNA(VLOOKUP(D13;$B$5:$D$9;2;FALSE));VLOOKUP(D13;$F$5:$H$9;2;FALSE);VLOOKUP(D13;$B$5:$D$9;2;FALSE));"")
Dan untuk rumus pada sel D15 tinggal mengubah col_index_num-nya dari 2 menjadi 3.
Lampiran
VLOOKUP 2 Tabel Array
Excel Workbook (xlsx)
Update | Menggunakan Excel 365
Karena di Excel 365 ada Fungsi baru untuk menggabungkan tabel atau array, maka saya update lagi artikel ini dengan alternatif fungsi lain yang dapat digunakan untuk masalah ini.
Bagi Anda yang menggunakan Excel office 365, Anda dapat menggunakan gabungan fungsi VLOOKUP dan VSTACK, meski di Excel 365 ada Fungsi XLOOKUP sebagai pengganti fungsi VLOOKUP, namun karena tema pembahasan ini awalnya menggunakan Fungsi VLOOKUP jadi pada contoh solusinya tetap menggunakan VLOOKUP.
Fungsi VSTACK adalah salah satu fungsi baru di Excel 365, fungsi VSTACK dapat digunakan untuk menggabungkan dua atau lebih larik (array) / tabel menjadi satu larik / tabel baru secara vertikal.
Contoh penggunaan Fungsi VLOOKUP + VSTACK adalah sebagai berikut:
Pada Sel D14 dimasukkan rumus excel:
=IFNA(VLOOKUP(D13;VSTACK(B5:D9;F5:H9);2;FALSE);"")
Sedangkan untuk sel D15 rumusnya adalah:
=IFNA(VLOOKUP(D13;VSTACK(B5:D9;F5:H9);3;FALSE);"")
Dengan menggunakan Fungsi VSTACK ini, penulisan rumusnya jadi lebih singkat, karena jika ditampilkan hasil dari rumus excel VSTACK(B5:D9;F5:H9) adalah seperti terlihat pada gambar di bawah ini:
Karena sudah berbentuk satu tabel data, maka untuk mengolahnya cukup menggunakan satu Fungsi VLOOKUP saja.
Begitulah cara membuat rumus VLOOKUP dengan dua Table Array di Excel, jika anda ingin mencari dari 3 table array atau 4 table array dan seterusnya, silahkan dikembangkan rumusnya, kurang lebihnya hampir sama dengan VLOOKUP dua table array. Semoga Bermanfaat...
terimakasih