Formula generik
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Ringkasan
Untuk membalikkan VLOOKUP - iaitu untuk mencari nilai carian asal menggunakan hasil formula VLOOKUP - anda boleh menggunakan formula rumit berdasarkan fungsi PILIH, atau formula lebih mudah berdasarkan INDEX dan MATCH atau XLOOKUP seperti yang dijelaskan di bawah. Dalam contoh yang ditunjukkan, formula dalam H10 adalah:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Dengan persediaan ini, VLOOKUP mencari pilihan yang berkaitan dengan kos 3000, dan mengembalikan "C".
Nota: ini adalah topik yang lebih maju. Sekiranya anda baru memulakan dengan VLOOKUP, mulakan di sini.
Pengenalan
Batasan utama VLOOKUP ialah ia hanya dapat mencari nilai ke kanan. Dengan kata lain, lajur dengan nilai carian mesti berada di sebelah kiri nilai yang ingin anda dapatkan dengan VLOOKUP. Hasilnya, dengan konfigurasi standard, tidak ada cara untuk menggunakan VLOOKUP untuk "melihat ke kiri" dan membalikkan carian semula.
Dari sudut pandangan VLOOKUP, kita dapat menggambarkan masalah seperti ini:
Penyelesaian yang dijelaskan di bawah menggunakan fungsi PILIH untuk menyusun semula jadual di dalam VLOOKUP.
Penjelasan
Bermula dari awal, formula di H5 adalah formula VLOOKUP biasa:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Menggunakan G5 sebagai nilai pencarian ("C"), dan data dalam B5: D8 sebagai susunan jadual, VLOOKUP melakukan pencarian nilai pada lajur B, dan mengembalikan nilai yang sesuai dari lajur 3 (lajur D), 3000. Perhatikan sifar (0) disediakan sebagai argumen terakhir untuk memaksa padanan tepat.
Rumus dalam G10 hanya menarik hasil dari H5:
=H5 // 3000
Untuk melakukan carian terbalik, formula dalam H10 adalah:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Bit yang rumit adalah fungsi PILIH, yang digunakan untuk menyusun semula susunan jadual sehingga Kos adalah lajur pertama, dan Pilihan adalah yang terakhir:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
Fungsi PILIH dirancang untuk memilih nilai berdasarkan indeks angka. Dalam kes ini, kami menyediakan tiga nilai indeks dalam pemalar array:
(3,2,1) // array constant
Dengan kata lain, kami meminta lajur 3, kemudian lajur 2, kemudian lajur 1. Ini diikuti oleh tiga julat yang mewakili setiap lajur jadual dalam urutan yang muncul di lembaran kerja.
Dengan konfigurasi ini, PILIH mengembalikan ketiga-tiga lajur dalam satu array 2D seperti ini:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Sekiranya kita memvisualisasikan susunan ini sebagai jadual pada lembaran kerja, kita mempunyai:
Catatan: tajuk bukan sebahagian daripada larik dan ditunjukkan di sini untuk kejelasan sahaja.
Secara efektif, kami telah menukar lajur 1 dan 3. Jadual yang disusun semula dikembalikan terus ke VLOOKUP, yang sepadan dengan 3000, dan mengembalikan nilai yang sesuai dari lajur 3, "C".
Dengan INDEX dan MATCH
Penyelesaian di atas berfungsi dengan baik, tetapi sukar untuk dicadangkan kerana kebanyakan pengguna tidak akan memahami bagaimana formula berfungsi. Penyelesaian yang lebih baik adalah INDEX dan MATCH, menggunakan formula seperti ini:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Di sini, fungsi MATCH menemui nilai 3000 dalam D5: D8, dan mengembalikan kedudukannya, 3:
MATCH(G10,D5:D8,0) // returns 3
Catatan: MATCH dikonfigurasi untuk pencocokan tepat dengan menetapkan argumen terakhir ke sifar (0).
MATCH mengembalikan hasil terus ke INDEX sebagai nombor baris, jadi rumus menjadi:
=INDEX(B5:B8,3) // returns "C"
dan INDEX mengembalikan nilai dari baris ketiga B5: B8, "C".
Formula ini menunjukkan bagaimana INDEX dan MATCH boleh menjadi lebih fleksibel daripada VLOOKUP.
Dengan XLOOKUP
XLOOKUP juga memberikan penyelesaian yang sangat baik. Formula setara adalah:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
Dengan nilai carian dari G10 (3000), susunan al ookup D5: D8 (kos) dan susunan hasil B5: B8 (pilihan), XLOOKUP mencari 3000 dalam tatasusunan carian, dan mengembalikan item yang sesuai dari susunan hasil, "C". Kerana XLOOKUP melakukan padanan tepat secara lalai, tidak perlu mengatur mod padanan secara eksplisit.
