Mengapa INDEX-MATCH Mengatasi VLOOKUP

Argumen ketiga VLOOKUP ialah nombor lajur yang ditetapkan terus, seperti angka 3 dalam =VLOOKUP(E1, A2:D20, 3, FALSE).

Jika seseorang menyisipkan lajur baharu di tengah jadual, angka 3 itu kini menunjuk pada medan yang salah dan formula anda secara senyap mengembalikan data yang salah.

INDEX-MATCH merujuk lajur sebenar melalui julat, jadi apabila lajur disisipkan, rujukan beralih secara automatik dan hasilnya kekal betul.

Oleh sebab INDEX menunjuk pada julat lajur tertentu seperti C2:C20, rujukan itu bergerak bersama lajur apabila susun atur berubah.

Sisipkan lajur baharu di hadapannya dan hamparan akan mengemas kini C2:C20 kepada D2:D20 secara automatik. Formula itu terus mengembalikan medan yang sama.

Ketahanan ini penting dalam buku kerja sebenar yang disunting oleh ramai orang dari semasa ke semasa. Lebih sedikit ralat tersembunyi bermakna laporan yang lebih boleh dipercayai.

VLOOKUP sering merujuk keseluruhan blok jadual, seperti A2:Z20, walaupun anda hanya memerlukan satu lajur. Dalam helaian besar, ini bermakna enjin mengimbas jauh lebih banyak sel daripada yang diperlukan.

INDEX-MATCH hanya mengakses dua lajur yang sempit: lajur yang dicari dan lajur yang mengembalikan nilai.

Untuk beberapa formula, perbezaannya tidak ketara, tetapi merentas ribuan carian, INDEX-MATCH boleh mengira semula dengan ketara lebih pantas.

Untuk mendapatkan beberapa medan dengan VLOOKUP, anda perlu mengulangi keseluruhan formula dan mengubah nombor lajur setiap kali, yang mudah menyebabkan kesilapan.

Dengan INDEX-MATCH, anda mengira kedudukan sekali dan menggunakannya semula. Ramai orang menyimpan =MATCH(E1, A2:A20, 0) dalam sel bantuan, katakan H1, kemudian menulis =INDEX(C2:C20, H1) dan =INDEX(D2:D20, H1).

Satu padanan, banyak pengambilan nilai yang kemas.

Hamparan yang lebih baharu menyediakan XLOOKUP, yang juga boleh membuat carian dalam apa-apa arah dan mengelakkan masalah nombor lajur, jadi fungsi ini menyelesaikan isu yang sama seperti INDEX-MATCH.

=XLOOKUP(E1, A2:A20, C2:C20) kelihatan kemas dan mudah dibaca.

Walau bagaimanapun, XLOOKUP tidak tersedia dalam versi Excel yang lebih lama atau sesetengah buku kerja yang dikongsi. INDEX-MATCH berfungsi hampir di semua tempat, sebab itulah ia kekal sebagai kemahiran penting.

Sejujurnya, INDEX-MATCH mempunyai satu kelemahan: formulanya lebih panjang dan lebih sukar dibaca sepintas lalu berbanding VLOOKUP.

Bandingkan =VLOOKUP(E1, A2:D20, 3, FALSE) dengan =INDEX(C2:C20, MATCH(E1, A2:A20, 0)).

Struktur bersarang ini memerlukan latihan. Membaca dari dalam ke luar, MATCH dahulu kemudian INDEX, menjadikannya lebih mudah diurus, dan fleksibiliti itu biasanya mengatasi aksara tambahan.

Inilah carian yang sama ditulis dalam kedua-dua cara untuk jadual yang mempunyai nama dalam A dan gaji dalam D:

• VLOOKUP: =VLOOKUP(E1, A2:D20, 4, FALSE)
• INDEX-MATCH: =INDEX(D2:D20, MATCH(E1, A2:A20, 0))

Kedua-duanya mengembalikan gaji. Tetapi jika lajur disisipkan, hanya versi INDEX-MATCH kekal betul, dan hanya versi itu yang boleh mengembalikan nilai di sebelah kiri lajur A.

Panduan praktikal:

• Gunakan XLOOKUP apabila hamparan anda menyokongnya, untuk sintaks moden yang paling kemas
• Gunakan INDEX-MATCH untuk keserasian maksimum, carian ke kiri dan rujukan yang tidak terjejas oleh penyisipan lajur
• Gunakan VLOOKUP hanya untuk carian ringkas ke sebelah kanan kunci dalam jadual yang stabil

Dengan mengetahui INDEX-MATCH, anda boleh membaca dan membaiki pelbagai buku kerja sedia ada yang bergantung padanya.

VLOOKUP ialah alat tunggal yang tegar. INDEX-MATCH pula terdiri daripada dua idea mudah—mencari kedudukan dan mengambil nilai—yang boleh anda gabungkan dengan cara yang fleksibel.

Kebolehgabungan itulah pelajaran sebenar: fungsi kecil yang boleh dicantumkan membolehkan anda mengendalikan carian dua hala, carian ke kiri dan pengambilan daripada berbilang medan yang tidak dapat dilakukan oleh fungsi sekali jalan.

Kuasai blok binaan ini dan anda akan melampaui batasan mana-mana fungsi carian tunggal.

Anda telah membandingkan kedua-dua pendekatan dan melihat kelebihan INDEX-MATCH:

• Ia boleh membuat carian dalam apa-apa arah, termasuk di sebelah kiri kunci
• Rujukan lajurnya kekal berfungsi selepas lajur disisipkan atau dialihkan
• Ia boleh menjadi lebih pantas dengan membaca hanya dua lajur yang diperlukan
• Ia berfungsi dalam hamparan lama yang tidak menyediakan XLOOKUP

VLOOKUP sesuai untuk kerja ringkas, tetapi INDEX-MATCH memberikan carian yang kukuh dan fleksibel, iaitu asas kepada teknik carian dua hala lanjutan yang akan datang.