## Find last matching value in an unsorted list

*Article last updated on January 20, 2018*

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 […]

### 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

Kamran Mumtaz asked: Is there any way to use VLOOKUP for multiple criteria and I do not want to use CSE?; […]

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 […]

How to use the AGGREGATE function

The AGGREGATE function allows you to calculate different specific functions to a list or database. A special AGGREGATE function feature […]

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

How to return a value if lookup value is in a range

In this article, I will demonstrate ways to lookup values that is to be found between given ranges and return […]

Extract unique distinct year and months from dates

Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]

The formula in cell D3 lets you get the last value in column B, it works fine with blank cells […]

### 22 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

=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/