# How can I return the second last text value from a row?

Could anyone tell me the Excel formula to return the second last text value in a row?

I managed to find this formula for last text value: `=INDEX(U2:Y2,MATCH(REPT("z",255),U2:Y2))`, but not sure how I get the second last value?

THE FORMULA

Here's an alternative method to return the nth text value from the end.

``````=IFERROR(INDEX(U2:Y2,LARGE(ISTEXT(U2:Y2)*COLUMN(U2:Y2),2)-COLUMN(U2)+1),"")
``````

This formula must be entered as an array formula using Ctrl+Shift+Enter instead of just Enter. You'll know you did it right if curly brackets { } appears at both ends.

HOW IT WORKS

`=IFERROR(...,"")` will return blank if the rest of the formula is an error. You'll get a `#VALUE!# error if there aren't at least n text values in the range.

`INDEX(U2:Y2,...)` takes in the array and returns some value within in. `INDEX(U2:Y2,4)` would return the 4th element in the array which, in this case, is whatever is in `X2`.

`LARGE(..,2)` takes in some array and returns the second largest value from within that array. Change the `2` to any other number to get the nth largest item. The `SMALL` function is similar but returns the nth smallest value instead.

`ISTEXT(U2:Y2)*COLUMN(U2:Y2)` is the part that makes you enter it as an array formula. The `ISTEXT` function will return an array of `TRUE`/`FALSE` values for whether each cell contains a text value. For instance, this could be `{TRUE,TRUE,FALSE,FALSE,TRUE}`. The `COLUMN` functions returns the column number. In this case, it will be `{21,22,23,24,25}`. These two are multiplied (`TRUE=1`, `FALSE=0`). For my example, the final array would be `{21,22,0,0,25}`. Plugging this into the `LARGE` formula from before would return `22` because it is the second largest value.

`-COLUMN(U2)+1` simply adjust the value being plugged into the `INDEX` formula to account for the fact that we're starting on column 22 but the array `U2:Y2` has only 5 columns. We want to return a value between `1` and `5`, not `21` and `25`.

EXAMPLE

Lets say you had the following data in the range `U2:Y2`:

`Hello | World | meep | 5 | boop`

The second-to-last text value is `meep` so let's see how the formula works. I'm going to fill in the calculations one step at a time. You can see a similar run-though of this using "Evaluate Formula" on the "Formulas" ribbon although it might not be in the same order.

``````=IFERROR(INDEX(U2:Y2,LARGE(ISTEXT(U2:Y2)*COLUMN(U2:Y2),2)-COLUMN(U2)+1),"")
=IFERROR(INDEX(U2:Y2,LARGE(ISTEXT(U2:Y2)*COLUMN(U2:Y2),2)-21+1),"")
=IFERROR(INDEX(U2:Y2,LARGE(ISTEXT(U2:Y2)*COLUMN(U2:Y2),2)-20),"")
=IFERROR(INDEX(U2:Y2,LARGE({TRUE,TRUE,TRUE,FALSE,TRUE}*COLUMN(U2:Y2),2)-21),"")
=IFERROR(INDEX(U2:Y2,LARGE({TRUE,TRUE,TRUE,FALSE,TRUE}*{21,22,23,24,25},2)-21),"")
=IFERROR(INDEX(U2:Y2,LARGE({21,22,23,0,25},2)-21),"")
=IFERROR(INDEX(U2:Y2,23-21),"")
=IFERROR(INDEX(U2:Y2,2),"")
=IFERROR(INDEX({"Hello","World","meep",5,"boop"},2),"")
=IFERROR("meep","")
="meep"
``````

I suspect your methodology may not be the most efficient... but using what you've already achieved, you could utilise your existing `MATCH`, subtract one to give you the new boundary to search, and return the last text from that new area. The `OFFSET` method would let you re-define the searching area with your new limit, like this:

``````=INDEX(U2:Y2,MATCH(REPT("z",255),OFFSET(U2,0,0,1,MATCH(REPT("z",255),U2:Y2)-1)))
``````
• IF you have a more efficient way of doing it, I'd welcome suggestions. – Brett Jun 4 '15 at 14:03

`=INDEX(D7:P7,MATCH(REPT("z",255),D7:P7)-1)`

• A bit more detail on how this works would improve your answer – Dave M Feb 28 '18 at 22:06
• The OP didn't post an example, so we don't know anything about the spreadsheet. The same with your answer. But you refer to different ranges than the question. So the answer raises additional questions about how to modify it and whether the approach would work for the OP. It would help if you included an example that the formula refers to, and an explanation of how it works. But this doesn't answer the question, because it identifies the cell before, not the text value before the last. – fixer1234 Mar 1 '18 at 0:10