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

Get FREE Advanced Excel Exercises with Solutions!

VLOOKUP is a very popular function in Excel which refers to the vertical lookup. We can use the built-in VLOOKUP function or we can even make our own formulas that will work as vertical lookup to return values with more dynamic criteria. In this article, I am going to show how to find the last value in a column with VLOOKUP in Excel.


Use of Excel VLOOKUP Function to Find Last Value in Column

Let’s get introduced to our workbook first. In this datasheet, I have used 3 columns and 10 rows to present some salespersons’ sales amounts according to the corresponding dates.

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

In this method, we’ll find the last occurrence of a value by using the VLOOKUP Function. VLOOKUP stands for ‘Vertical Lookup‘. It is a function that makes Excel search for a certain value in a column. Here we have 3 different sales amounts of Bill. Now we’ll find the amount of his last sales in Cell G5

Steps:

➦ Activate Cell G5, type the formula given below:

=VLOOKUP(F5,C5:D13,2)

➦ Hit the Enter button and you will get the last occurrence of his sales.

But VLOOKUP won’t give the right answer for unsorted data in approximate mode. See the image below.

And if we use the exact match for the fourth argument it will show the first match like the image below. Because VLOOKUP uses binary search. So when it finds a value larger than the lookup value then it gets back to the previous value to show, see it in the image below.

So to overcome this situation for unsorted data to find the last occurrence we’ll have to use lookup functions or other combined formulas. We’ll discuss those methods now in the following sections.


Alternatives to the VLOOKUP Function to Find Last Value in Column

Now we’ll apply four alternative methods to find the last value in a column.

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

Here we’ll find the last value of a column with the LOOKUP Function in Excel. 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. I’ll find the amount of the last sale here in Cell G4.

Steps: 

➦ Activate Cell G4.

➦ Type the formula given below:

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

➦ Then press the Enter button and you will get the last value.

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

👉 Formula Breakdown:

D:D<>””

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

{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE…..}

1/(D:D<>””)

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

{#DIV/0!;#DIV/0!;#DIV/0!;1;1;1;1;1;1;1;#DIV/0!;#DIV/0!}

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

I have set the lookup value to 2 because the lookup function will find 2 through the column, when it reaches to error then it will return back to its nearest value of 1 and will show that result. That will return as:

367

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


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

Here we’ll use the combination of the 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 then it returns the relative position of that item in the range.

Steps:

➦ Type the formula given below in Cell G5

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

➦ Hit the Enter button.

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 ‘1’  indicates the approximate match. Now the function will return as-

6

It is actually showing 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-

367

That is actually the last occurrence for Cell F5


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

Now we’ll do the task with the combination of INDEX, MAX, SUMPRODUCT, and ROW functions. The ROW function will find the row numbers. SUMPRODUCT is a function that multiplies a range of cells or arrays and returns the sum of 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.

Steps:

➦ Enable editing in Cell F7

➦ Copy and paste the formula given below:

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

➦ And Press the Enter button.

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-

{5;6;7;8;9;10;11;12;13}

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

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

{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}

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

It 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-

{0;0;0;0;9;0;0;0;13}

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

13

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

Now 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-

9.

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

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

563

That is our last occurrence for Bill.


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

If you like to code in Excel, then this method is suitable for you. We can perform the previous operation by using the VBA method too. Let’s go through the following steps on how we can do it easily.

For that, at first, I’ll make a dropdown bar for the unique names. Then I’ll make a new user-defined function “LastItemLookup” using VBA that we’ll use to find the last occurrence.

Step 1:

➦ First, 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

Step 2:

➦ Then go to the main sheet. Activate any new cell. I chose E5.

➦ Click Data > Data Tools > Data Validation.

A dialog box will appear.

Step 3:

➦ Select List from the Allow bar.

➦ Then press the open icon from the Source bar.

Step 4:

➦ After that go to your new sheet and select the unique names.

➦ Press OK

See a down arrow sign shown on that cell’s right side. By clicking here 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.

Step 5:

Right-click your mouse on the sheet name.

➦ Select View Code from the context menu.

A VBA window will open up.

Step 6:

➦ Type the codes 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

Step 7:

➦ Then press the play button to run the codes. A dialog box named Macros will appear.

➦ Click Run.

Our new function is ready now.

Step 8:

➦ Now return to your worksheet.

➦ Activate Cell F5

➦ Type the formula given below with the new function.

=LastItemLookup(E5,B5:C13,2)

➦ Press Enter to get the last occurrence result for Ron.

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


Download Practice Book

Download the Excel workbook that we’ve used to prepare this article.


Conclusion

I hope all of the methods described above will be well enough to VLOOKUP the last value in a column. Feel free to ask any questions in the comment section and please give me feedback.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

2 Comments
  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

 

 

ExcelDemy
Logo