Formula generik
=LOOKUP(2,1/(item="hat"),price)
Ringkasan
Untuk mencari harga terkini produk dalam senarai, disusun sehingga item terbaru muncul terakhir, anda boleh menggunakan formula berdasarkan fungsi LOOKUP. Dalam pertunjukan contoh, formula dalam G7 adalah:
=LOOKUP(2,1/(item=F7),price)
di mana item adalah julat bernama B5: B12, harga adalah julat bernama D5: D12, dan data disusun menaik mengikut tarikh.
Penjelasan
Fungsi LOOKUP mengandaikan data disusun, dan selalu melakukan padanan anggaran. Sekiranya nilai pencarian lebih besar daripada semua nilai dalam tatasusunan, tingkah laku lalai adalah "jatuh kembali" ke nilai sebelumnya. Rumus ini mengeksploitasi tingkah laku ini dengan membuat array yang hanya mengandungi 1s dan kesalahan, kemudian dengan sengaja mencari nilai 2, yang tidak akan pernah dijumpai.
Pertama, ungkapan ini dinilai:
item=F7
Apabila F7 mengandungi "sandal" hasilnya adalah susunan nilai BENAR dan SALAH seperti ini:
(FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)
Susunan ini disediakan sebagai pembahagi 1:
1/(FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)
Operasi matematik secara automatik memaksakan nilai BENAR dan SALAH menjadi 1s dan 0s, jadi hasilnya adalah susunan lain seperti ini:
(#DIV/0!;1;#DIV/0!;1;#DIV/0!;#DIV/0!;1;#DIV/0!)
kembali terus ke fungsi LOOKUP sebagai argumen vektor pencarian.
Perhatikan array hanya mengandungi dua nilai unik: ralat membahagi dengan sifar (# DIV / 0!) Dan nombor 1.
LOOKUP mencari array untuk nilai 2, mengabaikan nilai ralat. Tidak menjumpai 2, ia jatuh ke 1 terakhir, di kedudukan 7 di vektor pencarian. LOOKUP kemudian mengembalikan item ke-7 dalam vektor hasil (julat bernama "harga"), nilai 15.
Untuk membaca lebih lanjut mengenai konsep sengaja mencari nilai yang tidak akan pernah muncul, baca mengenai BigNum.
