# Find last value in a column

This article demonstrates formulas that return the last value in a given cell range or column. The image above shows a formula in cell D3 that extracts the last value from cell range B3:B12.

Cell range B3:B12 is populated with values and empty cells in random order, this shows that the formula works fine with empty cells.

### Table of Contents

- INDEX MATCH - Last value
- Extract the last value in a given cell range
- Return a hyperlink to the last value in a column
- Return the row number of the last value in a column
- Extract a corresponding value next to the last value in a column
- Find the last non-empty cell with short cut keys
- Get *.xlsx file

## 1. INDEX MATCH - Last value

INDEX and MATCH are more versatile than the VLOOKUP function in terms of lookups, however, it only gets the first match. I have shown before how to lookup all matching values in this post: INDEX MATCH â€“ multiple results and this article: VLOOKUP and return multipleÂ values

Today I will show you how to get the last matching value, the image above demonstrates this formula in cell E6. It looks for value BB and the last matching value is found on row 12, the corresponding value in column C is 10 and thisÂ value is returned in cell E6.

Array formula in cell E6:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with aÂ beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Explaining formula in cell E6

#### Step 1 - Check if values are equal to lookup value

The equal sig lets you compare a cell value to another cell value, in this case, I am comparing a cell against an entire cell range.

becomes

{"AA";"BB";"CC";"BB";"DD";"BB";"EE";"GG";"VV";"BB"}="BB"

and returns

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

#### Step 2 - Divide 1 with array

This step is special to the LOOKUP function, it allows us to get the last matching value.

1/(B3:B12=E3)

becomes

1/{FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE}

and returns

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

#### Step 3 - MATCH function

The MATCH function ignores error values and matches the last number smaller than the lookup value, in this specific situation.

MATCH(2,1/(B3:B12=E3))

becomes

MATCH(2,{#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1})

becomes

MATCH(2,{#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1})

and returns 10.

#### Step 4 - INDEX function

The INDEX function returns a value from a cell range based on row an column numbers.

INDEX($C$3:$C$12,MATCH(2,1/(B3:B12=E3)))

becomes

INDEX($C$3:$C$12,10)

and returns 10.

### Get Excel *.xlsx file

If you don't want to use an array formula, read this article:

Recommended articles

This article demonstrates a formula that returns the last matching value based on a given condition. The above image shows […]

## 2. Extract the last value in a given 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

Recommended articles

This article demonstrates a formula that returns the last matching value based on a given condition. The above image shows […]

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

### 2.1 Explaining formula

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

The less than and greater than signs are logical operators, they check if cell values in column B are not empty. The result is an array containing TRUE or FALSE.

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

Recommended articles

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

### 2.2 What about errors?

I have added a few errors in column B, see the image above.

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

## 3. Return a hyperlink to the last value in a column

The formula in cell D3, demonstrated in the image above, creates a hyperlink to the last cell in column B. Press with left mouse button on the hyperlink takes you to the last value in column B.

Formula in cell D3:

### 3.1 Explaining formula

#### Step 1 - Get path and workbook name

The CELL function gets information about the formatting, location, or the contents of a cell.

CELL("filename", A1)

returns C:\temp\[Find last value in listv2.xlsx]Hyperlink

#### Step 2 - Find the location of character [

We don't need the path in order to create a working hyperlink. This step returns the character of the first string that we do need.

The SEARCH function returns a number representing the position of character at which a specific text string is found reading left to right.

SEARCH("[", CELL("filename", A1))

becomes

SEARCH("[", "C:\temp\[Find last value in listv2.xlsx]Hyperlink")

and returns 35.

#### Step 3 - Extract workbook and worksheet name

The MID function returns a substring from a string based on theÂ starting position and the number of characters you want to extract.

MID(text, start_num, num_chars)

MID(CELL("filename", A1), SEARCH("[", CELL("filename", A1)), LEN(CELL("filename", A1)))

becomes

MID(CELL("filename", A1), 9, LEN(CELL("filename", A1)))

The LEN function calculates the number of characters in a given string.

MID(CELL("filename", A1), 9, LEN(CELL("filename", A1)))

becomes

MID(CELL("filename", A1), 9, 49)

and returns

"[Find last value in listv2.xlsx]Hyperlink"

#### Step 4 - Concatenate workbook name, worksheet name, and column

The ampersand character & lets you concatenate strings in an Excel formula.

MID(CELL("filename", A1), SEARCH("[", CELL("filename", A1)), LEN(CELL("filename", A1)))&"!$B$"

becomes

"[Find last value in listv2.xlsx]Hyperlink"&"!$B$"

and returns "[Find last value in listv2.xlsx]Hyperlink!$B$".

#### Step 5 - Calculate row number of the last vale in a column

The ROW function allows you to calculate the row number of the last value.

MID(CELL("filename", A1), SEARCH("[", CELL("filename", A1)), LEN(CELL("filename", A1)))&"!$B$"&LOOKUP(2, 1/(B:B<>""), ROW(B:B))

becomes

"[Find last value in listv2.xlsx]Hyperlink!$B$"&LOOKUP(2, 1/(B:B<>""), ROW(B:B))

becomes

"[Find last value in listv2.xlsx]Hyperlink!$B$"&11

and returns "[Find last value in listv2.xlsx]Hyperlink!$B$11"

#### Step 6 - Return the last value

LOOKUP(2, 1/(B:B<>""), B:B)

returns "XH".

#### Step 7 - Create a hyperlink

The HYPERLINK function allows you to build a link in a cell pointing to something else like a file, workbook, cell, cell range, or webpage.

HYPERLINK(MID(CELL("filename", A1), SEARCH("[", CELL("filename", A1)), LEN(CELL("filename", A1)))&"!$B$"&LOOKUP(2, 1/(B:B<>""), ROW(B:B)), LOOKUP(2, 1/(B:B<>""), B:B))

becomes

HYPERLINK("[Find last value in listv2.xlsx]Hyperlink!$B$"&11, "XH")

and returns a hyperlink to cell B11.

## 4. Return the row number of the last value in a column

The formula in cell D3 returns a number representing the row number of the last value in column B.

Formula in cell D3:

## 5. Return adjacent value

This data set has two columns B and C. The formula in cell E3 returns the corresponding value of the last value in column B from column 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 its starting point and ending point are the same as the first cell reference (B:B).

## 6. Find the last non-empty cell with short cut keys

The following steps decribe how to select the last non-empty cell in column B using keyboard keys.

- Press with the left mouse button on cell A1 to select it.
- Press and hold CTRL key.
- Press down arrow key once. This takes you to the last cell in column A, if all cells in column A are empty.
- Release all keys. Press right arrow key to move to the last cell in column B.
- Press and hold CTRL key.
- Press up arrow key once. This takes you to the last non-empty cell in column B. See the image above.

### Lookups category

This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]

Table of Contents How to perform a two-dimensional lookup Reverse two-way lookups in a cross reference table [Excel 2016] Reverse […]

This article demonstrates a formula that returns the last matching value based on a given condition. The above image shows […]

### Excel categories

### 14 Responses 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/ […]

May I ask How to Find the Last Match in a Range with a Wildcard? Thank you very much.

Grace,

Formula in cell E6:

=XLOOKUP(E3,B3:B12,C3:C12,"N/A",2,-1)

XLOOKUP is a new formula recently introduced to Excel 365 subscribers.

Grace,

Try this array formula if you own an earlier version of Excel.

=INDEX($C$3:$C$12,MATCH(2,1/SEARCH(E3,B3:B12)))

Dear Oscar

Thank you very much.

thanks again and again

Dear Oscar

Thank you very much.

thanks again and again

Guys how can I tell excel to ignore if the last match is NA?

Claudia,

(Array) formula in cell E6:

=INDEX($C$3:$C$12, MATCH(2, 1/((B3:B12=E3)*(NOT(ISNA(C3:C12))))))

Excel 365 subscribers do not need to enter the formula as an array formula.

Hi guys

How can I tell excel that I want second to last match as a result ???

Please can you explain why the MATCH lookup_value is 2?

Step3 example appears to repeat itself:

MATCH(2,{#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1})

becomes

MATCH(2,{#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1})

Penny's dropped, it's looking for the last number (1) smaller than the lookup value 2.

Maybe Step 3 should be:

MATCH(2,{#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1})

becomes

MATCH(2,{#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1},1)

Is there a way to ignore previous matched values? In the instance where there are multiple matches, that the formula will return the next matched value?

Many, many, many thanks! Exactly what I needed.

`=you:you are great, TRUE`