## INDEX MATCH – Last value

INDEX and MATCH are more versatile than the VLOOKUP function in terms of lookups, however, it only gets the first occurrence. I have shown before how to lookup all matching values in this post: INDEX MATCH – multiple results and this article: VLOOKUP and return multiple values

Today I will show you how to get the last matching value, the image above demonstrates this formula in cell E6. It looks for value BB and the last matching value is found on row 12, the corresponding value in column C is 10 and this value is returned in cell E6.

Array formula in cell E6:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Explaining formula in cell E6

#### Step 1 - Check if values are equal to lookup value

The equal sig lets you compare a cell value to another cell value, in this case, I am comparing a cell against an entire cell range.

becomes

{"AA";"BB";"CC";"BB";"DD";"BB";"EE";"GG";"VV";"BB"}="BB"

and returns

{FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE}

#### Step 2 - Divide 1 with array

This step is special to the LOOKUP function, it allows us to get the last matching value.

1/(B3:B12=E3)

becomes

1/{FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE}

and returns

{#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1}.

#### Step 3 - MATCH function

The MATCH function ignores error values and matches the last number smaller than the lookup value, in this specific situation.

MATCH(2,1/(B3:B12=E3))

becomes

MATCH(2,{#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1})

becomes

MATCH(2,{#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1})

and returns 10.

#### Step 4 - INDEX function

The INDEX function returns a value from a cell range based on row an column numbers.

INDEX($C$3:$C$12,MATCH(2,1/(B3:B12=E3)))

becomes

INDEX($C$3:$C$12,10)

and returns 10.

### Download Excel *.xlsx file

If you don't want to use an array formula, read this article:

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

Lookup multiple values across columns and return a single value

This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns. S.Babu asks: […]

INDEX MATCH – multiple results

The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the […]

### 10 Responses to “INDEX MATCH – Last value”

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

**Contact Oscar**

You can contact me through this contact form

May I ask How to Find the Last Match in a Range with a Wildcard? Thank you very much.

Grace,

Formula in cell E6:

=XLOOKUP(E3,B3:B12,C3:C12,"N/A",2,-1)

XLOOKUP is a new formula recently introduced to Excel 365 subscribers.

Grace,

Try this array formula if you own an earlier version of Excel.

=INDEX($C$3:$C$12,MATCH(2,1/SEARCH(E3,B3:B12)))

Dear Oscar

Thank you very much.

thanks again and again

Dear Oscar

Thank you very much.

thanks again and again

Guys how can I tell excel to ignore if the last match is NA?

Claudia,

(Array) formula in cell E6:

=INDEX($C$3:$C$12, MATCH(2, 1/((B3:B12=E3)*(NOT(ISNA(C3:C12))))))

Excel 365 subscribers do not need to enter the formula as an array formula.

Hi guys

How can I tell excel that I want second to last match as a result ???

Please can you explain why the MATCH lookup_value is 2?

Step3 example appears to repeat itself:

MATCH(2,{#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1})

becomes

MATCH(2,{#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1})

Penny's dropped, it's looking for the last number (1) smaller than the lookup value 2.

Maybe Step 3 should be:

MATCH(2,{#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1})

becomes

MATCH(2,{#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1},1)