Explanation
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 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 the 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 with 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))}
Explanation
In this example, the goal is to mark or flag certain records in a data set based on one or more logical conditions . In each case, the result should be “Y” for “Yes” or “N” for “No”. The data represents drinks purchased by five people on different days. Note that most people appear more than once in the data. For each name, we are checking for three separate conditions:
- Have they purchased tea?
- Have they purchased coffee?
- Have they purchased both coffee and tea?
Note these conditions apply to all the data available for each person. To make the formulas easier to set up, we have two named ranges to work with: name (B5:B16) and drink (D5:D16). Also, note that we are using names as a unique identifier for convenience only. Normally, data like this will include some sort of unique ID for each person.
Coffee
To check if a person has ever purchased coffee, we need to look for a specific name in the same row as “coffee”. Essentially, we want to count rows where a given name appears with “coffee”. We can do this with the COUNTIFS function configured with two conditions like this:
COUNTIFS(name,B5,drink,"coffee") // returns 3
We are checking for the name in B5 (“Juan”) in names (B5:B16), and “coffee” in drinks (D5:D16). COUNTIFS joins these conditions with AND logic, so the result is a count of all records where the name is “Juan” and the drink is “coffee” (3). To get a result of “Y” for “Yes” or “N” for “No”, we nest the COUNTIFS function inside the IF function like this:
=IF(COUNTIFS(name,B5,drink,"coffee"),"Y","N")
Note we are using the COUNTIFS formula as the logical test inside the IF function. COUNTIFS won’t return TRUE and FALSE, but rather a number that represents a count. When supplied conditions are met, the result will be a non-zero number. If no records meet supplied conditions, COUNTIFS will return zero. Excel will evaluate any non-zero number as TRUE and zero as FALSE . In cell E5, the formula is solved like this:
=IF(COUNTIFS(name,B5,drink,"coffee"),"Y","N")
=IF(3,"Y","N")
="Y"
Note the result for “Juan” and “Coffee”, “Y”, will be the same in all rows where the name is “Juan”. This is a global test applied with all data considered.
Tea
To check if a person has ever purchased tea, we use the same approach. In this case, however, we want to check for rows where a given name appears with “tea”. The formula in cell F5 is:
=IF(COUNTIFS(name,B5,drink,"tea"),"Y","N")
Note the structure is the same as the formula in cell E5: COUNTIFS is used to count records that meet two conditions and delivers a numeric count to the IF function , which returns a final result. In cell F5, the formula is solved like this:
=IF(COUNTIFS(name,B5,drink,"tea"),"Y","N")
=IF(0,"Y","N")
="N"
Note in this case the count from COUNTIFS is zero, since Juan never purchased tea, and IF evaluates zero as FALSE and returns “N”.
Coffee and Tea
To check if a person has purchased both coffee and tea, we can use the AND function for the logical test. The formula in G5 is:
=IF(AND(E5="Y",F5="Y"),"Y","N")
Here, we are using the AND function to check the already calculated results in columns E and F for convenience and efficiency. For a standalone version of this formula, we could use the COUNTIFS function twice in one formula like this:
=IF(AND(COUNTIFS(name,B5,drink,"coffee"),COUNTIFS(name,B5,drink,"tea")),"Y","N")
As before, the AND function will evaluate any non-zero number as TRUE and zero as FALSE. In cell G5, the formula is evaluated like this:
=IF(AND(COUNTIFS(name,B5,drink,"coffee"),COUNTIFS(name,B5,drink,"tea")),"Y","N")
=IF(AND(3,0),"Y","N")
=IF(FALSE,"Y","N")
="N"
The result is “N” because although Juan has purchased coffee 3 times, he has never purchased tea.
