Rumus Vlookup hanya berfungsi apabila susunan jadual dalam formula tidak berubah, tetapi jika ada lajur baru yang disisipkan ke meja atau lajur dihapus, formula memberikan hasil yang salah atau mencerminkan kesalahan, untuk menjadikan formula bebas ralat di situasi dinamik seperti itu kita menggunakan fungsi padanan untuk benar-benar memadankan indeks data dan mengembalikan hasil sebenar.
Gabungkan VLOOKUP dengan Match
Rumus vlookup adalah fungsi yang paling sering digunakan yang digunakan untuk mencari dan mengembalikan sama ada nilai yang sama dalam indeks lajur yang ditentukan atau nilai dari indeks lajur yang berbeza dengan merujuk pada nilai yang sesuai dari lajur pertama. Cabaran utama yang dihadapi ketika menggunakan vlookup adalah bahawa indeks lajur yang akan ditentukan adalah statik dan tidak mempunyai fungsi yang dinamik. Terutama apabila anda menggunakan beberapa kriteria yang memerlukan anda menukar indeks lajur rujukan secara manual. Oleh itu, keperluan ini dipenuhi dengan menggunakan formula "MATCH" untuk mendapatkan pegangan atau kawalan yang lebih baik terhadap indeks lajur yang sering berubah dalam formula VLOOKUP.
Formula VLookup and Match
# 1 - Formula VLOOKUP
Rumus Fungsi VLOOKUP di Excel
Di sini semua hujah yang hendak dimasukkan adalah wajib.
- Lookup_value - Di sini, sel rujukan atau teks dengan petikan berganda harus dimasukkan untuk dikenal pasti dalam julat lajur.
- Susunan jadual - Argumen ini memerlukan julat jadual dimasukkan di mana Nilai_Perhatian harus dicari dan data yang akan diambil berada dalam julat lajur tertentu.
- Col_index_num - Dalam argumen ini, nombor indeks lajur atau kiraan lajur dari lajur rujukan pertama perlu dimasukkan dari mana nilai yang sesuai perlu ditarik dari kedudukan yang sama dengan nilai yang dicari di lajur pertama.
- (Range_lookup) - Argumen ini akan memberikan dua pilihan.
- BENAR - Pencocokan anggaran: - Argumen dapat dimasukkan sebagai BENAR atau angka 1, yang mengembalikan pencocokan perkiraan yang sesuai dengan lajur rujukan atau lajur pertama. Lebih-lebih lagi, nilai pada lajur pertama array jadual mesti disusun mengikut urutan menaik.
- FALSE - Padanan tepat: - Di sini, argumen yang akan dimasukkan boleh menjadi PALSU atau angka "0". Pilihan ini hanya akan mengembalikan padanan tepat dari nilai yang akan dikenal pasti dari kedudukan dalam julat lajur pertama. Kegagalan untuk mencari nilai dari lajur pertama akan mengembalikan mesej ralat "# N / A".
# 2 - Formula Perlawanan
Fungsi padanan mengembalikan kedudukan sel nilai yang dimasukkan untuk susunan jadual yang diberikan.
Semua hujah dalam sintaks adalah wajib.
- Lookup_value - Di sini, argumen yang dimasukkan boleh menjadi rujukan sel nilai atau rentetan teks dengan tanda kutip ganda yang kedudukan selnya diperlukan untuk ditarik.
- Lookup_array - Julat susunan untuk jadual perlu dimasukkan yang nilai atau kandungan selnya ingin dikenali.
- (jenis padanan) - Argumen ini memberikan tiga pilihan, seperti yang dijelaskan di bawah.
- "1-Kurang dari" - Di sini, argumen yang akan dimasukkan adalah angka "1", yang akan mengembalikan nilai yang kurang dari atau sama dengan nilai pencarian. Dan juga, tatasusunan carian mesti disusun mengikut urutan menaik.
- "0-Tepat padanan" - Di sini, argumen yang harus dimasukkan harus berangka "0". Pilihan ini akan mengembalikan kedudukan tepat dari nilai carian yang dipadankan. Walau bagaimanapun, susunan carian boleh mengikut urutan apa pun.
- "-1-Lebih besar dari" - Argumen yang harus dimasukkan harus berangka "-1". Pilihan ketiga mencari nilai terkecil yang lebih besar daripada atau sama dengan nilai carian. Di sini pesanan untuk array carian mesti dibuat mengikut urutan menurun.
# 3 - VLOOKUP dengan MATCH Formula
= VLOOKUP (lookup_value, table_array, MATCH (lookup_value, lookup_array, (match_type)), (range range))
Bagaimana Menggunakan VLOOKUP dengan Match Formula di Excel?
Contoh di bawah ini akan membantu memahami fungsi formula pandangan dan padanan ketika mengumpulkan.
Pertimbangkan jadual data di bawah, yang menerangkan spesifikasi kenderaan yang akan dibeli.
Untuk mendapatkan kejelasan fungsi gabungan untuk fungsi vlookup dan match, mari kita fahami bagaimana formula individu beroperasi dan kemudian sampai pada hasil pertandingan vlookup ketika disatukan.
Langkah # 1 - Mari kita menerapkan formula pandangan pada tahap individu untuk mencapai hasilnya.
Hasilnya ditunjukkan di bawah:
Di sini nilai pencarian disebut $ B9 yang merupakan model "E", dan array pencarian diberikan sebagai julat tabel data dengan nilai mutlak "$" lajur "Jenis", dan carian jarak diberi pencocokan tepat.
Oleh itu, formula berikut digunakan untuk mengembalikan nilai untuk lajur "Bahan Bakar."
Hasilnya ditunjukkan di bawah:
Di sini nilai pencarian dengan string mutlak "$" digunakan untuk nilai pencarian dan lookup_array membantu memperbaiki sel rujukan walaupun formula disalin ke sel lain. Di lajur "Bahan Bakar", kita perlu mengubah indeks lajur menjadi "5" sebagai nilai dari mana data yang diperlukan untuk diambil berubah.
Langkah # 2 - Sekarang, mari kita gunakan formula Match untuk mengambil kedudukan untuk nilai pencarian yang diberikan.
Hasilnya ditunjukkan di bawah:
Seperti yang dapat dilihat pada tangkapan skrin di atas, di sini kita berusaha mendapatkan kembali kedudukan lajur dari susunan jadual. Dalam hal ini, nomor kolom yang akan ditarik disebut sebagai sel C8 yaitu kolom "Jenis", dan julat pencarian yang akan dicari diberikan sebagai kisaran tajuk lajur, dan jenis pencocokan diberi pencocokan tepat untuk menjadi sebagai "0".
Oleh itu, jadual di bawah akan memberikan hasil yang diinginkan untuk kedudukan lajur "Bahan Bakar."
Sekarang di sini, lajur yang dicari diberi sel D8, dan indeks lajur yang diinginkan dikembalikan menjadi “5”.
Langkah # 3 - Sekarang, formula Match akan digunakan dalam fungsi vlookup untuk mendapatkan nilai dari kedudukan lajur yang dikenal pasti.
Hasilnya ditunjukkan di bawah:
Dalam formula di atas, fungsi pencocokan diletakkan di tempat parameter indeks lajur fungsi vlookup. Di sini fungsi pertandingan akan mengenal pasti sel rujukan nilai carian "C8" dan mengembalikan nombor lajur melalui susunan jadual yang diberikan. Posisi lajur ini akan berfungsi sebagai input untuk argumen indeks lajur dalam fungsi vlookup. Yang seterusnya, yang akan membantu melihat untuk mengenal pasti nilai yang akan dikembalikan dari nombor indeks lajur yang dihasilkan?
Demikian pula, kami telah menerapkan vlookup dengan formula pertandingan untuk kolom "Fuel" juga.
Hasilnya ditunjukkan di bawah:
Oleh itu, kita dapat menerapkan fungsi kombinasi ini untuk lajur lain "Jenis" dan "Bahan Bakar" juga.
Perkara yang Perlu Diingat
- VLOOKUP dapat digunakan untuk mencari nilai hanya di sebelah kiri utamanya. Nilai yang ada untuk dicari di sebelah kanan jadual data akan mengembalikan nilai ralat "# N / A".
- Julat table_array yang dimasukkan dalam argumen kedua harus menjadi rujukan sel mutlak "$", ini akan mengekalkan julat array jadual tetap ketika menerapkan formula pencarian ke sel lain, atau sel rujukan untuk julat susunan jadual akan beralih ke sel berikutnya rujukan.
- Nilai yang dimasukkan dalam nilai pencarian tidak boleh lebih kecil daripada nilai terkecil pada lajur pertama dari array tabel, jika tidak, fungsi akan mengembalikan nilai ralat "# N / A".
- Sebelum menerapkan padanan anggaran "BENAR" atau "1" pada argumen terakhir, selalu ingat untuk menyusun susunan jadual dalam urutan menaik.
- Fungsi padanan hanya mengembalikan kedudukan nilai dalam susunan jadual vlookup dan tidak mengembalikan nilainya.
- Sekiranya Match Fungsi tidak dapat mengenal pasti kedudukan nilai pencarian dalam array jadual, maka rumus mengembalikan "# N / A" dalam nilai kesalahan.
- Fungsi pandangan dan padanan tidak peka huruf besar-kecil ketika memadankan nilai pencarian dengan nilai teks yang sesuai dalam susunan jadual.
