Rumus Vlookup hanya berfungsi ketika larik tabel dalam rumus tidak berubah, tetapi jika ada kolom baru yang disisipkan ke tabel atau kolom dihapus, rumus tersebut memberikan hasil yang salah atau mencerminkan kesalahan, untuk membuat rumus bebas 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. Oleh karena itu, kebutuhan ini dipenuhi dengan menggunakan rumus “MATCH” untuk memiliki pegangan atau kontrol yang lebih baik terhadap 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.
- Larik tabel - Argumen ini mengharuskan rentang tabel dimasukkan di mana Nilai_penelitian harus dicari dan data yang akan diambil berada dalam rentang 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 TRUE atau numerik “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 tepat: - 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 pencocokan 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 menyediakan tiga opsi, seperti yang dijelaskan di bawah ini.
- “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 berupa angka "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 untuk 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.
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 pahami bagaimana rumus individu beroperasi 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 dirujuk ke $ B9 yaitu model "E", dan array pencarian diberikan sebagai rentang tabel data dengan nilai absolut "$", indeks kolom dirujuk ke 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 yang datanya diperlukan 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 diberi kecocokan yang persis sama sebagai "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 di dalam fungsi vlookup untuk mendapatkan nilai dari posisi kolom yang teridentifikasi.
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. Manakah, pada gilirannya, yang 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 akan 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.
