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

=INDEX($C$3:$C$11,MAX(($E$3=$B$3:$B$11)*MATCH(ROW($B$3:$B$11),ROW($B$3:$B$11))))

### Explaining the array formula

**Step 1 - Find matching values**

($E$3=$B$3:$B$11) returns {TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE}

**Step 2 - Create row numbers for cell range $B$3:$B$11**

MATCH(ROW($B$3:$B$11),ROW($B$3:$B$11)) returns {1; 2; 3; 4; 5; 6; 7; 8; 9}

**Step 3 - Multiply arrays**

($E$3=$B$3:$B$11)*MATCH(ROW($B$3:$B$11),ROW($B$3:$B$11))

becomes

{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE} * {1; 2; 3; 4; 5; 6; 7; 8; 9} returns {1; 0; 0; 4; 0; 0; 0; 8; 0}

**Step 4 - Maximum value in array**

MAX(($E$3=$B$3:$B$11)*MATCH(ROW($B$3:$B$11),ROW($B$3:$B$11)))

becomes

MAX({1; 0; 0; 4; 0; 0; 0; 8; 0}) and returns 8.

**Step 5 - Return a value of a cell at the intersection of a particular row and column**

=INDEX($C$3:$C$11,MAX(($E$3=$B$3:$B$11)*MATCH(ROW($B$3:$B$11),ROW($B$3:$B$11))))

becomes

=INDEX($C$3:$C$11, 8)

### Download *.xlsx file

Find last matching value in an unsorted list.xlsx

### Functions in this post

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**MAX(**number1, number2**)
**Returns the largest value in a set of values

** MATCH(**lookup_value, lookup_array, [match_type

**]**

Returns the relative position of an item in an array that matches a specified value

**ROW(**reference**)**

Returns the rownumber of a reference

### 17 Responses to “Find last matching value in an unsorted list”

### Leave a Reply

**How to add vba code to your comment:**

[vb 1="vbnet" language=","]

your code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

=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