Rumus excel match dan index

I cannot imagine how it can be that the formula returns zeros in cells that have no data; for me it returns the #N/A error in this case. Anyway, this can be easily fixed by wrapping your formula in the IFERROR function, so that it returns an empty string (blank cell) when no match is found:

=IFERROR(INDEX('B1'!$H$2:$H$3099,MATCH(C18,'B1'!$B$2:$B$3099,0)), "")

Also, I'd suggest that you stick with the original formula that uses a single cell for the lookup value (C18). Using a range may cause very frustrating issues (#SPILL error) when migrating to Excel 365 or Excel 2021.

Double-clicking the plus sign works as long as there is some data in the adjacent column(s). As soon as an empty row occurs, the auto fill stops. In this case, you can copy the formula down by dragging the fill handle (a small square at the lower right-hand corner of the cell). For more information, please see How to copy formula in Excel.

Anda pernah melakukan pencarian data dari suatu tabel ke tabel yang lain? Biasannya pengguna excel akan melakukannya dengan menggunakan Fungsi VLOOKUP atau Fungsi HLOOKUP. Akan tetapi kedua fungsi lookup ini mempunyai kelemahan yaitu hanya mampu membaca tabel array ke arah kanan atau kebawah saja. Bagaiamana agar Pencarian data tersebut fleksibel, dalam arti dapat mencari dan mengambil data dari kolom ataupun baris manapun yang kita tentukan, Fungsi INDEX MATCH adalah Solusinya.

Kenapa Menggunakan Fungsi INDEX MATCH?

Pada umumnya penggunaan Fungsi INDEX MATCH adalah untuk menanggulangi kelemahan dari fungsi VLOOKUP atau HLOOKUP. Hal ini disebabkan kedua fungsi LOOKUP tersebut hanya mampu membaca tabel array ke arah kanan atau kebawah. Sedangkan dalam dunia kerja, tabel array mempunyai bentuk yang elastis, tidak bisa diprediksi bahwa Cell yang menjadi acuan berada dikiri, kanan, bawah atau atas. untuk itu dengan menggunakan fungsi INDEX MATCH kekurangan tersebut dapat teratasi.

Fungsi INDEX

Pada dasarnya Fungsi INDEX MATCH adalah dua fungsi yang berbeda yang mempunyai perhitungan dan argumen berbeda pula. Fungsi INDEX adalah fungsi yang cukup sederhana, digunakan untuk mendapatkan nilai dari suatu cell berdasarkan pencarian pada suatu definisi table / data range worksheet kita.
metode pencariannya berdasarkan informasi posisi kolom dan baris, dengan acuan berupa kolom dan baris pertama table / data range tersebut.

Syntax =INDEX(array,row_num,Column_num)

Keterangan :

• Array: Range data yang akan diambil nilainya.
• Row_num: pergerakan nomor baris.
• Col_num : Pergerakan nomor kolom.

Contoh Kasus : Kita diminta untuk mengindex data di kolom jenis produk yang akan di ambil dari tabel dibawahnya menggunakan fungsi INDEX.

Penyelesaian :

1. Letakan Pointer pada Cell D13
2. Masukan Formula / rumus

   =INDEX($B$22:$D$25,2,1)

3. Tekan Enter
4. Hasilnya adalah “Makanan“

Catatan : Pada fungsi Index untuk mengisi argumen Row_number dan Col_Number harus manual.

Fungsi MATCH

Fungsi MATCH adalah fungsi yang digunakan untuk mencari suatu nilai dari suatu range yang terdapat pada suatu kolom atau baris, tapi tidak kedua-duanya. hasil dari fungsi ini adalah bernilai nomor.

Syntax  =MATCH(lookup_value, lookup_array, [match_type])

Keterangan :

• Lookup_value : adalah nilai acuan yang ingin dicari pada lookup_array.
• Lookup_array : adalah range data dari suatu kolom ataupun baris.
• Match_type :  adalah angka yang menunjukkan tipe pencocokan sebagai berikut :
  *angka 1 : jenis pencocokan dimana lookup_array harus dalam keadaan terurut secara ascending (kecil ke besar). Pencocokan dilakukan dengan mengambil nilai terbesar dari range data yang lebih kecil atau sama dari lookup_value.
  *angka 0 : jenis pencocokan dimana pada lookup_array dicari data yang sama persis dengan lookup_value. Urutan data tidak menjadi masalah. Jika diketemukan lebih dari satu data yang sama, maka akan diambil data yang pertama kali diketemukan secara sekuensial.
  *angka -1 : -1 :  jenis pencocokan dimana lookup_array harus dalam keadaan terurut secara descending (besar ke kecil).  Pencocokan dilakukan dengan mengambil nilai terkecil dari range data yang lebih besar atau sama dari lookup_value.

Contoh Kasus :  Carilah nomor baris dari tabel data berikut ini.

Penyelesaian : 

1. Letakan Pointer pada Cell L14
2. Rumus / Formula berikut ini

   =MATCH(K14,$K$24:$K$27,1)

3. Tekan Enter
4. Hasilnya adalah Nompr Baris ke “2”

Catatan : Blok data Lookup_array hanya berupa Range Baris atau Range kolom saja. fungsi match mengembalikan nilai nomor baris atau kolom.

Penggabungan Fungsi INDEX MACTH

Dari kedua Fungsi yang sudah kita pelajari diatas dapat kita gabungkan sebagai satu kesatuan rumus untuk mencari data dan mengindek data secara dinamis dalam suatu tabel.

Syntax =INDEX(array,MATCH(lookup_value, lookup_array, [match_type]))

Keterangan : Dari syntax diatas Fungsi INDEX dijadikan sebagai fungsi induk, kemudian fungsi MATCH digunakan untuk menggantikan argumen Row_number dalam fungsi index.

Contoh Kasus : Dari tabel dibawah ini kita diminta untuk mengindex data “harga” dari tabel sumber disampingnya menggunakan fungsi index match.

Penyelesaian :

1. Letakan pointer pada cell F4
2. Tuliskan Rumus / Formula berikut ini :

   =INDEX($I$4:$I$7,MATCH(D4,$K$4:$K$7,0))

3. Tekan Enter
4. Hasilnya adalah “1.100.000”
5. Copy Formula untuk cell dibawahnya menggunakan autofill.

Penjelasan :

• Tanda $ menunjukkan bahwa array yang dijadikan acuan harus dikunci karena ingin dicopy ke celll bawahnya
  $I$4:$I$7 = menunjukkan array dari Price, yaitu kolom yang ingin dicari nilainya
• D4 = menunjukkan sel acuan yang menjadi penentu sebagai informasi pengisian
  kolom Price
• $K$4:$K$7 = menunjukkan array dari kolom product yang menjadi sel acuan
• 0 = menunjukkan tipe match yang dipilih yaitu tipe yang tepat dengan sel D4

Penggunaan fungsi index match diatas digunakan untuk data array vertikal, karena fungsi ini sangat dinamis anda juga dapat gunakan untuk bentuk data horisontal.

Rumus Index Match untuk apa?

Apa itu rumus indeks?

Rumus apa saja yang ada di Excel?

Rumus VLOOKUP dan hlookup untuk apa?