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:

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.

The following tutorials explain how to perform other common tasks in Excel: