Contoh Kasus: Pencarian Multi Variable dengan Match dan Index

Kasus

Saya lagi cari rumus utk bisa mengembalikan nilai dari pencarian yang menggunakan 3 variabel.. misalnya saya punya sheet Daftar Harga Barang dgn kolom sbb:
  • Kolom 1 : Berupa Kode/ID
  • Kolom 2 : Berupa tipe konsumen (reseller/langsung)
  • Kolom 3 : Harga barang.
di sheet yang lain saya punya rincian transaksi penjualan yang disusun perhari. Pada sheet ini sy punya kolom:
  • Kolom 1: Tanggal
  • Kolom 2: Kode/ID
  • Kolom 3: Tipe konsumen
Nah saya menginginkan pada Kolom 4 dapat muncul harga barang tersebut utk Kode dan tipe konsumen yg sudah saya catat sebelumnya.

Bagaimana caranya? function apa yg perlu sy gunakan dan gimana rumusnya.

Jawab

Solusi untuk masalah ini adalah menggunakan array formula dari fungsi Index dan Match. Untuk lebih jelasnya kita akan mencoba dengan menggunakan file yang dapat Anda download di alamat Disini.

File Excel ini memiliki sheet data dan master_harga. Klik pada tiap sheet tab untuk mempelajari isinya.



Sekarang kita akan mengisi kolom Harga yang didapatkan dari pencarian multi kolom Kode / ID dan Tipe ke sheet master_harga.


Berikut adalah langkah-langkah untuk melakukan kombinasi pencarian tersebut :
  1. Buka file yang Anda download tersebut dengan Microsoft Excel 2007.
  2. Pada sheet data, klik cell pada alamat D2 dan masukkan rumus di bawah ini.

    =    INDEX(master_harga!$C$2:$C$11,    MATCH(B2&C2,master_harga!$A$2:$A$11&master_harga!$B$2:$B$11,0))

    Perhatikan bahwa kita menggunakan operator and ( & ) untuk menggabungkan cell B dan C - yaitu kolom Kode / ID dan Tipe.
  3. Tekan tombol CTRL+SHIFT+ENTER.
  4. Akan muncul nilai harga 9340, sesuai nilai yang kita cari. Perhatikan bahwa rumus yang kita masukkan diapit oleh kurung kurawal  {} yang menandakan bahwa formula tersebut adalah suatu array formula.
  5. Copy rumus tersebut ke baris di bawahnya yang masih kosong.

  6. Selesai.

Referensi

at
Labels: , , ,

15 comments:

  1. faybudiMarch 17, 2013 at 7:26 AM

    Trims sudah posting artikel atas pertanyaan saya dulu.

    Sudah saya coba dan tepat sekali memang rumus ini yang saya butuhkan. Thanks sharing nya.

    Fay

    ReplyDelete
  2. achmad FarizkyMarch 20, 2013 at 6:48 PM

    Terima kasih atas tutorial ini, sangat membantu sekali

    ReplyDelete
    Replies
    1. Tim ExcellentJuly 30, 2013 at 10:13 PM

      Sama2, semoga bermanfaat ya Pak. Jangan lupa follow kita ya di @BelajarExcel.

      Delete
  3. RinovMarch 25, 2013 at 2:03 PM

    Sebelumnya terima kasih atas tutornya, tp ada sedikit pertanyaan.... kira-kira bisa tidak atau bagaimana rumusnya untuk Multi Variable (INDEX & MATCH) bisa menjumlahkan apabila ada 2 range dan kategori yang sama. sejauh ini sy coba untuk fungsi INDEX & MATCH ini hanya bisa mengambil 1 sum range (anggap saja harga yang sy maksud).

    ReplyDelete
    Replies
    1. Tim ExcellentApril 15, 2013 at 1:05 AM

      Pak Rinov, maaf baru balas. Apakah ada contoh datanya dan dishare disini Pak ? Biar bisa kita bantu dengan lebih konkrit. Terima kasih.

      Delete
  4. UnknownApril 16, 2013 at 5:43 PM

    wah sangat membantu saya,, terima kasih

    ReplyDelete
  5. UnknownJuly 30, 2013 at 9:03 AM

    bagus sekali tutorialnya.. sangat membantu..
    ada satu pertanyaan lagi, seandainya dalam contoh diatas ditambahkan 1 atau lebih kriteria lagi apakah bisa menggunakan rumus yg sama? (saya coba tambah & lg tp hasilnya blm benar)
    terima kasih.

    Eriq

    ReplyDelete
    Replies
    1. Tim ExcellentJuly 30, 2013 at 10:28 PM

      Pak Eriq, benar sekali. Ternyata perlu menambahkan parameter ketiga pada match dengan angka 0. Saya edit artikelnya. Dan untuk contoh 3 variable bisa dilihat dan didownload di sini. Semoga bermanfaat.

      Delete
    2. UnknownJuly 31, 2013 at 8:07 AM

      mantap.. terima kasih atas bantuanya..

      Delete
    3. Tim ExcellentAugust 1, 2013 at 9:37 AM

      Siap, sama2 Pak. Semoga bermanfaat.

      Delete
  6. ardiAugust 14, 2013 at 3:23 PM

    Mas, mau tanya dari case diatas.
    itukan mencari harga dari KODE/ID dan TIPE yang tersusun secara vertikal (tersusun kebawah..), bisa ga bila urutannya menjadi horizontal..

    misal list KODE/ID tersususn vertikal sedangkan TIPE tersusun horizontal dan HARGAnya ada dua macam sesuai TIPEnya (lansung&reseller).

    terima kasih atas pencerahannya, saya coba ga bisa..

    ReplyDelete
  7. UnknownMarch 3, 2014 at 3:11 PM

    bapak, saya mau tanya, contoh diatas tersebut adalah apabila database dan rumus index n match berada di dalam satu file. nah, yg mau saya tanyakan adalah apabila database dan rumus nya berbeda file apakah ada tambahan rumusan lain? karena telah saya coba pertama memang berhasil, sukses. akan tetapi setelah saya tutup file2 tsb, dan saya buka lg setelah ya kira2 1 minggu, hasil rumusnya menjadi N/A smua. mohon bantuannya bapak. terima kasih

    ReplyDelete
  8. Keshia NathaniaMay 16, 2014 at 8:53 AM

    Saya ingin menghitung komisi utk sales dg komposisi total tagihan bl pembyrn <=1bl,diskon <31%,komisinya adlh 1%. Utk pmbyrn 1,5bl-2bl komisi 0,75%. Bgm rumus yg hrs sy masukkan ke cell. Terimakasih

    ReplyDelete
    Replies
    1. Just Another BloggerMay 16, 2014 at 9:02 AM

      Hi Keshia, gabung ke user groupnya yuk... biar lebih banyak yang kasih masukan :)

      https://www.facebook.com/groups/belajarexcelinfo/

      Terima kasih ya... :)

      Delete

Subscribe to: Post Comments (Atom)

Launching E-BOOK EIUG: Form Entry Sederhana dengan Excel VBA

Pengunjung BelajarExcel.info Yang Saya Hormati, Pada tanggal 14 Juni 2014,Excel Indonesia User Group (EIUG) yang merupakan salah satu k...