How to find and return the second to last value in a certain row or column in Excel?
As below screenshot shown, how to find and return the second to last value in row 6 or column B of table range A1:E16? This article will show you method to achieve it.
Excel Productivity Tools
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial
As above screenshot shown, for finding and returning the second to last value in row 6 or column B of table range A1:E16, the formulas we provide in this article can help you. Please do as follows.
Find and return the second to last value in column B
1. Select a blank cell for placing the second to last value, enter formula =INDEX(B:B,LARGE(IF(B:B<>"",ROW(B:B)),2)) into the Formula Bar, and then press Ctrl + Shift + Enter keys simultaneously to get the result.
Note: in the formula, B:B is the column where you will find the second to last value. You can change it to any column as you need.
Find and return the second to last value in row 6
Select a blank cell to populate the second to last value in a row, then enter formula =OFFSET($A$6,0,COUNTA(6:6)-2,1,1) into the Formula Bar, and then press the Enter key to get the result. See screenshot:
Note: In this formula, $A$6 is the first cell of the specified row, 6:6 is the row number where you need to find the second to last value,
- How to find position of first/last number in a text string in Excel?
- How to find first or last Friday of every month in Excel?
- How to vlookup find the first, 2nd or nth match value in Excel?
- How to find value with highest frequency in a range in Excel?