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

**Table of Contents**hide

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

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

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

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

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

**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:**

➦ R**ight-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

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.

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.