Excel VLOOKUP to Find Last Value in Column (with Alternatives)

Finding a value in a column – a vertical lookup – is a common task when working in Excel. Excel provides a built-in VLOOKUP function for this purpose, but this has limitations, To work round these, we can make our own formulas that will work as vertical lookup to return values with more dynamic criteria. In this article, we will examine how to find the last value in a column with VLOOKUP, the limitations of this method, and several alternative methods.

Using the VLOOKUP Function to Find Last Value in Column

Let’s get introduced to our sample dataset first. It has 3 columns and 10 rows to containing some salespersons’ sales amounts on particular dates.

Use of VLOOKUP Function to Find the Last Value in a Column

We’ll find the last occurrence of a value by using the VLOOKUP Function.

Specifically, we have 3 different sales amounts for Bill. Let’s find the amount of his last sales and place it in Cell G5.


  • In Cell G5, enter the formula below:
  • Press Enter to return the last occurrence of his sales.

The result is incorrect, because the lookup data is unsorted.

VLOOKUP will return an error for unsorted data in approximate mode.

And if, as we did in the formula above, we use exact match for the fourth argument in the formula, the first match will be returned, not the last. That’s because VLOOKUP uses binary search, so when it finds a value larger than the lookup value, it reverts to the previous matched value.

Alternatives to VLOOKUP to Find Last Value in Column

Method 1: Using the LOOKUP Function

The LOOKUP function is used for looking through a single column or row to find a particular value from the same place in a second column or row.


  • Activate Cell G4.
  • Enter the formula below:
  • Press Enter to return the last value.

Method 1: Use LOOKUP Function to Find Last Value in Column

Formula Breakdown:

  • D:D<>””

Checks if the cells in Column D are empty or not. It will return as-


  • 1/(D:D<>””)

Divides 1 by the result. As FALSE means 0 and TRUE means 1 the result will be as follows:


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

We set the lookup value to 2 because the lookup function will find 2 through the column. When it reaches an error then it will return to its nearest value of 1 and will show that result. That will return as:


Read More: How to Use VLOOKUP to Find Duplicates in Two Columns

Method 2 – Using INDEX and MATCH Functions

The INDEX function returns a value or the reference to a value from within a table or range. The MATCH function is used to search for a specified item in a range and return the relative position of that item in the range.


  • Enter the formula below in Cell G5:
  • Press Enter.

Method 2: Use INDEX and MATCH Functions to Find Last Value in Column

How Does the Formula Work:

  • MATCH(F5,C5:C13,1)

Here the MATCH function is used to find the value of Cell F5 for items sorted in ascending order from the array C5:C13. Setting the third argument to ‘1’  indicates an approximate match. The function will return as-


This is the row number counted from the first entry.

  • INDEX(D5:D13,MATCH(F5,C5:C13,1))

The INDEX function will give the corresponding sales (D5:D13) according to the previous match from the array (C5:C13), which will return as-


Method 3 – Combining INDEX, MAX, SUMPRODUCT And ROW Functions

The ROW function finds row numbers. SUMPRODUCT is a function that multiplies a range of cells or arrays and returns the sum of the products. The MAX function will find the maximum number. The INDEX function returns a value or the reference to a value from within a table or range.


  • Enable editing in Cell F7.
  • Copy and paste the formula below:

➦ Press Enter.

Method 3: Combination of INDEX, MAX, SUMPRODUCT And ROW Functions to Find Last Value in Column

How Does the Formula Work:

  • ROW($B$5:$B$13)

The ROW function will show the row number for the array that will return as-


  • ($E$7=$B$5:$B$13)

Here Cell E7 is our lookup value and this formula will match it through the array B5:B13. It will return as-


  • ROW($B$5:$B$13)*($E$7=$B$5:$B$13)

This is the multiplication of the previous two formulas that will actually multiply the corresponding row numbers. FALSE means 0 and TRUE means 1. So after multiplication, it will return as-


  • MAX(ROW($B$5:$B$13)*($E$7=$B$5:$B$13))

The MAX function will find the maximum value from the previous result, that will return as


  • SUMPRODUCT(MAX(ROW($B$5:$B$13)*($E$7=$B$5:$B$13))-4)

The SUMPRODUCT function is used to find the row number in the array. As our list starts from the 5th row onwards, 4 has been subtracted. So the position of the last occurrence of Bill is 9 on our list, so the formula will return as-



The INDEX function is used to find the sales for the last matched name. And it will return as-


That is our last occurrence for Bill.

Method 4 – Use Excel VBA to Find Last Occurrence of a Value in Column

We can perform the previous operation by using VBA code.

First, we’ll make a dropdown bar for the unique names. Then we’ll make a new user-defined function “LastItemLookup” using VBA and use it to find the last occurrence.


  • Copy the unique names from the main sheet to a new sheet.

Method 4: Use Excel VBA to Find Last Occurrence of a Value in Column

  • Go to the main sheet and activate any new cell, here E5.
  • Click Data > Data Tools > Data Validation.

A dialog box will appear.

  • Select List from the Allow bar.
  • Press the Open icon from the Source bar.

  • Go to your new sheet and select the unique names.
  • Press OK

There’s a down arrow sign on cell E5’s right side. By clicking it you can select any name. That will save us time because we won’t need to type the names every time.

Now we’ll make a new function named LastItemLookup with Excel VBA.

  • Right-click your mouse on the sheet name.
  • Select View Code from the context menu.

A VBA window will open up.

  • Enter the code given below:

Option Explicit
Sub vlookup_last()
End Sub
Function LastItemLookup(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
Dim x As Long
For x = LookupRange.Columns(1).Cells.Count To 1 Step -1
If Lookupvalue = LookupRange.Cells(x, 1) Then
    LastItemLookup = LookupRange.Cells(x, ColumnNumber)
    Exit Function
End If
Next x
End Function


  • Press the Play button to run the codes.

A dialog box named Macros will appear.

  • Click Run.

Our new function is ready now.

  • Return to your worksheet.
  • Activate Cell F5.
  • Enter the formula given below:

Press Enter to get the last occurrence for Ron.

Now when you choose any salesman’s name then you will get his corresponding last occurrence value.

Download Practice Book

Related Articles

<< Go Back to VLOOKUP a Range | Excel VLOOKUP Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

  1. VLOOKUP will be happy to do the task.

    The trick is to present the table parameter in reverse order so as it searches in what it perceives as first record to last record in the index column, it is only doing so in the virtual table and in reality is searching from the bottom of the physical table (“real table” or any other similar name, even though it could actually have no physical presence in the spreadsheet being instead a virtual table itself produced by the formula…).

    Just use INDEX on the table range/name/address and specify all the rows to be used but to come in reverse. If a 9 row table like above, one would have used “10-ROW(1:9)” in the old days or SEQUENCE(10,1,10,-1) nowadays, for the row parameter. Use an array constant for the column parameter (for example, {7,1} to use column 7 for the lookup index and column 1 for the return value… (that use of array constant for the column parameter also lets one “look left” with VLOOKUP)).

    Since it is looking last record to first, the first match will be the factually last instance of the lookup value in the range.

  2. Hello ROY, thanks for your feedback. You have got a nice trick. I hope, it will help others.
    But if the reverse order affects the other calculation of any user then maybe the alternative methods are more feasible.

Leave a reply

Advanced Excel Exercises with Solutions PDF