#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 3rd 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 3rd 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 1st argument. The undocumented feature of the 3rd 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.

RowCrit1

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

Advertisements

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

  1. Kevin Lehrbass

    Hi David,
    Similar to you, I’ve always been intrigued by Tom’s formula but never used it at work. I would normally use an array like this:
    =MAX(IF((Table1[Name]=”Urtis”)*(Table1[Type]=”b”)*(Table1[Total]<300),ROW(Table1[Name]),""))
    or possibly like this
    =MAX(IF((Table1[Name]="Urtis")*(Table1[Type]="b")*(Table1[Total]<300),ROW(Table1[Name])-ROW(Table1[[#Headers],[Type]]),""))
    or even a helper column approach if the audience prefers a non array solution.
    Thanks for exploring Tom's MATCH true array as it deserves a second look. It's a shorter formula and it's always good to understand how it works for possible auditing purposes.
    Cheers,
    Kevin Lehrbass

    Reply
  2. Pingback: #Excel For You Blog Posts Having Downloadable Workbook Files by David Hager | Excel For You

  3. Pingback: #Excel Super Links #36 – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s