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 lookup value in cell E3 and the formula in cell F3.
The formula uses the condition in cell E3 to find the last matching value in cell range B3:B11 and returns the corresponding value on the same orw from cell range F3:F11.
I read an interesting blog post Find Last Item in Group With Index Match written by Debra Dalgleish. It is about finding the last matching value in a sorted list. It got me thinking how to find the last matching item in an unsorted list.
This picture shows random text strings in column B and values in column C. Cell E3 contains the search value and F3 contains an array formula that returns the last matching value in a list.
Formula in cell F3:
This formula is a regular formula if you are an Excel 365 subscriber, however, if you use an earlier Excel version you need to enter the formula as an array formula.
How to enter an array formula
- Copy the above formula.
- Double press with left mouse button on with the left mouse button on cell F3.
- Paste formula to cell.
- Press and hold CTRL + SHIFT simultaneously.
- Press Enter once.
- Release all keys.
Learn to add more criteria:
Recommended articles
This article shows a formula that performs a reverse lookup and returns the corresponding value based on the last matching […]
Explaining the formula in cell F3
The Evaluate Formula tool lets you see formula calculations in greater detail. Press with left mouse button on with left mouse button on cell F3 to select it. Go to the "Formula" tab on the ribbon. Press with left mouse button on the "Evaluate Formula" button and a dialog box appears, see above image.
You can now examine and troubleshoot the formula using the "Evaluate" button on the dialog box. It will, step by step, go through the calculation. The underlined expression is what is about to be evaluated and the italicized expression is the result of the most recent evaluation.
Keep press with left mouse button oning the "Evaluate" button to see all calculations. You will see the final result when all calculations are made, that value will match the value returned in cell F3. The "Close" button dismisses the dialog box when you are done evaluating.
Step 1 - Find matching values
The logical operators allow you to create a logical expression, they are : = < > and can also be combined.
= equal
> larger than
< smaller than
<> not equal to
=> larger than or equal to
=< smaller than or equal to
A logical expression returns a boolean value TRUE or FALSE. Note, they have numerical equivalents. TRUE anything but zero and FALSE = 0 (zero).
The following logical expression returns an array corresponding to cell range B3:B11.
B3:B11=E3
becomes
{"SV"; "AD"; "WE"; "SV"; "SX"; "HJ"; "KL"; "SV"; "XC"}="SV"
Arrays has delimiting characters, a ; (semicolon) means that the values are in a column. A , (comma) separates values in a row. An array may contain values from a cell range containing multiple rows and columns, this means that the array contains both ; and , to indicate their positions in the cell range.
returns
{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE}
TRUE means that the value is equal to the value in cell E3.
Step 2 - Divide 1 with array
The LOOKUP function lets you find a value in a cell range and return a corresponding value on the same row, however, it also ignores error values and returns the last match which is surprising. I will show you how in step 3.
LOOKUP(lookup_value, lookup_vector, [result_vector])
The [result_vector] argument is optional. To create an error replacing the boolean value FALSE I simply divide 1 with the array.
1/(B3:B11=E3)
The parentheses allow you to control the order of operation meaning we want the formula to first calculate the logical expression and then divide 1 with the resulting array.
1/(B3:B11=E3)
becomes
1/{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE}
and returns
{1; #DIV/0!; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1; #DIV/0!}
The #DIV/0! error is returned if you try to divide something with zero which is not possible.
The above image shows the array in cell range D3:D11, Excel shows a green triangle in the top left corner indicating that the value is an error value.
Step 3 - Find last value in array
The lookup_value must be larger than the values in the loop_vector and the values in the lookup_vector must be the same in order to get the last value that matches the lookup_value.
LOOKUP(lookup_value, lookup_vector, [result_vector])
LOOKUP(2,1/(B3:B11=E3),C3:C11)
becomes
LOOKUP(2,{1; #DIV/0!; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1; #DIV/0!},C3:C11)
becomes
LOOKUP(2,{1; #DIV/0!; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1; #DIV/0!},{25; 20; 30; 80; 50; 60; 70; 40; 90})
Step 4 - Return corresponding value
LOOKUP(2,1/(B3:B11=E3),C3:C11)
becomes
LOOKUP(2, {1;#DIV/0!;#DIV/0!;1; #DIV/0!;#DIV/0!; #DIV/0!;1;#DIV/0!}, {25; 20; 30; 80; 50; 60; 70; 40; 90})
and returns 40 in cell F3.
The last value matching the condition has the relative position eight in the array, the corresponding value in cell range C3:C11 is 40.
Recommended reading
Find the Last Occurrence of a Lookup Value
Lookups category
This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]
Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]
Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]
Excel categories
24 Responses to “Find last matching value in an unsorted list”
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.
=INDEX(C2:C11,MAX(IF(B2:B11=E3,ROW(B2:B11)-1)))
sam,
I try to make formulas that work in any cell without any user interaction. That is why my formulas are sometimes a bit longer.
Thanks for commenting!
If you have Excel 2010> use the INDEX & AGGREGATE for a non array formula.
=INDEX(C3:C11,AGGREGATE(14,6,ROW(B3:B11)-ROW(B2)/(B3:B11=E3),1))
Kevin,
Interesting formula, thank you for commenting.
Sam,
your formula is not robust. If any row inserted above the table it will produce inaccurate value.
Oscar,
my array version is
=INDEX($C$3:$C$11,MATCH(1,--($E$3=$B$3:$B$11),1))
or, for those who is afraid of array formula,
=INDEX($C$3:$C$11,MATCH(1,INDEX(--($E$3=$B$3:$B$11),),1))
I like the idiom MATCH(ROW($B$3:$B$11),ROW($B$3:$B$11)) you used to generate a numeric sequence, but ROW($B$3:$B$11)-ROW($B$2) should be quicker.
Leonid,
I can´t get your formula working. If I change the value in cell B10 this happens, see picture:
Good catch. This slight modification should fix the problem:
array
=INDEX(C3:C11,MATCH(1,1/(E3=B3:B11),1))
non array
=INDEX(C3:C11,MATCH(1,INDEX(1/(E3=B3:B11),),1))
Leonid,
Thank you for your solution.
I don´t understand why this works
MATCH(1, {1; #DIV/0!; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!}, 1)
but this does not
MATCH(1,{1;0;0;1;0;0;0;0;0},1)
[…] Recently, I shared a formula for finding the last item in a category, in a sorted list. Oscar created a formula that works with an unsorted list. […]
Hi
=LOOKUP(2,1/($B$3:$B$11=E3),$C$3:$C$11)
Xlarium..Thanks! Works perfectly!
Is there a way to specify which kth largest/smallest value to return?
Thanks for this. Very useful. Only thing is that it returns a result (10) even if the search value does not appear in the list. Any thoughts?
Hello,
I only comment to say thaaank you so much!!. Very useful.
Only one thing. You should advertise that we have to press CONTROL+SHIFT+ENTER, instead of ONLY ENTER, to add the matriz formula.
How to do the same with double criteria.
Month Text Value
1 SV 10
1 AD 20
1 kl 30
1 SV 40
2 SX 50
2 HJ 60
2 KL 150
3 SV 80
3 XC 90
3 SV 50
3 ab 90
3 SV 70
I need below result.
Month Text Value:
3 sv 70
OR 2 KL 150
hi
See solution below
=INDEX(D1:D12,(MAX((C1:C12="sv")*(B1:B12=3)*ROW(C1:C12))))
Where Column B= Months Column C= Text and Column D = Values
I have a similar problem,
Im trying to make an excel where column A is a drop down box and the first 30 odd rows Column F finds a value from a different page i done this with VLookup all works find but now when i type in 'SV on column A i want column F to find the last value from SV on the same page, but there will be no orders to it- i am trying to do a stock take page when i order something in it gets added to stock if i ship something out it gets taken away from the value in column F
how can I use this formula with multiple criteria? I've already managed to do the simple one but I would like to find the last value of something by month.
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
DonW,
Check this out:
https://www.get-digital-help.com/2017/08/15/find-last-matching-value-in-an-unsorted-table/
I need a way to find the last non empty value on an entry based on month columns.
https://imgur.com/a/Sf5YvUI
So as per above Image,
I had used =LOOKUP(2,1/($A:$A=$D$3),$B:$B) this formula to get value for month but for Jan it showing correct but for Month Feb it showing Value 0 instead of ABS-143-002 because last value for Month Feb empty cell.
I tried too much but not getting perfect answer for this
Please, help me with this I need non empty last value from column B for selected month without using VBA.
Gopi Sahane,
try this formula:
=LOOKUP(2,1/(($A1:$A1000=$D$3)*($B1:$B1000<>"")),$B1:$B1000)