VLOOKUP dengan Match

Formula Vlookup hanya berfungsi apabila susunan jadual dalam formula tidak berubah, tetapi jika ada lajur baru yang dimasukkan ke dalam jadual atau lajur dihapus, formula memberikan hasil yang salah atau mencerminkan kesalahan, untuk menjadikan formula bebas ralat 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 memiliki pegangan atau kawalan yang lebih baik terhadap indeks lajur yang sering berubah dalam formula VLOOKUP.

Formula VLookup and Match

# 1 - Formula VLOOKUP

Formula Fungsi VLOOKUP di Excel

Di sini semua hujah yang hendak dimasukkan adalah wajib.

  • Lookup_value - Di sini sel atau teks rujukan dengan tanda kutip 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 susunan jadual mesti disusun mengikut urutan menaik.
  • FALSE - Padanan tepat: - Di sini argumen yang hendak 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 array 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 array 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]), [jangkauan carian])

Bagaimana Menggunakan VLOOKUP dengan Match Formula di Excel?

Contoh di bawah ini akan membantu memahami fungsi formula pandangan dan padanan ketika mengumpulkan.

Anda boleh memuat turun Templat VLookup dengan Match Excel ini di sini - Templat VLookup dengan Match Excel

Pertimbangkan jadual data di bawah yang menerangkan spesifikasi kenderaan yang akan dibeli.

Untuk mendapatkan kejelasan fungsi gabungan untuk fungsi vlookup dan match mari kita memahami bagaimana formula individu beroperasi dan kemudian sampai pada hasil pertandingan vlookup ketika disatukan.

Langkah # 1 - Marilah 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 "$", indeks lajur disebut lajur "4" yang merupakan hitungan untuk lajur "Jenis" dan pencarian julat diberi padanan 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 kolom "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 rentang pencarian yang akan dicari diberikan sebagai rentang tajuk lajur dan jenis pencocokan diberi pencocokan tepat untuk menjadi " 0 ”.

Oleh itu, jadual di bawah akan memberikan hasil yang diinginkan untuk kedudukan lajur "Bahan Bakar".

Sekarang di sini lajur yang akan 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 akan membantu vlookup untuk mengenal pasti nilai yang akan dikembalikan dari nombor indeks lajur yang terhasil?

Begitu juga, 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. Setiap 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 susunan jadual tetap ketika menerapkan formula pencarian ke sel lain, atau sel rujukan untuk julat array 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" dalam 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 Fungsi Pencocokan tidak dapat mengenal pasti kedudukan nilai pencarian dalam susunan 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.