Formula generik
=VLOOKUP(val&"",table,col,0)
Ringkasan
Untuk menggunakan fungsi VLOOKUP untuk mengambil maklumat dari jadual di mana nilai kunci adalah nombor yang disimpan sebagai teks, anda boleh menggunakan formula yang menggabungkan rentetan kosong ("") ke nilai carian angka, memaksanya ke teks. Dalam contoh yang ditunjukkan, formula dalam H3 adalah:
=VLOOKUP(id&"",planets,2,0) // returns "Earth"
di mana id (H2) dan planet (B3: B11) dinamakan julat.
Catatan: Contohnya adalah penyelesaian untuk masalah nombor dan teks yang tidak sesuai, yang menyebabkan kesalahan # N / A. Sekiranya tidak ada ketidakcocokan, penyelesaiannya tidak diperlukan dan anda boleh menggunakan formula VLOOKUP biasa.
Penjelasan
Ralat VLOOKUP biasa adalah ketidakcocokan antara nombor dan teks. Selalunya, lajur carian dalam jadual mengandungi nilai angka yang kelihatan seperti nombor, tetapi sebenarnya nombor disimpan sebagai teks. Apabila nombor tulen diteruskan ke VLOOKUP sebagai argumen pertama, formula mengembalikan ralat # N / A, walaupun sepertinya ada yang sesuai. Skrin di bawah menunjukkan contoh masalah ini:
Nombor di lajur B sebenarnya teks, jadi nilai pencarian angka, 3, gagal, walaupun sepertinya VLOOKUP harus sepadan dengan B5 dan mengembalikan "Bumi". Anda boleh memasukkan nombor sebagai nilai teks dengan mendahului nombor dengan satu petikan (').
Penyelesaian terbaik adalah memastikan nilai carian dalam jadual memang nombor. Namun, jika anda tidak mempunyai kawalan ke atas meja, anda boleh mengubah formula VLOOKUP untuk memaksa nilai pencarian agar sesuai dengan jenis di dalam jadual. Dalam contoh yang ditunjukkan, kami memaksa nilai pencarian angka ke teks dengan menggabungkan rentetan kosong:
=VLOOKUP(id,planets,2,0) // original =VLOOKUP(id&"",planets,2,0) // revised
Dan formula yang disemak semula mengatasi kesilapan:
Anda juga boleh melakukan perkara yang sama dengan formula yang lebih panjang yang menggunakan fungsi TEXT untuk menukar nombor menjadi teks:
=VLOOKUP(TEXT(id,"@"),planets,2,0)
Kedua-dua nombor dan teks
Sekiranya anda tidak pasti bila anda akan mempunyai nombor dan bila anda akan mempunyai teks, anda boleh memenuhi kedua-dua pilihan dengan membungkus VLOOKUP dalam fungsi IFERROR dan menggunakan formula yang menangani kedua-dua kes:
=IFERROR(VLOOKUP(id,planets,3,0),VLOOKUP(id&"",planets,3,0))
Di sini, pertama-tama kita mencuba formula VLOOKUP biasa yang mengandaikan kedua nilai carian dan lajur pertama dalam jadual adalah nombor. Sekiranya itu menimbulkan ralat, kami cuba lagi dengan formula yang disemak semula. Sekiranya formula itu juga gagal, VLOOKUP akan mengembalikan ralat # N / A seperti biasa.
