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.
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
If you don't want to use an array formula, read this article:
Recommended articles
This article demonstrates a formula that returns the last matching value based on a given condition. The above image shows […]
Index match category
This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns. S.Babu asks: […]
This article demonstrates how to use INDEX and MATCH functions to lookup and return multiple results. The lookup value is […]
This article demonstrates how to use INDEX and MATCH functions to match multiple conditions and return multiple results. The Excel […]
This article demonstrates how to extract multiple numbers based on a condition and return a sorted list from small to […]
The picture above demonstrates a formula in cell F3 that allows you to look up a value in column B […]
This article demonstrates formulas that let you perform lookups using two or more conditions. The image above shows two conditions […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
11 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.
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)
Is there a way to ignore previous matched values? In the instance where there are multiple matches, that the formula will return the next matched value?