lookup data dengan sebagian kata kunci

Pertanyaan:

"Hai oom abimono, saya di kantor sering banget tuh harus lookup data dari tabel lain yang dibuat oleh orang lain. Masalahnya, tabel referensinya sering kacau. Misalkan harusnya nama itemnya adalah 'apel malang', yang sering dijadikan referensi adalah 'apel' saja. Bulan depannya boro-boro sama apel malang. Bisa jadi ditulisnya 'apel malanggg'. Nah, akhirnya yang dipakai keywordnya aja deh, yaitu 'apel'. Gimana dong caranya? Kayaknya sih kalau pakai vlookup kata kuncinya harus sama kan ya? Bingung nih. Tolong dibantu ya oom. Pecas ndahe." - Gadis penikmat "hujan meteor", Jakarta.

Jawab:
Hai penikmat hujan meteor, langsung saja ya, lookup dengan kata kuci tentu saja bisa, tapi dengan syarat bahwa satu kata kunci hanya boleh merefer ke satu kata juga di file tujuan. Kalau tidak hasilnya tidak sesuai. Contohnya pada kasus di atas, kata-kata yang berwarna merah di kolom B adalah kata kuncinya. Yang dicari adalah harga di kolom C dengan kata kunci di kolom E. Rumus yang digunakan di F3 adalah:

=LOOKUP(9^9,SEARCH(E3,$B$3:$B$12),$C$3:$C$12), kemudian copy ke range F4 sampai F12.

Penjelasan:

1. ...SEARCH(E3,$B$3:$B$12)... berfungsi untuk mencari apakah kata kunci ada di salah satu dari array yang dicari, dalam hal ini B3:B12. Jika ada dia akan memberikan angka.
2. =LOOKUP(9^9, [hasil search], ...) akan menunjukkan letak cell yang memberikan angka terdekat dengan 9^9. Dalam hal ini, jika kata kunci yang dicari adalah unik, maka baris dengan kata kunci itu adalah satu-satunya yang memberi hasil angka akibat fungsi SEARCH, sisanya akan memberikan hasil #VALUE!.
3. angka pada langkah kedua ini akan menunjukkan satu posisi pada array tujuan. Dengan formula =LOOKUP( [posisi yang ditunjuk], [array tujuan] ), maka akan diambil salah satu anggota array tujuan.

File:

Anda dapat mendownload materi tips ini di sini

[harus memiliki yahoo id dan mengikuti mailing list XL-mania, tempat tips dan trik excel terlengkap]

menghilangkan tanda #N/A hasil lookup

Pertanyaan:

"Gimana caranya menghilangkan tanda NA() hasil vlookup?" - Enda Nasution, dunia maya.

Jawab:
Dear XL-maniawan dan XL-maniawati, saya coba jawab pertanyaan Anda di sini satu per satu, tapi harap bersabar ya, kalau mau cepat, bisa ditanya langsung ke milis :)

Hai Enda Nasution, menjawab pertanyaan di sini, saya koreksi sedikit kalau boleh, harusnya #N/A, bukan N/A( ). Sebenarnya tanda #N/A itu tidak salah apa-apa, karena artinya "not available". Tapi banyak orang yang ingin menghilangkannya, bahasa Jawa nya "error handler". Caranya cukup mudah, yaitu dengan menuliskan rumus aslinya ke dalam template:

=IF(ISNA( [rumus aslinya] ),"", [rumus aslinya]) di cell F3, kemudian mengcopynya ke range F4 sampai F8. Untuk kondisi lain, misalkan yang ingin dihilangkan adalah error jenis lain seperti #DIV/0!, #NAME?, #NUM!, dan lain-lain, ISNA dapat diganti dengan ISERROR. Begitu pula tanda "" dapat diganti dengan bentuk lain seperti "data tidak ada"

Penjelasan:

1. ...ISNA(VLOOKUP(E3,$B$3:$C$8,2,FALSE))... berfungsi untuk mencari apakah rumus vlookup menghasilkan #N/A.
2. =IF(kondisi, hasil 1, hasil 2) berfungsi untuk memberikan hasil 1 jika nilai kondisi adalah TRUE, pada kasus ini jika nilai dalam ISNA( ) adalah #N/A, maka hasilnya adalah TRUE.

File:

Anda dapat mendownload materi tips ini di sini

[harus memiliki yahoo id dan mengikuti mailing list XL-mania, tempat materi belajar excel terlengkap]