This formula uses the LOOKUP function to find and retrieve the last matching file name. The lookup value is 2, and the lookup_vector is created with this:
Inside this snippet, the FIND function looks for the value in G6 inside the named range "files" (B5:B11). The result is an array like this:
Here, the number 1 represents a match, and the #VALUE error represents a non-matching file name. This array goes into the ISNUMBER function and comes out like this:
Error values are now FALSE, and the number 1 is now TRUE. This overcomes challenge #1, we now have an array that shows clearly which files in the list contain the file name of interest.
Next, the array is used as the denominator with 1 as numerator. The result looks like this:
which goes into LOOKUP as the lookup_vector. This is a tricky solution to challenge #2. The LOOKUP function operates in approximate match mode only, and automatically ignores error values. This means with 2 as a lookup value, VLOOKUP will try to find 2, fail, and step back to the previous number (in this case matching the last 1 in position 7). Finally, LOOKUP uses 7 like an index to retrieve the 7th file in the list of files.
Handling blank lookups
Oddly, the FIND function returns 1 if the lookup value is an empty string (""). To guard against a false match, you can wrap the formula in IF and test for an empty lookup: