## Find last matching value in an unsorted table

Ok, you've shown it for regular ranges....how about within tables.

I have a table similar to:

ID Name Date

1001 Joe Smith 5/1/2017

1002 John Doe 5/2/2017

1001 Joe Smith 5/17/2017

1003 Jane Doe 5/18/2017

1001 Joe Smith 5/20/2017

The formula below lets you search for criteria and return the last matching record in the table.

Example, 1001 and Joe Smith is found on row 3,5 and 7. The record on row 7 is the last record in the table so the formula returns the date (2017-05-17) from row 7, in cell G4.

**Formula in cell G4:**

This formula is weird, you don't need to enter it as an array formula as you should. I don't know why, however this gave me an idea, check it out here:

Create a list of unique distinct values

Recommended article

**Find last matching value in an unsorted list**

Comments(22) Filed in category: Excel, Lookup function

#### Explaining formula in cell G4

**Step 1 - Construct first logical expression (ID)**

We want to find all to find values in column B equal to 1001.

B3:B12=G2

becomes

{1001;1002;1001;1003;1001;1003;1002;1002;1003;1002}=1001

and returns

{TRUE;FALSE;TRUE;FALSE;TRUE; FALSE;FALSE;FALSE;FALSE;FALSE}

**Step 2 - Construct second logical expression (NAME)**

The second logical expressions checks if values in cell range C3:C12 is equal to "Joe Smith"

C3:C12=G3

becomes

{"Joe Smith";"John Doe";"Joe Smith";"Jane Doe";"Joe Smith";"Jane Doe";"John Doe";"John Doe";"Jane Doe";"John Doe"}="Joe Smith"

and returns

{TRUE;FALSE;TRUE;FALSE;TRUE; FALSE;FALSE;FALSE;FALSE;FALSE}

**Step 3 - Add arrays**

Both conditions must be met so we must multiply (*) the arrays. If we wanted at least one of two conditions met we would add the arrays (+)

(B3:B12=G2)*(C3:C12=G3)

becomes

{TRUE;FALSE;TRUE;FALSE;TRUE; FALSE;FALSE;FALSE;FALSE;FALSE} * {TRUE;FALSE;TRUE;FALSE;TRUE; FALSE;FALSE;FALSE;FALSE;FALSE}

and returns

{1;0;1;0;1;0;0;0;0;0}

**Step 4 - Dividing by zero returns an error**

The LOOKUP function allows us to match a value if it is sorted ascending, however it also lets you match the last value in an array if the others are errors.

1/((B3:B12=G2)*(C3:C12=G3))

becomes

1/{1;0;1;0;1;0;0;0;0;0}

and returns

{1;#DIV/0!;1;#DIV/0!; 1;#DIV/0!; #DIV/0!; #DIV/0!;#DIV/0!; #DIV/0!}

1/0 returns #DIV/0!

**Step 5 - Find last value in array**

The LOOKUP function finds the last value in the array and returns the corresponding value on the same row in cell range D3:D12.

LOOKUP(2,1/((B3:B12=G2)*(C3:C12=G3)),D3:D12)

becomes

LOOKUP(2,{**1**;#DIV/0!;**1**;#DIV/0!; **1**;#DIV/0!; #DIV/0!; #DIV/0!;#DIV/0!; #DIV/0!},D3:D12)

becomes

LOOKUP(2,{1;#DIV/0!;1;#DIV/0!; 1;#DIV/0!; #DIV/0!; #DIV/0!;#DIV/0!; #DIV/0!},{**42856**;42857;**42875**; 42873;**42872**;42858; 42877;42857;42860; 42882})

and returns 2017-05-17 (42872) in cell G4.

Comments(0) Filed in category: Excel, Lookup function

#### Download excel *.xlsx file

### Category: Lookup function

How to return a value if lookup value is in range

Question: Hi, What type of formula could be used if you weren't using a date range and your data was […]Comments(23) Filed in category: Excel, Lookup function, Vlookup

Find last matching value in an unsorted list

I read an interesting blog post Find Last Item in Group With Index Match written by Debra Dalgleish. It is about […]Comments(22) Filed in category: Excel, Lookup function

Comments(1) Filed in category: Excel, Lookup function

Comments(0) Filed in category: Excel, Lookup function