Exceljet

Quick, clean, and to the point

Lookup last file revision

Excel formula: Lookup last file revision
Generic formula 
{=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1))}
Explanation 

To find the position (row) of the last file revision in a table, you can use a formula based on several Excel functions: MAX, IF, ISERROR, ROW, and INDEX.

In the example shown, the formula in cell H6 is:

{=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1))}

where "files" is the named range C4:C11.

Note: this is an array formula and must be entered with control + shift + enter.

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, except for the code appended at the end to represent version ("CA", "CB", "CC", "CD", etc.).

For a given file, we want to locate the position (row number) for the last revision.  This is a tricky problem, because the version codes at the end of the file names make it harder to match on the file name. Also, by default, Excel match formulas will return the first match, not the last match, so we need to work around that challenge with some tricky techniques. 

How this formula works

At the core of this formula, we build a list of row numbers for a given file. Then we use the MAX function to get the largest row number, which corresponds to the last revision (last occurrence) of that file.

To find all occurrences of a given file, we use the SEARCH function, configured with the asterisk (*) wildcard to match the file name, ignoring the version codes. SEARCH will throw a VALUE error when text isn't found, so we wrap search in ISERROR:

ISERROR(SEARCH(H5&"*",files))

This results in an array of TRUE and FALSE values like this:

{FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE}

It's confusing, but TRUE represents an error (text not found), and FALSE represents a match. This array result is fed into the IF function as the logical test. For value if TRUE, we use zero, and for value if true, we supply this code, which generates relative row numbers for the range we are working with:

ROW(files)-ROW(INDEX(files,1,1))+1)

The IF function then returns an array of values like this:

{1;0;3;4;0;0;7;0}

All numbers except zero represent matches for "filename1" – i.e. the row number inside the named range "files" where "filename1" appears.

Finally, we use the MAX function to get the maximum value in this array, which is 7 in this example.

Use INDEX with this row number to retrieve information related to the last revision (i.e. full file name, date, user, etc).

Without named range

Named ranges make it fast and easy to set up a more complex formula, since you don't have to enter cell addresses by hand. However, in this case, we are using an extra function (INDEX) to get the first cell of the named range "files", which complicates things a bit. Without the named range, the formula looks like this:

{=MAX(IF(ISERROR(SEARCH(H5&"*",C4:C11)),0,ROW(C4:C11)-ROW(C4)+1))}
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.