## Find last value in a column

The formula in cell D3 lets you get the last value in column B, it works fine with blank cells in your list.

**Formula in cell D3:**

The formula is quite cpu-intensive since it is processing all cells in column B, there are more than a million cells in one column in Excel 2007 and later versions.

Recommended article

Find last matching value in an unsorted list

I read an interesting blog post Find Last Item in Group With Index Match written by Debra Dalgleish. It is about […]

Find last matching value in an unsorted list

You can change the cell references if you know you will have a smaller list, this formula is easier for Excel to process:

### Explaining formula

The formula takes all cell values in column B and checks if they are not empty. Then it divides 1 with the array creating errors for all empty cells.

The LOOKUP function ignores errors and tries to find a match. If every match is 1 and the lookup value is 2, the LOOKUP function returns last value in cell range. Normally the list must be sorted ascending for the LOOKUP function to work, however since every value is 1 there is no need to sort the list.

I am going to use the following formula because it will be easier to demonstrate:

LOOKUP(2,1/(B3:B12<>""),B3:B12)

**Step 1 - Check if cells in column B are not equal to nothing**

B3:B12<>""

becomes

{"ZM";"TY";"NX";0; "DY";"RQ";0; "JW";"XH";0}<>""

and returns

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

**Step 2 - Divide 1 with array**

1/(B3:B12<>"")

becomes

1/({TRUE;TRUE;TRUE; FALSE;TRUE;TRUE;FALSE; TRUE;TRUE;FALSE})

and returns

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

Boolean value TRUE is equal to 1 and FALSE is equal to 0. You can't divide a value with zero so Excel returns an error (#DIV/0!).

**Step 3 - Return value**

LOOKUP(2,1/(B3:B12<>""),B3:B12)

becomes

LOOKUP(2,{1;1;1;#DIV/0!;1;1; #DIV/0!;1;1;#DIV/0!},B3:B12)

becomes

LOOKUP(2,{1;1;1;#DIV/0!;1;1; #DIV/0!;1;1;#DIV/0!},{"ZM";"TY";"NX";0; "DY";"RQ";0; "JW";"XH";0})

and returns XH in cell D3.

Recommended article

How to use the LOOKUP function

Finds a value in a sorted cell range and returns a value on the same row.

How to use the LOOKUP function

### What about errors?

I have added a few errors in column B below.

I am working with Excel 2016 and errors in column B seems to not be an issue.

### Return adjacent value

This data set has two columns, column B and C.

**Formula in cell E3:**

The formula returns an adjacent value of the last value. In fact, it doesn't need to be adjacent, you can change the cell reference (C:C) as long as it's starting point and ending point is the same as the first cell reference (B:B).

### Download excel *.xlsx file

5 easy ways to extract Unique Distinct Values

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

How to return a value if lookup value is in a range

In this article, I will demonstrate four different formulas that allow you to lookup a value that is to be found […]

Find last matching value in an unsorted table

DonW asks: Ok, you've shown it for regular ranges....how about within tables. I have a table similar to: ID Name […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

How to perform a two-dimensional lookup

Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]

Find last matching value in an unsorted list

I read an interesting blog post Find Last Item in Group With Index Match written by Debra Dalgleish. It is about […]

Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]

Lookup with an unknown number of criteria

Rashid asks:I used your array formula with great success to find the search results from multiple criteria. However, my problem […]

### One Response to “Find last value in a column”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

[…] https://www.get-digital-help.com/2017/08/16/find-last-value-in-a-column/ […]