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.

B3:B12=E3

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.

Get Excel *.xlsx file

INDEX MATCH - Last value_v2

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

Recommended articles

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

Index match category

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
This article demonstrates how to use INDEX and MATCH functions to lookup and return multiple results. The lookup value is […]

INDEX and MATCH – multiple criteria and multiple results
This article demonstrates how to use INDEX and MATCH functions to match multiple conditions and return multiple results. The Excel […]

Excel categories

Comments (11)