Jika Anda telah membaca tips Excel untuk beberapa waktu, Anda pasti telah menemukan seseorang yang berbicara tentang penggunaan fungsi Excel INDEX () & MATCH () dan bukan Excel VLOOKUP. Berbicara sendiri, selalu terlalu sulit untuk mencoba dan menguasai DUA fungsi baru secara bersamaan. Tapi, itu trik yang bagus. Beri saya lima menit dan saya akan mencoba menjelaskannya dalam bahasa Inggris sederhana.
Ulasan 30 detik VLOOKUP
Katakanlah Anda memiliki tabel catatan karyawan. Kolom pertama adalah nomor karyawan, dan kolom sisanya adalah berbagai data tentang karyawan. Setiap kali Anda memiliki nomor karyawan di lembar kerja, Anda bisa menggunakan VLOOKUP untuk mengembalikan data tertentu tentang karyawan tersebut. Sintaksnya adalah VLOOKUP (nilai, rentang data, nomor kolom, FALSE). Dikatakan pada Excel, "Pergi ke rentang data. Temukan baris yang memiliki (nilai) di kolom pertama rentang data. Kembalikan nilai (nomor kolom) dari baris itu. Setelah Anda memahaminya, itu sangat sederhana dan kuat.
Masalah
Suatu hari, Anda mengalami situasi di mana Anda memiliki nama karyawan, tetapi membutuhkan nomor karyawan. Pada gambar berikut, Anda memiliki nama di A10 dan perlu menemukan nomor karyawan di B10.
Jika bidang kunci berada di sebelah kanan data yang ingin Anda ambil, VLOOKUP tidak akan berfungsi. Jika hanya VLOOKUP yang menerima -1 sebagai nomor kolom, tidak akan ada masalah. Tapi ternyata tidak. Salah satu solusi umum adalah memasukkan sementara kolom baru A, menyalin kolom nama ke kolom baru A, mengisi dengan VLOOKUP, Menempelkan Nilai Khusus, lalu menghapus kolom sementara A. Profesional Excel mungkin dapat melakukan langkah ini dalam tidur mereka.
Saya akan menyarankan Anda untuk mengambil tantangan dan mencoba menggunakan metode satu langkah ini. Ya, Anda harus menempelkan formula di dinding Anda selama beberapa minggu, tetapi Anda melakukannya dengan VLOOKUP sejak lama juga, bukan?
Saya pikir alasan mengapa hal ini begitu sulit adalah karena Anda menggunakan dua fungsi yang mungkin belum pernah Anda gunakan sebelumnya. Jadi, izinkan saya memecahnya menjadi dua bagian.
Pertama, ada fungsi INDEX (). Ini adalah fungsi bernama mengerikan. Ketika seseorang mengatakan "indeks", itu tidak membayangkan apa pun dalam pikiran saya yang mirip dengan fungsi ini. Indeks membutuhkan tiga argumen.
=INDEX(data range, row number, column number)
Dalam bahasa Inggris, Excel masuk ke rentang data dan mengembalikan Anda nilai di persimpangan baris (nomor baris) dan kolom (nomor kolom). Hei, pikirkanlah - ini cukup sederhana, bukan? =INDEX($A$2:$C$6,4,2)akan memberi Anda nilai dalam B5.
Menerapkan INDEX () untuk masalah kita, Anda bisa mengetahui bahwa untuk mengembalikan jumlah karyawan dari jangkauan, Anda akan menggunakan ini: =INDEX($A$2:$A$6,?,1). Sebenarnya, bagian ini terlihat sangat sepele sehingga terlihat tidak berguna. Namun, saat Anda mengganti tanda tanya dengan fungsi MATCH (), Anda memiliki solusinya.
Berikut sintaksnya:
=MATCH(Value, Single-column data range, FALSE)
Ini memberi tahu Excel, "Cari rentang data dan beri tahu saya nomor baris relatif tempat Anda menemukan kecocokan untuk (data). Jadi, untuk menemukan baris mana yang memiliki karyawan di A10, Anda akan menggunakan =MATCH(A10,$B$2:$B$6,FALSE). Ya, ini lebih kompleks daripada Indeks , tetapi harus tepat di gang pro VLOOKUP. Jika A10 berisi "Miller, Bob" maka MATCH ini akan mengembalikan bahwa dia berada di baris ke-3 dari rentang B2: B6.
Itu dia - fungsi MATCH () memberi tahu fungsi Indeks baris mana yang harus dicari - Anda sudah selesai. Ambil fungsi Indeks, ganti tanda tanya kami dengan fungsi MATCH, dan sekarang Anda dapat melakukan persamaan VLOOKUP saat bidang kunci tidak ada di kolom kiri. Berikut fungsi yang akan digunakan:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Catatan tempel di dinding saya sebenarnya menunjukkannya sebagai dua baris. Pertama saya menulis penjelasan untuk MATCH (). Di bawah ini saya menulis penjelasan untuk INDEX (). Saya kemudian menggambar bentuk corong di antara keduanya untuk menunjukkan bahwa fungsi MATCH () masuk ke argumen ke-2 dari fungsi INDEX ().
Beberapa kali pertama saya harus melakukan salah satu dari ini, saya tergoda untuk membanting kolom A sementara baru di sana, tetapi mengalami rasa sakit karena melakukannya dengan cara ini. Ini lebih cepat, dan membutuhkan lebih sedikit manipulasi. Jadi, lain kali Anda ingin memasukkan angka negatif ke dalam fungsi VLOOKUP, coba kombinasi INDEX dan MATCH yang aneh ini untuk menyelesaikan masalah Anda.
