Returning the last non-blank value in a horizontal range

I know that this has been asked before, as I have found some possible solutions that involve array formulae, but I'm wondering if anyone knows how to do it without one.

I want to be able to return the value of the last non-blank cell in the horizontal range L3:BF3. This would need to account for any blank cells that might occur within the range. Those 'blank' cells would contain formulae and the non-blank cells would contain GCSE grades from A* down to U or National Curriculum levels in the format nx (e.g. 3a). I would need to be able to copy the formula down to other rows. The reason I'm not keen on an array formula is because they can slow things down, as I understand it.

I'm afraid I don't have a sample worksheet as this is all, for now at least, an emerging plan in my head. I should probably add that the grades will not necessarily increase in value in the range, so we're not looking for the largest.

Is there a way, or am I going to have to embrace an array formula?

Hi Ali,

Try:

=LOOKUP(2,1/(L3:BF3<>""),L3:BF3)

Regards

Looks perfect, thanks. Sometimes I just can't see the wood for the trees ...

I'll have a play with this.

Great - this seems to work fine on some sample data. Would you mind just explaining to me how it works? I'm sure I'll be able to adapt it for other purposes if I understand exactly what the syntax is asking Excel to do. Thanks in advance!

Do you want the cell value as in what is in the cell or the cell value as in the cell reference IE AA3?.. oops i posted this way to late.

Thanks for responding. It's the value I was after, as stated in the thread title and the opening post, not the cell address or reference.

Here's a couple of generic "return the last entry in the range" type formulas.

For the last numeric value in column A:

=LOOKUP(1E100,A:A)

For the last text value in column A:

=LOOKUP("zzzzz",A:A)

Note that this version does not exclude formula blanks which are text values.

Thanks, Tony.

Originally Posted by AliGW

Would you mind just explaining to me how it works? I'm sure I'll be able to adapt it for other purposes if I understand exactly what the syntax is asking Excel to do. Thanks in advance!

Here's an explanation I wrote for a very similar query based on this formula:

=LOOKUP(2,1/(A2:A10<>0),A2:A10)

All the logic of the explanation is basically the same for your application.

http://www.excelforum.com/excel-form...ml#post3398950

Many thanks! This is very useful.

You're welcome!