Formula to return last but one cell in row!

Assuming you don't have gaps, i.e. to get the cell immediately before

=INDEX($6:$6,LOOKUP(2,1/($6:$6<>""),COLUMN($6:$6))-1)

or a shorter version that needs to be "array entered"

=INDEX($6:$6,MATCH(2,1/($6:$6<>""))-1)

confirm with CTRL+SHIFT+ENTER

Hi daddylonglegs

Thanks for that quick reply.

Assuming you don't have gaps

Do you mean by that, any blank cells within the row to be tested e.g. from A1 to BZ1. If so, yes there gaps.

I have just tried out your formula on about 10 different rows, all of which have "gaps" and it has returned the correct result in each instance.

I prefer the non array method, especially as I'm trying to cut down on using any more array formulas, to avoid making my workbook run even slower than it already does.

By the way, would you have any thoughts on how to make the formula work so that it references the current row where it is located?

Thanks again ...spellbound

What I mean about gaps is.....

Your original formula returns the last value in the row. So if that value is in Z6 my suggested formulas will give you the value in Y6, even if Y6 is blank, so you might want to return the previous populated cell in row 6, be that X6, W6 or whatever.......for that you can use this formula

=INDEX($6:$6,LARGE(IF($6:$6<>"",COLUMN($6:$6)),2))

which needs to be confirmed with CTRL+SHIFT+ENTER

You don't want to put the formula in the referenced row if it refers to the whole row.

If your data range ends at column BZ then use the formula in CA6, tweaked to make it more "robust"

=INDEX(A6:BZ6,LOOKUP(2,1/(A6:BZ6<>""),COLUMN(A6:BZ6)-COLUMN(A6)))

Unfortunately, I could not use your last solution because the formula has to reside in Column B of the same row and the data range expands weekly by 6 columns per week.

I have tried out numerous solutions from the web but they were all producing a circular reference which turned out to be a conflict with a specific type of formula that I use throughout this particular worksheet.

Out of interest the conflicting formula was:

=SUMIF($B$5:$B$2191,INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN($B$1),4),"1","")&(ROW()-1)),CE$5:CE$2191)

By virtue of its reference to Column B. So I will have to change all of these formulas to:

=SUM(OFFSET(CE86,-COUNTIF($B$5:$B85,$B85),,):OFFSET(CE86,-1,0))

Which sum the relevant blocks of data as required.

This enabled me to make use of daddylonglegs formula:

=INDEX($6:$6,LOOKUP(2,1/($6:$6<>""),COLUMN($6:$6))-1)

Which I have tested in about 100 rows and no obvious problems have arisen at this time.

I also came across this formula on XLDynamic.com which is supposed to be the fastest solution for finding the last numeric cell but I was unable to make it work in returning the last but one numeric cell.

=INDEX(1:1,MATCH(9.99999999999999E307,1:1))

I would be interested to learn if there is a way of adapting this formula to return the last but one numeric cell.

It is surprising how long this has taken to sort out for what I hoped would be a minor change.

spellbound

You didn't say you wanted the last numeric value

Simplest formula for that (not necessarily the most efficient but it won't make much difference over a relatively short range)

=LOOKUP(9.99999999999999E+307,1:1)

but to get the second last you'd use INDEX/MATCH, i.e.

=INDEX(1:1,MATCH(9.99999999999999E+307,1:1)-1)

Note: as with some of the previous formulas suggested this gives the cell before the last numeric value, regardless of what's in that cell so it wouldn't necessarily be the second last numeric if that cell was blank or contained text......

That works fine in this instance as the last cell and the last cell but one will always be numeric.

Mind you, I could have sworn that I tried out this formula with -1 at the end but sometimes it is quite easy to miss the obvious.

Thanks for your help …spellbound