Explanation
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:
1/(ISNUMBER(FIND(G6,files)))
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:
{1;#VALUE!;1;1;#VALUE!;#VALUE!;1}
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:
{TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE}
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:
{1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;1}
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:
=IF(G6<>"",LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files),"")
Explanation
The LOOKUP function assumes data is sorted, and always does an approximate match. If the lookup value is greater than all values in the lookup array, default behavior is to “fall back” to the previous value. This formula exploits this behavior by creating an array that contains only 1s and errors, then deliberately looking for the value 2, which will never be found.
First, this expression is evaluated:
item=F7
When F7 contains “sandals” the result is an array of TRUE and FALSE values like this:
{FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}
This array is provided as the divisor to 1:
1/{FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}
The math operation automatically coerces the TRUE and FALSE values to 1s and 0s, so the result is another array like this:
{#DIV/0!;1;#DIV/0!;1;#DIV/0!;#DIV/0!;1;#DIV/0!}
returned directly to the LOOKUP function as the lookup vector argument.
Notice the array contains only two unique values: the divide by zero error (#DIV/0!) and the number 1.
LOOKUP searches the array for the value 2, ignoring the error values. Not finding 2, it falls back to the last 1, at position 7 in the lookup vector. LOOKUP then returns the 7th item in the result vector (the named range “price”), the value 15.
