0

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?

2

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"
| improve this answer | |
0

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)))
| improve this answer | |
  • IF you have a more efficient way of doing it, I'd welcome suggestions. – Brett Jun 4 '15 at 14:03
-2

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

| improve this answer | |
  • 2
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.