How to use VLOOKUP to find the last value in the column in Excel – 3 simple ways

How to find the last value in the column in Excel using VLOOKUP 

• Scenario on hand: We have a sample dataset of student marks for three assessments on different dates. 
• Goal: We want to get the last entry and last occurrence of data.

We have 3 methods for this:

1. Using VLOOKUP with a search keyword
2. Retrieve the last occurrence with VLOOKUP
3. Using VLOOKUP with a function   

Method 1 – Using VLOOKUP with a search keyword

You can use a keyword in the VLOOKUP formula to find the first occurrence of the keyword in the data. 

For our scenario, we want to find the first occurrence of Bill’s marks in the dataset. 

Here’s the formula you can input for this: 

=VLOOKUP({Lookup value}, {Table range}, {column index from which to return value with $}, FALSE)

=VLOOKUP(F7, $C$6:$D$14, 2, FALSE)

While this is a great way to search for a specific keyword in the data, it is not the way to find the last occurrence of the keyword in the data. 

VLOOKUP cannot be used alone to retrieve the last occurrence of Bill’s marks in the dataset. 

Method 2 – Retrieve the last occurrence with VLOOKUP 

If you wish to find Bill’s marks in the last assessment, you can use two methods:

2.1 Use Sort with VLOOKUP

VLOOKUP can be effectively used to retrieve the first assessment’s and the last assessment’s marks. 

1. Before you use VLOOKUP, select the table range that has the data. Then head to the Data tab from the top and click Sort.

2. Then select the column with the dates and sort by Newest to Oldest to get the last assessment’s data in the first rows.

3. Then, use the same VLOOKUP formula to retrieve the marks secured by Bill in the last assessment. 

=VLOOKUP({Lookup value}, {Table range}, {column index from which to return value with $}, FALSE)

=VLOOKUP(F7, $C$6:$D$14, 2, FALSE)

2.2 Use MAX before VLOOKUP

If you have a considerable dataset, finding the last date of the data can be tedious. A simple MAX formula can help you retrieve the latest entry date in the data. 

Here’s the simple formula you can input:

=MAX({Column range with dates})

=MAX(B6:B14)

Here’s the result:

Note: Make sure that the data in the date column is in the date format by checking or changing the data type from this option:

You can then use a filter to show the marks only on the last assessment date and a VLOOKUP formula to get Bill’s marks on the last assessment.

Here’s how to use the filter:

• Select the table range where you want to apply the filter.
• Click the Filter button from the Data tab. 
• The label rows on the table will get an arrow. 
• Select the last assessment date from the arrow on the Assessment Date column.

Then input the VLOOKUP formula:

=VLOOKUP({Lookup value}, {Table range}, {column index from which to return value with $}, FALSE)

=VLOOKUP(F12,C12:D14, 2, FALSE)

Here’s the result from the VLOOKUP formula after applying the filter on the date column:

Method 3 – Using VLOOKUP with a function 

A function can get you the last entry of the data. This formula is helpful for times when you have a huge amount of data and want to retrieve the last entry.

Here’s the formula:

=LOOKUP(2,1/(D:D<>””),D:D)

Here’s the result:

Wrapping up

VLOOKUP has a number of uses. Above, we discussed some ways you can use VLOOKUP to retrieve the first occurrence of a keyword in a data and the last data entry. You can use VLOOKUP in combination with other Excel functions to get your desired value. 

Learn more about Excel with these helpful guides: