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?
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityCould 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)))
=INDEX(D7:P7,MATCH(REPT("z",255),D7:P7)-1)