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

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.

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

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

367


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.

Steps:

➦ Type the formula given below in Cell G5

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

➦ Hit 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))

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-

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. And 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 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 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.

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 will choose any salesman’s name then you will get his corresponding last occurrence value.


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

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

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

ExcelDemy
Logo