VLOOKUP dengan Match

Rumus Vlookup hanya berfungsi ketika larik tabel dalam rumus tidak berubah, tetapi jika ada kolom baru yang disisipkan ke tabel atau kolom dihapus, rumus memberikan hasil yang salah atau mencerminkan kesalahan, untuk membuat rumus bebas dari kesalahan di situasi dinamis seperti itu kami menggunakan fungsi pencocokan untuk benar-benar mencocokkan indeks data dan mengembalikan hasil sebenarnya.

Gabungkan VLOOKUP dengan Match

Rumus vlookup adalah fungsi yang paling umum digunakan yang digunakan untuk mencari dan mengembalikan nilai yang sama dalam indeks kolom yang ditentukan atau nilai dari indeks kolom yang berbeda dengan mengacu pada nilai yang cocok dari kolom pertama. Tantangan utama yang dihadapi saat menggunakan vlookup adalah indeks kolom yang akan ditentukan bersifat statis dan tidak memiliki fungsionalitas dinamis. Terutama saat Anda mengerjakan beberapa kriteria yang mengharuskan Anda mengubah indeks kolom referensi secara manual. Dengan demikian, kebutuhan ini dapat dipenuhi dengan menggunakan rumus “MATCH” untuk lebih menguasai atau mengontrol indeks kolom yang sering berubah dalam rumus VLOOKUP.

VLookup dan Formula Pertandingan

# 1 - Formula VLOOKUP

Rumus Fungsi VLOOKUP di Excel

Di sini semua argumen yang harus dimasukkan bersifat wajib.

  • Lookup_value - Di sini sel referensi atau teks dengan tanda kutip ganda harus dimasukkan untuk diidentifikasi dalam rentang kolom.
  • Array tabel -   Argumen ini mengharuskan rentang tabel dimasukkan di mana Lookup_value harus dicari dan data yang akan diambil berada di kisaran kolom tertentu.
  • Col_index_num - Dalam argumen ini, nomor indeks kolom atau jumlah kolom dari kolom pertama referensi perlu dimasukkan dari mana nilai yang sesuai perlu ditarik dari posisi yang sama dengan nilai yang dicari di kolom pertama.
  • [Range_lookup] - Argumen ini akan memberikan dua opsi.
  • BENAR - Perkiraan kecocokan: - Argumen dapat dimasukkan sebagai BENAR atau angka “1”, yang mengembalikan kecocokan perkiraan yang sesuai dengan kolom referensi atau kolom pertama. Selain itu, nilai di kolom pertama larik tabel harus diurutkan dalam urutan menaik.
  • SALAH - Pencocokan persis: - Di sini argumen yang akan dimasukkan bisa SALAH atau numerik "0". Opsi ini hanya akan mengembalikan nilai yang sama persis dengan yang akan diidentifikasi dari posisi di rentang kolom pertama. Kegagalan untuk mencari nilai dari kolom pertama akan menghasilkan pesan kesalahan "# N / A".

# 2 - Formula Pertandingan

Fungsi cocok mengembalikan posisi sel dari nilai yang dimasukkan untuk larik tabel yang diberikan.

Semua argumen dalam sintaksis bersifat wajib.

  • Lookup_value - Di sini argumen yang dimasukkan dapat berupa referensi sel dari nilai atau string teks dengan tanda kutip ganda yang posisi selnya harus ditarik.
  • Lookup_array - Rentang array untuk tabel harus dimasukkan yang nilai atau konten selnya ingin diidentifikasi.
  • [jenis pencocokan] - Argumen ini memberikan tiga opsi seperti yang dijelaskan di bawah.
  • “1-Less than” - Di sini argumen yang akan dimasukkan adalah numerik “1” yang akan mengembalikan nilai yang kurang dari atau sama dengan nilai pencarian. Dan juga array pencarian harus diurutkan dalam urutan menaik.
  • "0-Exact match" - Di sini argumen yang akan dimasukkan harus numerik "0". Opsi ini akan mengembalikan posisi persis dari nilai pencarian yang cocok. Namun, array pencarian bisa dalam urutan apa pun.
  • "-1-Greater than" -  Argumen yang akan dimasukkan harus numerik "-1". Opsi ketiga menemukan nilai terkecil yang lebih besar dari atau sama dengan nilai pencarian. Di sini urutan array pencarian harus ditempatkan dalam urutan menurun.

# 3 - VLOOKUP dengan Formula PERTANDINGAN

= VLOOKUP (lookup_value, table_array, MATCH (lookup_value, lookup_array, [match_type]), [range lookup])

Bagaimana Cara Menggunakan VLOOKUP dengan Formula Pertandingan di Excel?

Contoh di bawah ini akan membantu memahami fungsi rumus vlookup dan pencocokan saat menyusun.

Anda dapat mendownload VLookup dengan Template Match Excel di sini - VLookup dengan Template Match Excel

Perhatikan tabel data di bawah ini yang menjelaskan spesifikasi kendaraan yang akan dibeli.

Untuk mendapatkan kejelasan fungsi gabungan untuk vlookup dan fungsi pencocokan, mari kita memahami bagaimana cara kerja rumus individual dan kemudian sampai pada hasil pencocokan vlookup saat disatukan.

Langkah # 1 - Mari kita menerapkan rumus vlookup pada tingkat individu untuk sampai pada hasilnya.

Outputnya ditunjukkan di bawah ini:

Di sini nilai pencarian mengacu pada $ B9 yaitu model "E" dan array pencarian diberikan sebagai rentang tabel data dengan nilai absolut "$", indeks kolom disebut kolom "4" yang merupakan hitungan untuk kolom "Type" dan pencarian rentang diberikan kecocokan persis.

Jadi, rumus berikut diterapkan untuk mengembalikan nilai kolom "Bahan Bakar".

Outputnya ditunjukkan di bawah ini:

Di sini nilai pencarian dengan string absolut "$" diterapkan untuk nilai pencarian dan lookup_array membantu memperbaiki sel referensi meskipun rumus sedang disalin ke sel yang berbeda. Di kolom "Bahan bakar", kita perlu mengubah indeks kolom menjadi "5" sebagai nilai dari mana data yang dibutuhkan untuk mengambil perubahan.

Langkah # 2 -  Sekarang mari kita menerapkan rumus Cocok untuk mengambil posisi untuk nilai pencarian yang diberikan.

Outputnya ditunjukkan di bawah ini:

Seperti yang dapat dilihat pada gambar di atas, di sini kami mencoba mengambil posisi kolom dari larik tabel. Dalam kasus ini, nomor kolom yang akan ditarik disebut sebagai sel C8 yaitu kolom "Jenis" dan rentang pencarian yang akan dicari diberikan sebagai rentang tajuk kolom dan jenis pencocokan diberikan kecocokan yang tepat menjadi " 0 ".

Dengan demikian tabel di bawah ini akan memberikan hasil yang diinginkan untuk posisi kolom “Bahan Bakar”.

Sekarang di sini kolom yang akan dicari diberikan menjadi sel D8 dan indeks kolom yang diinginkan dikembalikan menjadi "5".

Langkah # 3 - Sekarang rumus Match akan digunakan dalam fungsi vlookup untuk mendapatkan nilai dari posisi kolom yang diidentifikasi.

Outputnya ditunjukkan di bawah ini:

Dalam rumus di atas, fungsi kecocokan menggantikan parameter indeks kolom dari fungsi vlookup. Di sini fungsi pencocokan akan mengidentifikasi sel referensi nilai pencarian "C8" dan mengembalikan nomor kolom melalui larik tabel yang diberikan. Posisi kolom ini akan berfungsi sebagai masukan ke argumen indeks kolom di fungsi vlookup. Yang pada gilirannya akan membantu vlookup untuk mengidentifikasi nilai yang akan dikembalikan dari nomor indeks kolom yang dihasilkan?

Demikian pula, kami telah menerapkan vlookup dengan rumus kecocokan untuk kolom "Bahan Bakar" juga.

Outputnya ditunjukkan di bawah ini:

Dengan demikian, kita dapat menerapkan fungsi kombinasi ini untuk kolom "Jenis" dan "Bahan Bakar" lainnya juga.

Hal-hal untuk diingat

  • VLOOKUP dapat diterapkan ke nilai pencarian hanya di sisi kiri utamanya. Setiap nilai yang ada untuk dicari di sisi kanan tabel data akan mengembalikan nilai kesalahan "# N / A".
  • Rentang table_array yang dimasukkan dalam argumen kedua haruslah referensi sel absolut "$", ini akan mempertahankan rentang array tabel tetap saat menerapkan rumus pencarian ke sel lain, atau sel referensi untuk rentang array tabel akan bergeser ke sel berikutnya referensi.
  • Nilai yang dimasukkan dalam nilai pencarian tidak boleh lebih kecil dari nilai terkecil di kolom pertama larik tabel, atau fungsi akan mengembalikan nilai kesalahan "# N / A".
  • Sebelum menerapkan perkiraan kecocokan "TRUE" atau "1" dalam argumen terakhir, selalu ingat untuk mengurutkan larik tabel dalam urutan menaik.
  • Fungsi match hanya mengembalikan posisi nilai dalam larik tabel vlookup dan tidak mengembalikan nilainya.
  • Jika Fungsi Pencocokan tidak dapat mengidentifikasi posisi nilai pencarian dalam larik tabel, maka rumus mengembalikan "# N / A" dalam nilai kesalahan.
  • Fungsi Vlookup dan pencocokan tidak peka huruf besar / kecil saat mencocokkan nilai pencarian dengan nilai teks yang cocok dalam larik tabel.