Mencari Nilai Terakhir yang Cocok

Sebagian besar pencarian mengembalikan kecocokan pertama yang ditemukan. Namun, terkadang Anda menginginkan kecocokan terakhir: harga terbaru suatu produk, pembaruan status terkini, atau entri terakhir untuk seorang pelanggan.

Saat sebuah daftar bertambah seiring waktu dan kunci yang sama muncul berkali-kali, baris paling bawah biasanya merupakan data yang paling baru. VLOOKUP standar atau MATCH dengan pencocokan tepat akan tetap mengambil baris teratas.

Pelajaran ini menunjukkan beberapa cara andal untuk mengambil nilai yang cocok terakhir.

MATCH(value, range, 0) memindai dari atas ke bawah dan berhenti pada kecocokan tepat pertama. Jika "Apple" muncul di baris 2, 5, dan 9, MATCH mengembalikan 2.

Hal ini sempurna jika kuncinya unik, tetapi mengabaikan baris yang lebih baru. Untuk mencapai kemunculan terakhir, kita memerlukan teknik yang mencari dari bawah atau mengembalikan posisi kecocokan terakhir.

Jika Anda menggunakan versi Excel atau Google Sheets yang lebih baru, XLOOKUP mempermudah hal ini. Argumen kelima dan keenamnya mengatur mode pencocokan dan arah pencarian.

Masukkan -1 sebagai argumen mode pencarian untuk mencari dari terakhir ke pertama. XLOOKUP kemudian mengembalikan nilai yang terkait dengan kunci cocok paling bawah.

Di sini, fungsi tersebut mencari produk di G1 dalam A2:A10 dan mengembalikan harga yang cocok dari B2:B10, dengan memulai pencarian dari bawah.

Pada lembar sebar yang lebih lama, trik yang terkenal menggunakan LOOKUP dengan angka 2 dan pembagian berdasarkan kondisi.

Ekspresi 1/(A2:A10=G1) menghasilkan 1 untuk baris yang cocok dan kesalahan pembagian untuk baris yang tidak cocok. LOOKUP yang mencari 2 (nilai yang lebih besar daripada semua nilai yang ada) melewati kesalahan dan berhenti pada angka 1 valid terakhir, lalu mengembalikan nilai yang cocok dari B2:B10.

Telusuri 1/(A2:A10=G1) langkah demi langkah:

• Baris dengan kunci yang cocok menghasilkan 1/TRUE = 1.
• Baris yang tidak cocok menghasilkan 1/FALSE = kesalahan #DIV/0!.

LOOKUP mengabaikan kesalahan dan, saat tidak dapat menemukan targetnya (2), mengembalikan hasil yang sejajar dengan entri terakhir yang tidak mengalami kesalahan. Karena semua kecocokan bernilai 1, angka 1 terakhir yang dipilih, sehingga Anda mendapatkan nilai dari baris yang cocok terakhir.

Anda juga dapat tetap menggunakan keluarga INDEX-MATCH. Idenya adalah menemukan posisi kecocokan terakhir, lalu memasukkannya ke INDEX.

Dengan menggunakan trik pembagian yang sama di dalam MATCH, cari angka 2 pada 1/(A2:A10=G1) untuk mendapatkan posisi baris dari kecocokan terakhir. Masukkan posisi tersebut ke INDEX pada kolom hasil.

Jika argumen ketiga MATCH dihilangkan, nilai bawaannya adalah 1, yang berarti pencocokan perkiraan pada data menaik. MATCH kemudian mencari nilai terbesar yang kurang dari atau sama dengan 2.

Array 1/(A2:A10=G1) hanya berisi angka 1 dan kesalahan. Nilai terbesar yang kurang dari atau sama dengan 2 adalah 1, dan MATCH mengembalikan posisi angka 1 terakhir tersebut. Posisi itu tepat merupakan baris yang cocok terakhir.

Misalkan A2:A10 berisi status pesanan untuk "Order-7" yang dicatat dari waktu ke waktu, dan B2:B10 berisi teks statusnya. "Order-7" muncul di baris 3, 6, dan 9.

• Array pencocokan menandai baris 3, 6, dan 9 dengan angka 1, sedangkan baris lainnya berisi kesalahan.
• MATCH(2, ...) mengembalikan 9 sebagai posisi (dihitung dari awal rentang), yaitu kecocokan terakhir.
• INDEX mengembalikan status dari baris terakhir tersebut, yaitu status yang paling baru.

Pendekatan mana yang sebaiknya Anda gunakan?

• XLOOKUP dengan -1: paling rapi dan mudah dibaca jika didukung oleh aplikasi Anda.
• LOOKUP(2, 1/...): berfungsi hampir di semua tempat dan tidak memerlukan versi khusus.
• INDEX-MATCH(2, 1/...): berguna saat Anda juga memerlukan posisi atau ingin mengembalikan nilai dari kolom lain.

Ketiganya memberikan jawaban yang sama; pilih berdasarkan alat yang Anda gunakan dan tingkat keterbacaan rumus yang Anda inginkan.

Perhatikan masalah berikut:

• Ukuran rentang tidak sesuai: rentang kondisi dan rentang hasil harus memiliki tinggi yang sama agar baris tidak bergeser.
• Duplikat tersembunyi: spasi di akhir dapat membuat "Apple " berbeda dari "Apple"; bersihkan teks terlebih dahulu dengan TRIM.
• Tidak ada kecocokan sama sekali: trik ini menghasilkan kesalahan jika tidak ada yang cocok. Bungkus dengan IFERROR untuk menyediakan nilai pengganti yang informatif.

Anda dapat menggabungkan trik kecocokan terakhir dengan dua kondisi. Kalikan pengujian kondisi di dalam pembagian, sehingga hanya baris yang memenuhi kedua kunci yang menghasilkan angka 1.

Misalnya, cari harga terbaru ketika produk sama dengan G1 dan wilayah sama dengan G2. Trik LOOKUP(2, ...) tetap berhenti pada baris terakhir yang memenuhi syarat.

Ini berguna untuk catatan aktivitas bertanda waktu ketika produk yang sama muncul di beberapa wilayah.

Untuk mengembalikan nilai yang cocok terakhir, bukan yang pertama:

• Gunakan XLOOKUP(..., -1) untuk mencari dari bawah ke atas jika tersedia.
• Gunakan trik klasik LOOKUP(2, 1/(range=key), result) pada versi apa pun.
• Gunakan INDEX(result, MATCH(2, 1/(range=key))) jika Anda juga memerlukan posisinya.

Ingatlah untuk menjaga ukuran rentang tetap sama, membersihkan spasi yang tidak diperlukan, dan membungkus rumus dengan IFERROR untuk keamanan.