Formula umum
=LOOKUP(2,1/(ISNUMBER(FIND(filename,range))),range)
Ringkasan
Untuk mencari versi file terbaru dalam daftar, Anda bisa menggunakan rumus berdasarkan fungsi LOOKUP bersama dengan fungsi ISNUMBER dan FIND. Dalam contoh yang ditunjukkan, rumus di sel G7 adalah:
=LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files)
di mana "file" adalah rentang bernama B5: B11.
Konteks
Dalam contoh ini, kami memiliki sejumlah versi file yang tercantum dalam tabel dengan tanggal dan nama pengguna. Perhatikan bahwa nama file diulang dengan penghitung di bagian akhir sebagai nomor revisi - 001, 002, 003, dll.
Diberikan nama file, kami ingin mengambil nama revisi terakhir atau terbaru. Ada dua tantangan:
- Tantangannya adalah kode versi di akhir nama file membuat lebih sulit untuk mencocokkan nama file.
- Secara default, rumus kecocokan Excel akan mengembalikan kecocokan pertama, bukan kecocokan terakhir.
Untuk mengatasi tantangan ini, kita perlu menggunakan beberapa teknik yang rumit.
Penjelasan
Rumus ini menggunakan fungsi LOOKUP untuk menemukan dan mengambil nama file yang terakhir kali cocok. Nilai pencarian adalah 2, dan lookup_vector dibuat dengan ini:
1/(ISNUMBER(FIND(G6,files)))
Di dalam potongan ini, fungsi FIND mencari nilai di G6 di dalam rentang bernama "files" (B5: B11). Hasilnya adalah array seperti ini:
(1;#VALUE!;1;1;#VALUE!;#VALUE!;1)
Di sini, angka 1 menunjukkan kecocokan, dan kesalahan #VALUE menunjukkan nama file yang tidak cocok. Larik ini masuk ke fungsi ISNUMBER dan keluar seperti ini:
(TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE)
Nilai kesalahan sekarang SALAH, dan angka 1 sekarang BENAR. Ini mengatasi tantangan # 1, kami sekarang memiliki sebuah array yang menunjukkan dengan jelas file mana dalam daftar yang berisi nama file yang diinginkan.
Selanjutnya, array digunakan sebagai penyebut dengan 1 sebagai pembilangnya. Hasilnya terlihat seperti ini:
(1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;1)
yang masuk ke LOOKUP sebagai lookup_vector. Ini adalah solusi rumit untuk menantang # 2. Fungsi LOOKUP beroperasi dalam mode perkiraan kecocokan saja, dan secara otomatis mengabaikan nilai kesalahan. Artinya dengan 2 sebagai nilai pencarian, VLOOKUP akan mencoba menemukan 2, gagal, dan mundur ke angka sebelumnya (dalam hal ini cocok dengan 1 terakhir di posisi 7). Terakhir, LOOKUP menggunakan 7 seperti indeks untuk mengambil file ke-7 dalam daftar file.
Menangani pencarian kosong
Anehnya, fungsi FIND mengembalikan 1 jika nilai pencarian adalah string kosong (""). Untuk menjaga agar tidak terjadi kecocokan palsu, Anda dapat menggabungkan rumus dalam IF dan menguji pencarian kosong:
=IF(G6"",LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files),"")
