XL-mania adalah komunitas yahoogroups Microsoft Excel terbesar di dunia. Melalui XL-mania kita dapat belajar Excel bersama. Fungsi yang dipelajari dari fungsi keuangan, tanggal dan waktu, matematika, statistik, kondisi, lookup, referensi, database, logika, teks, informasi, serta cara membuat user defined function. Selain itu, di XL-mania juga dibahas mengenai penggabungan beberapa fungsi Excel menjadi sebuah formula yang unik, array, dan megaformula. Fitur-fitur Excel yang dibahas juga luas, dari conditional formatting, goal seek, chart, pivot table, macro / VBA, business model, financial modeling, solver, dan lain-lain. Dapatkan tips dan trik excel gratis dari XL-mania! Ingat, malu bertanya, kerja manual!

August 13, 2009

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]

13 comments:

  1. Aq tertarik situs ini dari kakakQ. Emang situs ini keren banget. Nyari situs yg keren kayak gini tapi yg mbahas khusus MS Access ada nggak yah?

    Bang owner, mo request neh. Tolong bahas masalah penggunaan form donk. Trims udah baca commentQ :) .

    ReplyDelete
  2. ok tuh penjelasannya...

    Kalo boleh tanya...

    Bisa gak ajarin saya untuk ngelookup data2 dari ms.access ke Excel?

    jadi kita udah punya database neh.. nah karena databasenya gede kalo diimport ke excel kan bakal pusingin..

    bisa gak kalo kita dari excel lookup langsung ke access..

    tolong penjelasan detailnya.. masih pemula di excel neh pak..

    terima kasih

    salam, theo

    ReplyDelete
  3. Aye lagi mencari VLOOKUP DENGAN KATA KUNCI. nemu di sini. pas bener....

    ReplyDelete
  4. mau txa2 nie,,,
    aq baru aza megang excel,,jd krng tau,,,apa itu rmus2 excel,,,mklm,,,he,

    gimana ya mas kalo mau ngelingkan dri file 1 ke file satunya lg,,,misal pembelian apel 1 kotak,,dan supaya ga repot infut/copy lg,,,ada ga rumus na ga ya,,,mf klo kata2nya amburadur,,baru2 juga ne gabung

    ReplyDelete
  5. wah mantap nih...
    tapi mas abimono...
    kalo mau tampilin dari satu data dengan banyak hasil bisa ga yah???

    mohon pencerahannya :)
    saya sudah pernah email ke group tapi lum ada yang jawab satupun :(

    ReplyDelete
  6. Bagaimana untuk melist semua data yang terdapat kata apel ?
    Misal: ada beberapa jenis buah2an diantaranya ada pisang ambon, pisang raja, pisang kepok, apel malang, apel bangkok, durian medan, durian montong. Kemudian saya ingin menampilkan semua buah pisang. Bagaimana caranya supaya pisang tersebut bisa tampak semuanya?

    terima kasih

    ReplyDelete
  7. dear Pak Abimono,
    Bagaimana kalau kita mau menjumlah dengan sebagian kata kunci dan beberapa kriteria??

    misalnya:
    minyak goreng filma 1000
    kertas minyak filma 200
    ayam minyak kfc 5000
    minyak zaitun kfc 2000

    nah pak, bagaimana kalau saya mau jumlahkan totalkan yang mengandung kata minyak, tapi yang filma saja..

    Terima kasih pak..

    ilmu bapak benar2 hebat

    ReplyDelete
  8. Salut buat yang punya situs ini, dan salam kenal...karena anda mau menjadi pencerah bagi yang membutuhkan. Banyak orang susah berbagi ilmu, tapi anda malah memberikannya secara gratis dan cuma-cuma. Teruskan berbagi ilmunya .... karena membuat orang lain tercerahkan adalah merupakan kepuasan batin yang tidak dapat di nilai dengan uang .......

    ReplyDelete
  9. To Master2 XL lam knal... & mo mnt tlg formula to tabel d bwh ini
    A B C D E F

    50 51 52 53 54
    1 30 0,98620 0,98610 0,98600 0,98590 0,98580
    2 31 0,98630 0,98620 0,98610 0,98600 0,98590
    3 32 0,98640 0,98630 0,98620 0,98610 0,98600
    4 33 0,98650 0,98640 0,98630 0,98620 0,98610
    5 34 0,98660 0,98650 0,98640 0,98630 0,98620
    yg Sy g tau formula seandainya angka decimal contoh 32,5;54,2. Pls ya... Suwun very much
    Aryo, Semarang

    ReplyDelete
  10. blenk tentang XL

    ReplyDelete
  11. Apakah fungsi SEARCH bisa dikombinasikan dengan fungsi logika seperti misalnya IF sebagai bagian dari kondisi yang menjadi syarat fungsi IF tersebut?

    ReplyDelete

Silahkan berkomentar tentang XL-mania...