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.
returns {FALSE; TRUE; 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)
returns {#DIV/0!; 1; #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)) 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))) 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<>"" returns {TRUE;TRUE;TRUE; ... ;FALSE}
Step 2 - Divide 1 with array
1/(B3:B12<>"")
returns {1;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) 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)))
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$"
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))
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))
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