By default, the VLOOKUP function in Excel looks up some value in a range and returns a corresponding value for the first match.
However, sometimes you may want to look up a value in a range and return the corresponding value for the last match.
The easiest way to do this in Excel is by using the following basic syntax with the LOOKUP function:
=LOOKUP(2,1/($A$2:$A$12=F1),$C$2:$C$12)
This particular formula finds the last value in the range A2:A12 that is equal to the value in cell F1 and returns the corresponding value in the range C2:C12.
The following example shows how to use this formula in practice.
Example: Look Up Value in Excel and Return Last Matching Value
Suppose we have the following dataset in Excel that contains information about various basketball players:
Suppose we would like to look up the team name “Nets” and return the value in the points column for the last player who is on the Nets team.
We can type the following formula into cell F2 to do so :
=LOOKUP(2,1/($A$2:$A$12=F1),$C$2:$C$12)
The following screenshot shows how to use this formula in practice:
The formula returns a value of 29.
By looking at the data, we can confirm that this is indeed the points value that corresponds to the last player on the “Nets” team:
If we change the value in cell F1 to a different team name, the formula will automatically update to find the new points value.
For example, suppose we change the team name to Warriors:
The formula returns a value of 12, which is the points value that corresponds to the last player on the “Warriors” team.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: Find First Occurrence of a Value in Column
Excel: Find First Value Greater than Specific Number
Excel: How to Find First Non-Zero Value in Row
