An expanding reference is absolute on one side, and relative on the other.
Note the reference starts in D4, one rowabovethe first unique entry, in the unique list.
So, we start on the row above.
Important: be sure the heading for the unique list does not appear in the master list.
For the criteria in COUNTIF, we are using the master list itself.
When given multiple criteria, COUNTIF will return multiple results in anarray.
Now we have the arrays we need to find positions (row numbers).
For this, we use MATCH, set up for an exact match, to find zero values.
looking for items that do not yet appear in the unique list).
This works because MATCH always returns the first match when there are duplicates.
Extract items that appear just once
The LOOKUP formula above is easy to extend withboolean logic.
These formulas can be adapted toapply logical criteria.
it’s possible for you to use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
LOOKUP’s default behavior makes it useful for solving certain problems in Excel.
