Công thức chung
=LOOKUP(2,1/(ISNUMBER(FIND(filename,range))),range)
Tóm lược
Để tra cứu phiên bản tệp mới nhất trong danh sách, bạn có thể sử dụng công thức dựa trên hàm LOOKUP cùng với các hàm ISNUMBER và FIND. Trong ví dụ được hiển thị, công thức trong ô G7 là:
=LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files)
trong đó "tệp" là phạm vi được đặt tên B5: B11.
Bối cảnh
Trong ví dụ này, chúng tôi có một số phiên bản tệp được liệt kê trong bảng với ngày tháng và tên người dùng. Lưu ý rằng tên tệp được lặp lại với bộ đếm ở cuối là số sửa đổi - 001, 002, 003, v.v.
Với một tên tệp, chúng tôi muốn truy xuất tên của bản sửa đổi gần đây nhất hoặc mới nhất. Có hai thách thức:
- Thách thức là mã phiên bản ở cuối tên tệp khiến việc khớp với tên tệp khó hơn.
- Theo mặc định, các công thức so khớp trong Excel sẽ trả về kết quả khớp đầu tiên, không phải kết quả khớp cuối cùng.
Để vượt qua những thách thức này, chúng ta cần sử dụng một số kỹ thuật phức tạp.
Giải trình
Công thức này sử dụng hàm LOOKUP để tìm và truy xuất tên tệp phù hợp cuối cùng. Giá trị tra cứu là 2 và lookup_vector được tạo bằng:
1/(ISNUMBER(FIND(G6,files)))
Bên trong đoạn mã này, hàm FIND tìm kiếm giá trị trong G6 bên trong phạm vi được đặt tên là "tệp" (B5: B11). Kết quả là một mảng như thế này:
(1;#VALUE!;1;1;#VALUE!;#VALUE!;1)
Ở đây, số 1 đại diện cho một sự trùng khớp và lỗi #VALUE đại diện cho một tên tệp không khớp. Mảng này đi vào hàm ISNUMBER và xuất hiện như sau:
(TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE)
Giá trị lỗi hiện là FALSE và số 1 hiện là TRUE. Điều này đã vượt qua thử thách số 1, bây giờ chúng ta có một mảng hiển thị rõ ràng tệp nào trong danh sách chứa tên tệp quan tâm.
Tiếp theo, mảng được sử dụng làm mẫu số với 1 là tử số. Kết quả trông như thế này:
(1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;1)
chuyển vào LOOKUP dưới dạng lookup_vector. Đây là một giải pháp khó cho thử thách số 2. Hàm LOOKUP chỉ hoạt động ở chế độ đối sánh gần đúng và tự động bỏ qua các giá trị lỗi. Điều này có nghĩa là với 2 là giá trị tra cứu, hàm VLOOKUP sẽ cố gắng tìm 2, không thành công và lùi về số trước đó (trong trường hợp này khớp với 1 cuối cùng ở vị trí 7). Cuối cùng, LOOKUP sử dụng 7 giống như một chỉ mục để truy xuất tệp thứ 7 trong danh sách các tệp.
Xử lý các tra cứu trống
Thật kỳ lạ, hàm FIND trả về 1 nếu giá trị tra cứu là một chuỗi rỗng (""). Để đề phòng kết hợp sai, bạn có thể bọc công thức trong IF và kiểm tra tìm kiếm trống:
=IF(G6"",LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files),"")
