#Excel: Finding and Visualizing the Last Record in a Table Based on Criteria by David Hager

A tweet by Tom Urtis intrigued me.

https://twitter.com/TomUrtis/status/863092107564638208

I used to play with the 3^rd argument of the MATCH function many years ago, but I gave it up as a lost cause because strange results were returned if the column in question had blank cells in it. However, if used in a contiguous list, Tom demonstrated its utility in the following formula.

=MATCH(2,1/(Table1[Name]=”Urtis”))

If the 3^rd argument of the MATCH is not declared, it defaults to a value of 1. That allows for a lookup that is equal to or less than the 1^st argument. The undocumented feature of the 3^rd argument is that when the value is 1 it does the lookup from the bottom of the data rather than the top. So, Tom’s formula finds the LAST matching item (in cell A44 in the example workbook).

I realized that this technique could be extended to multiple criteria. The following formula shows 2 criteria

=MATCH(2,1/((Table1[Name]=”Urtis”)*(Table1[Type]=”b”)))

and this formula shows 3 criteria.

=MATCH(2,1/((Table1[Name]=”Urtis”)*(Table1[Type]=”b”)*(Table1[Total]<300)))

It is important to note that the criteria can be from any column in a table, and just not adjacent rows.

Note also that the formulas return the row position in the table.

This technique can also be used for visualizing the row matching the criteria by conditional formatting (CF).

This is the conditional formatting formula used for 3 criteria.

Criteria3=ROW()=ROW(INDIRECT(“A”&MATCH(2,1/((Table1[Name]=”Urtis”)*(Table1[Type]=”b”)*(Table1[Total]<300)))+1))

This CF formula is applied to the entire table and highlights row 21 as expected.

As a further extension of this technique, a criteria table can be used that replaces the static criteria with values from the table, as shown below.

=MATCH(2,1/(Table1[Name]=F2))

=MATCH(2,1/((Table1[Name]=F2)*(Table1[Type]=G2)))

=MATCH(2,1/((Table1[Name]=F2)*(Table1[Type]=G2)*(Table1[Total]<H2)))

So, there are a number of ways to use this technique. Thanks Tom, for the idea.

You can download the file here.

LastRowCrit