7

I need to show the value of the right-most non-blank cell of each row in an array. How can this be accomplished in Excel?

In this example table the [Current] column has the desired result:

+---------+----------+---------+----------+
|  2016   |   2017   |  2018   | Current  |
+---------+----------+---------+----------+
|     700 |          |   200   |     200  |
|         |          |         |          |
|         |     450  |         |     450  |
|         |          |  2,700  |   2,700  |
|         |          |         |          |
|  42,350 |  71,500  |         |  71,500  |
|  2,660  |          |         |   2,660  |
|         |   1,100  |         |   1,100  |
|         |          |         |          |
|    470  |          |         |     470  |
+---------+----------+---------+----------+

Variations on the theme would be left-most, top-most, bottom-most value; or value greater than n, etc. Desktop Excel from Office 2016 if version is relevant.

2
  • is there a limit to your table or are you looking ar the possiblility of using the last row and column in excel.
    – Forward Ed
    Mar 19, 2019 at 23:00
  • @ForwardEd The [Current] 'summary' column could be on the left side if that makes the formula easier, or even another worksheet (though I'd prefer not). Year over year there will be a new columns added to the table. Mar 19, 2019 at 23:09

5 Answers 5

7

enter image description here

  • Enter this Formula in E2 & fill Down.

=LOOKUP(2,1/(A2:C2<>""),A2:C2)

How it works:

  • Formula recognizes that the Lookup Value of 2 is deliberately larger than any values that will appear in the Lookup Vector.
  • The expression A2:C2<>""returns an Array of True and False values.
  • 1 is then divided by this Array and creates a new Array composed of either 1's or divide by zero errors (#DIV/0!): {1,0,1,...}.
  • This array is the Lookup Vector.
  • When Formula can't finds Lookup Value then the
    Lookup matches the next smallest value.
  • In this case, the Lookup Value is 2, but the largest value in the Lookup Array is 1, so Lookup will match the last 1 in the Array.
  • LOOKUP returns the corresponding value in Result Vector, which is the value at the same position.

:Edited:

  • For Google Sheet this is the formula to use:

    =(IFERROR(LOOKUP( 2, 1 / ( A2:C2 <> "" ), A2:C2 ),""))
    
  • Finish it with Ctrl+Shift+Enter, formula will looks like,

    =ArrayFormula(IFERROR(LOOKUP( 2, 1 / ( A2:C2 <> "" ), A2:C2 ),""))
    
20
  • 1
    Thanks! I like this one for it's brevity, though it's taking effort to hold how it works in my mind. For readers, here's the same formula using structured references =LOOKUP(2,1/(Table13[@]<>""),Table13[@]). ('[@]' means "this row") Mar 20, 2019 at 15:43
  • Glad to help you @mattwilkie,, keep askong ☺ Mar 21, 2019 at 4:13
  • For me, this always return the leftmost value. Am I missing an edit? Jul 1, 2020 at 22:06
  • @AdamHowell,, nothing magical ,,, check the attaches screen shot,, shows the results,,, if possible share your sheet over the cloud and let me examine the data U are struggling with ! Jul 2, 2020 at 4:38
  • 1
    @AdamHowell,, better wrap formula with IFERROR,, looks like,, =ArrayFormula(IFERROR(LOOKUP( 2, 1 / ( A2:C2 <> "" ), A2:C2 ),"")) Jul 7, 2020 at 9:21
6

Although there are already multiple solution to this problem, here is my preferred one, for me this is the closest to the natural thinking:

=INDEX(A2:C2,MAX(IF(A2:C2="","",COLUMN(A2:C2)))) - this is an array formula, so press CTRL+SHIFT+ENTER after typing it.

enter image description here

How it works:

  • IF(A2:C2="","",COLUMN(A2:C2)) - for each cell in the row returns empty string if cell is empty and column number otherwise
  • MAX( ... ) - selects highest column number returned
  • =INDEX(A2:C2, ... ) - selects the cell from the row based on highest column number

Warning: it works correctly only if your range starts from first column, otherwise need to compensate for the shift, e.g. for a range staring from column C:
=INDEX(C2:X2,MAX(IF(C2:X2="","",COLUMN(C2:X2)))-2)

2
  • 2
    +1 for closest to match to thinking. I had a hard time choosing between this one and LOOKUP method. In the end brevity won, because shorter is less scary and it's easier to take the time to understand it. ;-) Mar 20, 2019 at 15:54
  • Also very nice! Jun 11, 2021 at 18:22
1

Lets assume your table is layed out in C2:F12 with header row being row 2 and summary column being F. Place the following formula in F3 and copy down.

=IFERROR(INDEX(3:3,AGGREGATE(14,6,column($C3:$E3)/($C3:$E3<>""),1)),"")

POC

NOTE:

  • AGGREGATE performs array operations with formula choice 14 and 15. As a result do not use full column/row references within the AGGREGATE function as you may wind up bogging down your system or crashing it with the number of calcs that will be performed. Using full column references outside array type functions are fine. note the 3:3 being used for INDEX.

  • When inserting a new column, if column F is selected and the insert is performed, you will need to update the formula in F so C3:F3 is the new range. If you have column E selected and you insert a new column, the range will update automatically but now your data is in the wrong column. If you left column F blank, placed the formulas in column G instead, and used C3:F3 as your range inside AGGREGATE, then in the future you could select column F for insert and your formulas would update and you can enter new data in F. You would have a blank column to the right still for selection in the next year to repeat the process.

1
  • +1 because it works and AGGREGATE is new to me. I think you meant "Place ... in F3 and copy down", not E3 as E3 is a data column? Mar 20, 2019 at 15:56
0

For the first value (not header) is in cell L24 (700), M24 () and N24 (200).

This is the formula:

=IF(N24<>"",CELL("contents",N24),IF(M24<>"",CELL("contents",M24),IF(L24<>"",CELL("contents", L24),"")))

I hope it is useful...

1
  • This finds the rightmost non-blank value in a group of three columns.  But the question says “the right-most non-blank cell of each row in an array”, and the OP says “Year over year there will be new columns added to the table”, so the answer needs to be able to handle any number of columns without massive rewriting. Mar 23, 2019 at 19:17
0

Another approach, inelegant, more brutish, but easily understandable is to use TEXTJOIN(), now that we have it.

Using A2:C2 for the first row, put the following into D2, then copy and paste down. Or Fill, or... you get the idea:

For Textjoin string below, use the TEXTJOIN() function to concatenate the entire range of cells you wish to examine. Use "TRUE" to leave out blanks to shorten the string, and for a delimiter, use a character that realistically will NEVER appear in your data. I use "Ŧ" below (and for the character to substitute the last one with, "Ų"). Using commas like one often does with TEXTJOIN() and its relatives can cause problems.

=RIGHT( Textjoin string,
LEN( Textjoin string ) -
FIND( "Ų", SUBSTITUTE( Textjoin string, "Ŧ", "Ų",
LEN( Textjoin string **with** delimiter ) - LEN( Textjoin string **without** delimiter )
)))

and it's easier to understand. SUBSTITUTE() can do its work starting on an Instance # which would let you find the last use of your delimiter in Textjoin string with delimiter. In the last line, you find the LEN() of the Textjoin string with and with out the delimiter and finding the difference by subtraction. That is the number of delimiters and therefore the Instance # you need.

In the next to last line, you substitute a different character for that instance, then use FIND() to get its position in the strng.

The second line subtracts that position from the overall LEN() of the string to find out how many characters follow it. That tells you how many characters to strip off the right of the string you made.

The first line does just that, leaving you with the contents of whatever last cell was in the range.

The string length Excel will use varies by function, some in the 6-7,000 range, for instance, some more like 32,000. With that in mind (it is why you specify "TRUE"), one can do a HUGE range rather than A2:C2.

Notice that you are then working with the joined string, not cells so:

  1. You never have to find cell addresses, etc.
  2. You can actually use it on a range made up of joined "sub" ranges, and ranges made up of REALLY broken apart cells. Discontinuous ranges are you friends and allies.

Because the way the data exists inside pieces being evaluated by Excel inside a formula, breaking chunks out into Named Ranges can cause problems, or not, as the interim results being created and used by Excel evaluating the formula CAN be in a different form than the end result a Named Range presents forward, sometimes one cannot used Named Ranges on pieces to lay out the logic of a formula for future ease. But nothing in the above presents that problem, so you can create Named Ranges for, say, the TEXTJOIN's and enter the rest natively so anyone clicking on the cell can see the logic. Or break the pieces to something logical like "InstanceNumber" (Named Range) so it reads even more easily. Create it, then dump it all into a Named Range. Or don't bother at all with Named Ranges.

As I say, inelegant. Longer than some solutions, but not reallly "brutish" the way some things are. No helper columns, or other things people often cannot use. Or will not. No {array} formulas.

(And you can use the discontinuous ranges when needed.) The approach can also take a pile of text and data that a report engine PDF's and which is then extracted to Excel but chunked differently into cells for each related set (so info about 10 clients, each set in a 10 column by 13 row block but the address for one is in cell 4,6 yet in cell 3,8 for another, yet it follows the same flow, just populates different cells when imported) and by making it a single string, let you formulaically hunt up the parts. Often, anyway. Or take a block of cells and see if a bit of data appears anywhere inside them using functions, not macrros or arrays or one helper cell for each one in the block.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .