Saturday, April 25, 2020

Home Excel tips How to use the VLOOKUP function to return the last match in a list

How to use the VLOOKUP function to return the last match in a list

April 25, 2020 Excel tips,

VLOOKUP is an awesome Excel function. We all know this. But it certainly has its limitations. One of these is that it can only return the first match from a list.

This is great when looking for a unique value. But what about when the value you are looking for appears multiple times in the list, and you want to return the last match.

Sure we could sort the list so that the last match would become the first, but this is not always an option.

This blog post looks at using the VLOOKUP function to return the last match in a list. The technique and formulas used can be adapted to find the 2^nd or 3^rd match if required.

VLOOKUP for Last Match – Watch the Video

Find the Number of Matches in the List

In order to create a VLOOKUP to return the last match in the list, we will need to know how many matches there are in total.

The following formula has been entered in cell J1 to return how many times the customer ID occurs in the list.

=COUNTIF(C:C,$I$4)

Create a Helper Column for the Lookup Value

Next we need to create a column of unique values which can then be used by the VLOOKUP function.

The values in this column are made by joining the customer ID and that instance of the ID. The image below shows column D as the helper column. You can see the two instances of customer Hanari Carnes which has the ID 1094. A number 1 and 2 has been attached to the end of the ID to make it unique.

Creating a unique ID for multiple matches

The following formula has been entered into column D. The position of the dollar signs is important for this formula to work.

=C2&COUNTIF($C$2:$C2,C2)

The COUNTIF function is an extremely powerful and versatile function to have in your Excel arsenal.

This column can be hidden once the VLOOKUP is written. It is an important column, but it does not need to be visible on screen.

VLOOKUP Function for the Last Match

With the helper column now in place, we can write a VLOOKUP to look to return the last match in a list.

The VLOOKUP function below has concatenated the contents of cell I4 (the customer ID) and J1 (the number of occurrences in the list of that customer ID) together to form the lookup value.

The table array is columns D:F to ensure that the leftmost column of the array is the helper column that we created.