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 value 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.
Download Practice Book
Download the Excel workbook that we’ve used to prepare this article.
Use of VLOOKUP Function to Find the Last Value in a 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.
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
➦ Activate Cell G5, type the formula given below:
➦ 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 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 last sales amount here in Cell G4.
➦ Activate Cell G4.
➦ Type the formula given below:
➦ Then press the Enter button and you will get the last value.
👉 Formula Breakdown:
Here it will check if the cells in Column D are empty or not. It will return as-
We have divided 1 by the result. As FALSE means 0 and TRUE means 1 so the result will be as follows:
I have set lookup value 2 because the lookup function will find 2 through the column, when it will reach to error then it will return back to its nearest value 1 and will show that result. That will return as-
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. And 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.
➦ Type the formula given below in Cell G5
➦ Hit Enter button.
👉 How Does the Formula Work:
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-
It is actually showing the row number counted from the first entry.
And the INDEX function will give the corresponding sales (D5:D13) according to the previous match from the array (C5:C13) which will return as-
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. And 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 given below:
➦ And Press the Enter button.
👉 How Does the Formula Work:
The ROW function will show the row number for the array that will return as-
Here Cell E7 is our lookup value and this formula will match it through the array B5:B13. Then it will return as-
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-
The MAX function will find the maximum value from the previous result that will return as
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-
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
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 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.
➦ First, copy the unique names from the main sheet to a new sheet.
➦ Then go to the main sheet. Activate any new cell. I chose E5.
➦ Click Data > Data Tools > Data Validation.
A dialog box will appear.
➦ Select List from the Allow bar.
➦ Then press the open icon from the Source bar.
➦ After that go to your new sheet and select the unique names.
➦ Press OK
See a down arrow sign is shown on that cell’s right side. By clicking here you can select any name. That will save our 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.
➦ 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
➦ Then press the play button to run the codes. A dialog box named Macros will appear.
➦ Click Run.
Our new function is ready now.
➦ Now return to your worksheet.
➦ Activate Cell F5
➦ Type the formula given below with the new function.
➦ Press Enter to get the last occurrence result for Ron.
Now when you will choose any salesman’s name then you will get his corresponding last occurrence value.
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