Formula umum
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Ringkasan
Untuk membalikkan VLOOKUP - yaitu untuk menemukan nilai pencarian asli menggunakan hasil rumus VLOOKUP - Anda dapat menggunakan rumus rumit berdasarkan fungsi PILIH, atau rumus yang lebih mudah berdasarkan INDEX dan MATCH atau XLOOKUP seperti yang dijelaskan di bawah ini. Pada contoh yang ditampilkan, rumus di H10 adalah:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Dengan penyiapan ini, VLOOKUP menemukan opsi yang terkait dengan biaya 3000, dan mengembalikan "C".
Catatan: ini adalah topik yang lebih maju. Jika Anda baru memulai VLOOKUP, mulailah di sini.
pengantar
Batasan utama VLOOKUP adalah ia hanya dapat mencari nilai di sebelah kanan. Dengan kata lain, kolom dengan nilai pencarian harus berada di sebelah kiri nilai yang ingin Anda ambil dengan VLOOKUP. Akibatnya, dengan konfigurasi standar, tidak ada cara untuk menggunakan VLOOKUP untuk "melihat ke kiri" dan membalikkan pencarian asli.
Dari sudut pandang VLOOKUP, kita dapat memvisualisasikan masalahnya seperti ini:
Solusi yang dijelaskan di bawah ini menggunakan fungsi CHOOSE untuk mengatur ulang tabel di dalam VLOOKUP.
Penjelasan
Dimulai dari awal, rumus di H5 adalah rumus VLOOKUP normal:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Menggunakan G5 sebagai nilai pencarian ("C"), dan data di B5: D8 sebagai larik tabel, VLOOKUP melakukan pencarian pada nilai di kolom B, dan mengembalikan nilai yang sesuai dari kolom 3 (kolom D), 3000. Perhatikan nol (0) disediakan sebagai argumen terakhir untuk memaksa pencocokan tepat.
Rumus di G10 hanya menarik hasil dari H5:
=H5 // 3000
Untuk melakukan pencarian terbalik, rumus di H10 adalah:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Sedikit rumit adalah fungsi PILIH, yang digunakan untuk mengatur ulang larik tabel sehingga Biaya menjadi kolom pertama, dan Opsi adalah yang terakhir:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
Fungsi CHOOSE dirancang untuk memilih nilai berdasarkan indeks numerik. Dalam kasus ini, kami menyediakan tiga nilai indeks dalam konstanta array:
(3,2,1) // array constant
Dengan kata lain, kita meminta kolom 3, lalu kolom 2, lalu kolom 1. Ini diikuti oleh tiga rentang yang mewakili setiap kolom tabel sesuai urutan kemunculannya di lembar kerja.
Dengan konfigurasi ini, CHOOSE mengembalikan ketiga kolom dalam satu larik 2D seperti ini:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Jika kami memvisualisasikan array ini sebagai tabel di lembar kerja, kami memiliki:
Catatan: judul bukan bagian dari larik dan hanya ditampilkan di sini untuk kejelasan.
Secara efektif, kami telah menukar kolom 1 dan 3. Tabel yang telah diatur ulang dikembalikan langsung ke VLOOKUP, yang cocok dengan 3000, dan mengembalikan nilai yang sesuai dari kolom 3, "C".
Dengan INDEX dan MATCH
Solusi di atas berfungsi dengan baik, tetapi sulit untuk direkomendasikan karena sebagian besar pengguna tidak akan memahami cara kerja rumus. Solusi yang lebih baik adalah INDEX dan MATCH, menggunakan rumus seperti ini:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Di sini, fungsi MATCH menemukan nilai 3000 di D5: D8, dan mengembalikan posisinya, 3:
MATCH(G10,D5:D8,0) // returns 3
Catatan: MATCH dikonfigurasi untuk pencocokan tepat dengan mengatur argumen terakhir ke nol (0).
MATCH mengembalikan hasil langsung ke INDEX sebagai nomor baris, sehingga rumusnya menjadi:
=INDEX(B5:B8,3) // returns "C"
dan INDEX mengembalikan nilai dari baris ketiga B5: B8, "C".
Rumus ini memperlihatkan bagaimana INDEX dan MATCH bisa lebih fleksibel daripada VLOOKUP.
Dengan XLOOKUP
XLOOKUP juga memberikan solusi yang sangat bagus. Rumus ekuivalennya adalah:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
Dengan nilai pencarian dari G10 (3000), larik al ookup D5: D8 (biaya) dan larik hasil B5: B8 (opsi), XLOOKUP menempatkan 3000 dalam larik pencarian, dan mengembalikan item yang sesuai dari larik hasil, "C". Karena XLOOKUP melakukan pencocokan tepat secara default, tidak perlu menyetel mode pencocokan secara eksplisit.
