Lookup last file version

To lookup the latest file version in a list, you can use a formula based on the LOOKUP function together with the ISNUMBER and FIND functions. In the example shown, the formula in cell G7 is: =LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files) where "files" is the named range B5:B11. Context In this example, we have a number of file versions listed in a table with a date and user name. Note that file names are repeated with a counter at the end as a revision number - 001, 002, 003, etc. Given a file name, we want to retrieve the name of the last or latest revision. There are two challenges: The challenge is the version codes at the end of the file names make it harder to match the file name. By default, Excel match formulas will return the first match, not the last match. To overcome these challenges, we need to use some tricky techniques.