RUMUS VLOOKUP DENGAN 2 TABEL ARRAY DATA
Cara menggunakan rumus Vlookup Excel dengan dua tabel referensi
atau lebih untuk memilih tabel array yang tepat secara otomatis.
Bagaimanakah cara
menggunakan fungsi atau rumus VLookup dengan 2 tabel data referensi yang
berbeda?
Seperti yang sudah
kita pelajari pada pembahasan tentang fungsi Vlookup Excel, bahwa fungsi
Vlookup hanya bisa digunakan untuk melakukan pencarian data pada 1 tabel
referensi saja.
Jika kita memiliki 2 Tabel data array yang mirip, bagaimana
cara agar rumus Vlookup bisa memilih tabel data yang
tepat sesuai kriteria yang kita kehendaki?
Misalnya Anda memiliki
data dengan 2 tabel seperti di bawah ini. Bagaimanakah rumus VLookup yang
digunakan untuk mendapatkan nilai Jumlah sesuai dengan tahun dan kwartal yang
kita pilih.
Secara umum sintaks
fungsi VLookup adalah sebagai berikut:
VLOOKUP(NilaiYangDiCari; TabelReferensi; NomorKolom; [RangeLookup])
Pada kasus Vlookup dengan dua tabel data array
seperti diatas argument fungsi VLookup yang perlu kita buat otomatis
menyesuaikan dengan kriteria adalah argument TabelReferensi.
Jika menginginkan data tahun 2015 maka Vlookup
menggunakan tabel data dengan referensi A2:E6. Sedangkan jika mengharapkan jumlah pada
tahun 2016 maka fungsi Vlookup harus menggunakan tabel referensi A9:E13.
Sedangkan untuk argument NilaiYangDiCari sama, yakni data Jumlah pada tahun yang
bersangkutan yang bisa didapatkan pada kolom no 5 dari tabel referensi.
Untuk mendapatkan
hasil sesuai 2 kriteria diatas maka setidaknya kita bisa menggunakan 2 rumus
excel yang berbeda.
Yang pertama dengan menggunakan gabungan fungsi IF dengan fungsi VLookup excel. Dan yang kedua dengan menggunakan
gabungan fungsi VLookup dan fungsi Choose.
Adapun untuk VLookup pada tabel yang sama
dengan multi kriteria bisa anda pelajari .
RUMUS GABUNGAN FUNGSI IF DAN VLOOKUP EXCEL
Sintaks umum fungsi IF
excel adalah sebagai berikut:
IF(TesLogika; [KondisiTerpenuhi]; [KondisiTidakTerpenuhi])
Penjelasan lebih detail tentang fungsi IF
excel ini bisa anda baca pada tutorial excel berikut:
Rumus If-Vlookup
Untuk mendapatkan data
Jumlah dari sintaks atau cara penulisan diatas maka jika digabungkan dengan
fungsi VLookup rumus excelnya akan menjadi:
IF(TesLogika;
VLookup_1; VLookup_2)
Dimana VLookup_1
merupakan rumus Vlookup untuk tabel pertama dan VLookup_2 merupakan rumus
Vlookup untuk tabel kedua.
Untuk contoh kasus
diatas maka rumus excel yang digunakan pada cell B17 adalah sebagai berikut:
=IF(B15=2015;VLOOKUP(B16;A2:E6;5;FALSE);VLOOKUP(B16;A9:E13;5;FALSE))
Dengan rumus diatas
jika B15 bernilai angka 2015 maka yang akan dipakai adalah rumus Vlookup_1
yakni:
VLOOKUP(B16;A2:E6;5;FALSE)
Dan sebaliknya jika
tidak bernilai 2015 maka fungsi IF akan menghasilkan nilai dari rumus Vlookup
berikut:
VLOOKUP(B16;A9:E13;5;FALSE)
Rumus Vlookup-If
Selain memasukkan
rumus Vlookup kedalam rumus IF seperti diatas, kasus ini bisa juga diselesaikan
dengan memasukkan rumus IF ke dalam rumus Vlookup.
=VLOOKUP(NilaiYangDiCari; IF(TestLogika;TabelReferensi1;TabelReferensi2); NomorKolom; [RangeLookup])
Dari sitaks di atas
jika evaluasi logika pada fungsi IF bernilai True maka Vlookup akan menggunakan
TabelReferensi1 dan sebliknya jika False maka Vlookup akan menggunakan
TabelReferensi2.
Untuk contoh seperti sebelumnya
rumus gabungan Vlookup-If nya adalah seperti berikut:
=VLOOKUP(B16;IF(B15=2015;A2:E6;A9:E13);5;FALSE)
Pada kasus ini nilai A2:E6 dan A9:E13, secara otomatis langsung dikenali sebagai
referensi Range oleh fungsi Vlookup. Pada fungsi lain mungkin hal ini tidak
terjadi dan malah menghasilkan nilai error sehingga anda membutuhkan fungsi INDIRECT untuk membuat referensi dari teks.
Jika menggunakan
fungsi Indirect, maka rumus di atas menjadi seperti ini:
=VLOOKUP(B16;IF(B15=2015;INDIRECT("A2:E6");INDIRECT("A9:E13"));5;FALSE)
RUMUS GABUNGAN FUNGSI VLOOKUP DAN CHOOSE EXCEL
Selain menggunakan
rumus excel gabungan seperti diatas kita bisa juga menggunakan fungsi CHOOSE
excel untuk memilih tabel data yang digunakan oleh fungsi VLookup.
Sintak fungsi Choose sendiri adalah sebagai berikut:
CHOOSE(IndexNumber; Nilai1; [Nilai2]; ...)
Jika dimasukkan pada
fungsi Vlookup maka rumus excelnya menjadi:
VLOOKUP(NilaiYangDiCari; CHOOSE(IndexNumber; Nilai1; [Nilai2]; ...); NomorKolom; [RangeLookup])
Adapun untuk menentukan index number kita menggunakan fungsi logika IF.
Jika diterapkan pada
kasus diatas maka rumus excel yang digunakan pada cell B15 adalah:
=VLOOKUP(B16;CHOOSE(IF(B15=2015;1;2);A2:E6;A9:E13);5;FALSE)
Pada rumus diatas
tabel referensi yang digunakan oleh VLookup ditentukan oleh referensi yang
dihasilkan oleh fungsi Choose.
Sedangkan fungsi Choose sendiri menggunakan hasil dari fungsi IF untuk menentukan nilai referensi range mana yang akan dihasilkan.
CHOOSE(IF(B15=2015;1;2);A2:E6;A9:E13)
Jika B15 bernilai 2015 maka Choose akan
memilih referensi ke-1 yang diberikan yakni range A2:E6, dan sebaliknya jika bukan 2015 maka akan
memilih referesi ke-2 yaitu range A9:E13.
Nah cukup mudah bukan?
Jika anda kreatif, saya kira masih banyak solusi lainnya. Hanya saja 2 rumus
exel diatas saya kira sudah cukup untuk saat ini.
© Kelas Excel |
sumber: https://www.kelasexcel.id/2017/05/rumus-vlookup-dengan-2-tabel.html