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 http://goo.gl/VSZL9.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

BelajarExcel.info

20 Comments

  1. Trims sudah posting artikel atas pertanyaan saya dulu.

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

    Fay

  2. 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).

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

  3. 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

  4. 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..

  5. 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

  6. 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

  7. om.. saya mau tanya gmna cara/rumusnya untuk menentukan angka tertinggi pertama, kedua, dan seterusnya. dengan menggunakan index, match.

  8. bantu sy untuk mencari rumus atau formula untuk mencari angka3terbesar dalam tabel data dan namanya.tk

  9. Ni sudah saya cari di mbah google, tapi ga ketemu, ni mas kalau bisa berarti mas hebat, saya menggabungkan berbagai kalimat dengan &, tapi ternyata format teks (bold) berubah. gimana ya mas agar teks tidak berubah formatnya. dan yang lain tetap tidak blod.
    kalau pakai conditional formating semua malah menjadi bold. gimana mas ? tolong ya..

Leave a Reply

Your email address will not be published. Required fields are marked *