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 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.
Array formula in cell F3:
Learn to add more criteria:
Find last matching value in an unsorted table
DonW asks: Ok, you've shown it for regular ranges....how about within tables. I have a table similar to: ID Name […]
Find last matching value in an unsorted table
Explaining the array formula
Step 1 - Find matching values
This 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"
returns
{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE}
TRUE means value is equal to cell E3.
Step 2 - Divide 1 with array
The LOOKUP function allows you to fetch the last value in an array if all other values are errors. 1/0 returns #DIV/0!.
1/{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE}
returns
{1;#DIV/0!;#DIV/0!;1; #DIV/0!;#DIV/0!; #DIV/0!;1;#DIV/0!}
Step 3 - Find last value in array
LOOKUP(2,1/(B3:B11=E3),C3:C11)
becomes
LOOKUP(2,1/{1;#DIV/0!;#DIV/0!;1; #DIV/0!;#DIV/0!; #DIV/0!;1;#DIV/0!},C3:C11)
Step 4 - Return corresponding value
LOOKUP(2,1/(B3:B11=E3),C3:C11)
becomes
LOOKUP(2,1/{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.
Download *.xlsx file
Convert array formula to a regular formula
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]
Find the longest/shortest consecutive sequence of a value
The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]
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 […]
Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]
How to perform a two-dimensional lookup
Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]
24 Responses to “Find last matching value in an unsorted list”
Leave a Reply to UsuarioExcel
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.
English
Spanish
=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)