Author: Oscar Cronquist Article last updated on November 15, 2018

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:

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

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.