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
This article demonstrates a formula that returns the last matching value based on a given condition. The above image shows […]
Find last matching value in an unsorted list
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.
How to use the LOOKUP function
Finds a value in a sorted cell range and returns a value on the same row.
How to use the LOOKUP function
Download excel *.xlsx file
5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
How to return a value if lookup value is in a range
In this article, I will demonstrate four different formulas that allow you to lookup a value that is to be found […]
Find last matching value in an unsorted list
This article demonstrates a formula that returns the last matching value based on a given condition. The above image shows […]
The formula in cell D3 lets you get the last value in column B, it works fine with blank cells […]
How to use the LOOKUP function
Finds a value in a sorted cell range and returns a value on the same row.
Filter values based on numerical ranges
Eero S asks: Thank you *so* much for your detailed examples and actively replying to users! I have a problem, […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.