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

Here's our step-by-step guide.

Pink background with Microsoft Excel logo.

You can trust PC GuideOur team of experts use a combination of independent consumer research, in-depth testing where appropriate – which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

Last Updated on

If you’re wondering how to use VLOOKUP to find the last value in the column, we’ve got you covered.

Let’s say you have a dataset with student marks. Now, you want to find the last occurrence of a specific student’s marks in the data. Or you want to see the last data point in the table. Manually scrolling through thousands of rows of data can be tedious. However, using VLOOKUP, you can save a lot of time.

So, let’s discover how you can retrieve the last occurrence of a specific keyword and the last data entry in a table.

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)

Use VLOOKUP to find the last value in a Microsoft Excel spreadsheet illustrated by a screen shot.

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.

A screen shot of an Excel spreadsheet with the last value obtained using VLOOKUP.

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.

A screen shot of an excel spreadsheet with the last value in a specific column.

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:

A screenshot of a Microsoft Excel spreadsheet showcasing the use of VLOOKUP to find the last value.

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:

A screen shot of an Excel spreadsheet with a number in it, showcasing the use of VLOOKUP to find the last value.

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.
A screen shot of a spreadsheet with the last value found using VLOOKUP.

How to create a VLOOKUP function in Excel to find the last value in a spreadsheet.

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:

A screenshot of a spreadsheet in excel showcasing the use of VLOOKUP to find the last value.

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:

A screenshot of an excel spreadsheet with the last value obtained using VLOOKUP.

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: