Formula umum
=XLOOKUP(max,dates,results,,-1) // latest match by date
Ringkasan
Untuk mendapatkan kecocokan terbaru dalam satu set data berdasarkan tanggal, Anda dapat menggunakan XLOOKUP dalam mode perkiraan kecocokan dengan mengatur match_mode ke -1. Dalam contoh yang ditunjukkan, rumus di G5, disalin ke bawah, adalah:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
di mana tanggal (C5: C15), item (B5: B15) dan harga (D5: D15) diberi nama rentang.
Penjelasan
XLOOKUP menawarkan beberapa fitur yang membuatnya sangat bagus untuk pencarian yang lebih rumit. Dalam contoh ini, kami menginginkan harga terbaru untuk item berdasarkan tanggal. Jika data diurutkan berdasarkan tanggal dalam urutan menaik, ini akan sangat mudah. Namun, dalam kasus ini, data tidak disortir.
Secara default, XLOOKUP akan mengembalikan kecocokan pertama dalam kumpulan data. Untuk mendapatkan kecocokan terakhir, kita dapat menyetel argumen opsional search_mode, ke -1 agar XLOOKUP mencari "terakhir ke pertama". Namun, kami tidak dapat menggunakan pendekatan ini di sini karena tidak ada jaminan bahwa harga terbaru untuk suatu item muncul terakhir.
Sebagai gantinya, kita dapat menyetel argumen opsional match_mode ke -1 untuk memaksa perkiraan kecocokan "tepat atau terkecil berikutnya", dan menyesuaikan nilai pencarian dan array pencarian seperti yang dijelaskan di bawah ini. Rumus di G5, disalin, adalah:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
Bekerja melalui argumen satu per satu, lookup_value adalah tanggal terbesar (terbaru) dalam data:
MAX(date) // get max date value
Lookup_array diturunkan dengan ekspresi logika boolean:
(item=F5)*date
Dengan membandingkan setiap item dengan nilai di F5, "Belt", kita mendapatkan larik nilai TRUE / FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE)
di mana nilai TRUE mewakili entri untuk "Belt". Array ini bertindak seperti filter. Jika dikalikan dengan nilai pada tanggal rentang bernama , nilai TRUE / FALSE dievaluasi menjadi 1 dan 0:
=(1;0;0;0;0;0;1;0;1;0;0)*date
Hasilnya adalah larik yang hanya berisi angka nol dan tanggal untuk sabuk:
=(43484;0;0;0;0;0;43561;0;43671;0;0)
Catatan: nomor seri adalah tanggal Excel yang valid.
Larik ini dikirim langsung ke XLOOKUP sebagai argumen lookup_array.
Return_array adalah kisaran bernama harga (D5: D15)
Argumen opsional not_found tidak disediakan.
Match_mode disetel ke -1, untuk pencocokan tepat, atau item terkecil berikutnya.
XLOOKUP melihat melalui array pencarian untuk nilai tanggal maksimum. Karena larik telah disaring untuk mengecualikan tanggal yang tidak terkait dengan "Belt", XLOOKUP hanya menemukan kecocokan terbaik (baik tanggal tepat, atau tanggal terkecil berikutnya) yang sesuai dengan tanggal terakhir.
Hasil akhirnya adalah harga yang terkait dengan tanggal terbaru. Rumus akan terus berfungsi saat data diurutkan dalam urutan apa pun.
