All the Formulas you Ever Wanted
In the sections below we will look at how to implement the following:
Multi-Condition VLOOKUP
Perform a VLOOKUP, INDEX-MATCH, HLOOKUP, LOOKUP or XLOOKUP with multiple match conditions. Skip to section ⇒
Multi-Match VLOOKUP
VLOOKUP will only return the top hit. XLOOKUP goes one step further and allows you to search up and return the last hit, but what about the second hit, or all of them spread out over multiple columns. Skip to section ⇒
Try it Out
An Excel workbook with all the above examples can be downloaded here. Give it a try yourself.
Try it Out
An Excel workbook with all the above examples can be downloaded here. Give it a try yourself.
Multi-Condition VLOOKUP
MULTI-CONDITION VLOOKUP
Perform a VLOOKUP, INDEX-MATCH, HLOOKUP, LOOKUP or XLOOKUP with multiple match conditions. Skip to section …
Multi-Match VLOOKUP
VLOOKUP, INDEX-MATCH and even the new XLOOKUP all return data from the first row which matches the criteria. XLOOKUP adds the feature to search up, i.e. it will return the last matching row, but this doesn’t help when there are an unknown number of hits.
If you want to combine all matches into a single cell, then you can use the “Concatifs”, covered in this earlier post.
However in this post we are going to split each match accross columns. See the image above where the three destination columns are labelled 1, 2, and 3.
The way this is done is again to use FILTER to return the rows we want. Following this we use INDEX to select the first, second, third, etc hit from the result. Finally, for neatness, we surround the whole with an IFERROR to blank out any cells where there isn’t a hit (e.g. Project D does not have a second member, so produces a #REF! error in the Second Member column).
The formula used in the image above is:
=IFERROR(INDEX(FILTER(MultiMatchSource[Member],MultiMatchSource[Project]=$F8),G$7),"")
where “ProjectMember” is the name of the blue table on the left, cell F8 is the cell containing the A in the column Project of the right hand (green) table, and cell G7 refers to the 1 in the header row of the green table.
We use the reference to the 1, 2, and 3 in the header to give us the first, second and third hits. We could alternatively use the either the COLUMN or COLUMNS functions to generate the number from the position of the cell. For example:
=IFERROR(INDEX(FILTER(MultiMatchSource[Member],MultiMatchSource[Project]=$F18),COLUMNS($G$17:G$17)),"")
We did say that we’d try not to avoid functions Spilling (occupying more than one cell) because that behaviour is weird. The above function therefore picks individual entries out of the filtered results using the INDEX function.
However, there is a cleaner way, simply use TRANSPOSE to flip a column of filtered data into a row. For example, the same effect seen in the image above can be achieved using the following formula:
=TRANSPOSE(FILTER(MultiMatchSource[Member],MultiMatchSource[Project]=$F28))
MIN/MAX/LARGE/SMALL with Non-Numeric Data
First name alphabetically, seventh name alphabetically, no problem. Skip to section …
Data associated with the MIN, MAX or Nth Largest
We can work out the biggest value with MAX, but we do not know what row that came from, so we have to do another lookup etc. Do it all with one Formula! Skip to section …
