VLOOKUP dengan Kecocokan

Rumus VLOOKUP hanya berfungsi jika larik tabel dalam rumus tidak berubah. Namun, jika kolom baru dimasukkan ke dalam tabel atau kolom dihapus, rumus memberikan hasil yang salah atau mencerminkan kesalahan. Untuk membuat rumus bebas kesalahan dalam situasi dinamis seperti itu, kami menggunakan fungsi MATCH untuk mencocokkan indeks data dan mengembalikan hasil yang sebenarnya.

Gabungkan VLOOKUP dengan Kecocokan

Rumus VLOOKUP adalah fungsi yang paling umum digunakan untuk mencari dan mengembalikan nilai yang sama dalam indeks kolom yang ditentukan atau nilai dari indeks kolom berbeda mengenai nilai yang cocok dari kolom pertama. Tantangan utama yang dihadapi saat menggunakan VLOOKUP adalah indeks kolom yang akan ditentukan bersifat statis dan tidak memiliki fungsi dinamis. Terutama ketika Anda sedang mengerjakan beberapa kriteria yang mengharuskan Anda untuk mengubah indeks kolom referensi secara manual, kebutuhan ini dipenuhi dengan menggunakan rumus ‘MATCH’ untuk memiliki pegangan atau kontrol yang lebih baik dari indeks kolom yang sering berubah dalam rumus VLOOKUP.

Anda bebas menggunakan gambar ini di situs web Anda, templat, dll., Harap berikan kami tautan atribusiBagaimana Memberikan Atribusi? Tautan Artikel untuk Di-Hyperlink
Misalnya: Sumber: VLOOKUP dengan Kecocokan (wallstreetmojo.com)

Rumus Pencarian dan Pencocokan V

#1 – Rumus VLOOKUP

Rumus fungsi VLOOKUP di Excel:

Di sini, semua argumen yang harus dimasukkan adalah wajib.

nilai lookup – Di sini, sel referensi atau teks dengan tanda kutip ganda harus dimasukkan untuk diidentifikasi dalam rentang kolom.
table_array – Argumen ini memerlukan rentang tabel yang akan dimasukkan di mana nilai_lookup harus dicari dan data yang akan diambil berada di rentang kolom tertentu.
col_index_num – Dalam argumen ini, nomor indeks kolom atau hitungan kolom dari referensi kolom pertama harus 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 numerik ‘1’, yang mengembalikan perkiraan kecocokan yang sesuai dengan kolom referensi atau kolom pertama. Selain itu, kita harus mengurutkan nilai di kolom pertama dari susunan tabel dalam urutan menaik.
FALSE – Pencocokan tepat: – Di sini, argumen yang akan dimasukkan bisa berupa ‘FALSE’ atau numerik ‘0’. Opsi ini hanya akan mengembalikan pencocokan persis dari nilai yang sesuai untuk diidentifikasi dari posisi di rentang kolom pertama. Kegagalan untuk mencari nilai dari kolom pertama akan mengembalikan pesan kesalahan #N/A.

#2 – Formula Pertandingan

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

Semua argumen dalam sintaks adalah wajib.

lookup_value – 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 tabel harus dimasukkan yang nilai atau konten selnya ingin diidentifikasi.
[tipe pencocokan] – Argumen ini menyediakan tiga opsi, seperti yang dijelaskan di bawah ini.
‘1-Kurang dari’ – Argumen yang akan dimasukkan adalah numerik ‘1’, yang akan mengembalikan nilai yang kurang dari atau sama dengan nilai pencarian. Dan juga, kita harus mengurutkan array pencarian dalam urutan menaik.
‘0-Exact match’ – Argumen yang akan dimasukkan harus numerik ‘0’. Opsi ini akan mengembalikan posisi yang tepat dari nilai pencarian yang cocok. Namun, array pencarian bisa dalam urutan apa pun.
‘-1-Lebih besar dari’ – Argumen yang akan dimasukkan harus numerik ‘-1’. Opsi ketiga menemukan nilai terkecil lebih besar dari atau sama dengan Lebih besar dari atau sama dengan ‘lebih besar dari atau sama dengan’ adalah operator pembanding atau logika yang membantu membandingkan dua sel data dari tipe data yang sama. Baca selengkapnya di lookup_value. Di sini, kita harus mengurutkan array pencarian dalam urutan menurun.

#3 – VLOOKUP dengan Formula MATCH

=VLOOKUP(lookup_value, table_array, MATCH(lookup_value, lookup_array, [jenis_cocokan]),[pencarian rentang])

Bagaimana Cara Menggunakan VLOOKUP dengan Formula Pencocokan di Excel?

Contoh di bawah ini akan membantu memahami fungsi rumus VLOOKUP dan MATCH jika disatukan.

Perhatikan tabel data di bawah Tabel DataTabel data di excel adalah jenis alat analisis bagaimana-jika yang memungkinkan Anda membandingkan variabel dan melihat pengaruhnya terhadap hasil dan keseluruhan data. Itu dapat ditemukan di bawah tab data di bagian analisis bagaimana-jika. Baca lebih lanjut, yang menjelaskan spesifikasi kendaraan yang akan dibeli.

Untuk memperjelas fungsi gabungan untuk fungsi VLOOKUP dan MATCH, mari kita pahami bagaimana rumus individual beroperasi dan kemudian sampai pada hasil VLOOKUP MATCH saat disatukan.

Langkah #1 – Mari kita terapkan rumus VLOOKUP pada tingkat individu untuk mendapatkan hasilnya.

Outputnya ditunjukkan di bawah ini:

Di sini, lookup_value disebut sebagai $B9, adalah model ‘E,’ dan lookup_array diberikan sebagai rentang tabel data dengan nilai absolut ‘$’ indeks_kolom disebut sebagai kolom ‘4,’ yang merupakan hitungan untuk kolom ‘Jenis’, dan pencarian rentang diberikan pencocokan persis.

Dengan demikian, rumus berikut diterapkan untuk mengembalikan nilai kolom ‘Bahan Bakar.’

Outputnya ditunjukkan di bawah ini:

Di sini, lookup_value dengan string absolut ‘$’ diterapkan untuk nilai lookup dan lookup_array membantu memperbaiki sel referensi meskipun rumus disalin ke sel yang berbeda. Misalnya, di kolom ‘Bahan Bakar’, kita perlu mengubah indeks kolom menjadi ‘5’ karena nilai dari data yang diperlukan untuk diambil berubah.

Langkah #2 – Sekarang, mari kita terapkan rumus MATCH untuk mengambil posisi untuk lookup_value yang diberikan.

Outputnya ditunjukkan di bawah ini:

Seperti yang ditunjukkan tangkapan layar di atas, kami mencoba mengambil posisi kolom dari larik tabel. Dalam hal ini, nomor kolom yang akan ditarik disebut sel C8, kolom ‘Tipe’, dan rentang_pencarian yang akan dicari diberikan sebagai rentang tajuk kolom, dan jenis pencocokan diberikan pencocokan tepat sebagai ‘0’ .

Dengan demikian, tabel di bawah ini akan memberikan hasil yang diinginkan untuk posisi kolom ‘Bahan Bakar’.

Di sini, kolom yang dicari diberikan menjadi sel D8, dan indeks_kolom yang diinginkan dikembalikan menjadi ‘5’.

Langkah #3 – Kami akan menggunakan rumus MATCH dalam fungsi VLOOKUP untuk mendapatkan nilai dari posisi kolom yang diidentifikasi.

Outputnya ditunjukkan di bawah ini:

Dalam rumus di atas, fungsi MATCH menggantikan parameter column_index dari fungsi VLOOKUP. Di sini, fungsi MATCH akan mengidentifikasi sel referensi lookup_value ‘C8’ dan mengembalikan nomor kolom melalui larik tabel. Posisi kolom ini akan berfungsi sebagai masukan untuk argumen indeks kolom dalam fungsi VLOOKUP. Yang mana, pada gilirannya, yang akan membantu VLOOKUP mengidentifikasi nilai yang dikembalikan dari nomor indeks kolom yang dihasilkan?

Demikian pula, kami juga telah menerapkan VLOOKUP dengan rumus MATCH untuk kolom ‘Bahan Bakar’.

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

Kita dapat menerapkan VLOOKUP untuk mencari nilai hanya di sisi kiri paling depan. Setiap nilai yang ada untuk dicari di sisi kanan tabel data akan mengembalikan nilai kesalahan #N/A.
Kisaran table_array yang dimasukkan dalam argumen kedua harus referensi sel absolutReferensi Sel AbsolutReferensi absolut di excel adalah jenis referensi sel di mana sel yang dirujuk tidak berubah, seperti yang terjadi pada referensi relatif. Dengan menekan f4, kita dapat membuat formula untuk referensi absolut.baca lebih lanjut ‘$’ karena ini akan mempertahankan rentang array tabel tetap saat menerapkan formula pencarian ke sel lain. Jika tidak, table_array, sel referensi rentang akan bergeser ke referensi sel berikutnya.
Nilai yang dimasukkan dalam lookup_value tidak boleh lebih kecil dari nilai terkecil di kolom pertama larik tabel. Jika tidak, fungsi akan mengembalikan nilai kesalahan #N/A.
Sebelum menerapkan pencocokan perkiraan ‘BENAR’ atau ‘1’ dalam argumen terakhir, kita harus selalu ingat untuk mengurutkan susunan tabel dalam urutan menaik.
Fungsi MATCH hanya mengembalikan posisi nilai dalam array tabel vlookupVlookup Table ArraySaat kita menggunakan sel referensi atau nilai untuk mencari dalam grup kolom berisi data yang akan dicocokkan dan mengambil output dalam array tabel VLOOKUP atau pencarian vertikal, fungsi rentang yang kami gunakan disebut array tabel VLOOKUP.baca lebih lanjut dan tidak mengembalikan nilainya.
Jika fungsi MATCH tidak dapat mengidentifikasi posisi lookup_value dalam larik tabel, maka rumus mengembalikan #N/A dalam nilai kesalahan.
Fungsi VLOOKUP dan MATCH tidak peka huruf besar-kecil saat mencocokkan nilai_lookup dengan nilai teks yang cocok di table_array.

Artikel yang Direkomendasikan

Artikel ini adalah panduan untuk VLOOKUP dengan Fungsi MATCH. Di sini, kita belajar cara membuat rumus fleksibel menggunakan VLOOKUP MATCH di Excel dan contoh praktis serta lembar Excel yang dapat diunduh. Anda dapat mempelajari lebih lanjut tentang Excel dari artikel berikut: –

Rumus Excel VLOOKUPRumus Excel VLOOKUPFungsi excel VLOOKUP mencari nilai tertentu dan mengembalikan kecocokan yang sesuai berdasarkan pengenal unik. Pengidentifikasi unik secara unik terkait dengan semua catatan database. Misalnya, ID karyawan, nomor daftar siswa, nomor kontak pelanggan, alamat email penjual, dll., adalah pengidentifikasi unik. Baca selengkapnya
Kesalahan VLOOKUP
Vlookup ke LeftVlookup Ke LeftVlookup ke kiri menemukan nilai masing-masing yang berada di kolom kiri sel referensi. Indeks, dan kecocokan adalah rumus yang digabungkan bersama atau kita dapat menggunakan rumus bersyarat dalam fungsi pencarian untuk menemukan nilai di sebelah kiri.baca lebih lanjut
Fungsi VLookup dengan Fungsi IFVLookup Dengan IFDi Excel, vlookup adalah fungsi referensi, dan IF adalah pernyataan bersyarat. Berdasarkan hasil fungsi Vlookup, mereka menemukan nilai yang memenuhi kriteria dan juga cocok dengan nilai referensi.baca lebih lanjut