1

I've found this code on the site and it works great for what's its intended for:

=LOOKUP(2,1/(NOT(ISBLANK(B25:B47))),B25:B47)

However, the values in the range B25:B47 are derived from a function along the lines of B25 =IF(A25="","",A25) or =IF(ISBLANK(A25),"",A25).

The problem is the lookup function sees "" as a value and not a blank cell and doesn't exclude it from the range.

How do I exclude "" as well? Or output something as nothing (not 0) instead of "".

The reason I don't want a 0 is because my sheet gets printed and I don't want a load of zero's everywhere!

Tunaki
  • 132,869
  • 46
  • 340
  • 423
Jonny Jon Jon
  • 13
  • 1
  • 5
  • Lookup formulas return `0` when they see `""` or an empty cell. If you don't like that, either wrap them in the same kind of `IF` you are using for B25, or apply a [zero-hiding format](http://office.microsoft.com/en-gb/excel-help/display-or-hide-zero-values-HP005199879.aspx) to the cells that contain them. – GSerg Dec 07 '14 at 11:23
  • Hi GSerg. I had a look at zero hiding and have come up with a bit of a bodge. If change the output of the IF to zero instead of "" and I use a lookup function to find last value that isnt a zero it'll find the correct value. Then all i would have to do is do some conditional formatting to make all zeros in the range appear white so when printed it wont show up. thanks for the nudge in the right direction. – Jonny Jon Jon Dec 07 '14 at 11:47

1 Answers1

1

Try checking the length of the cells using LEN function, then zero length data can be excluded, i.e. with

=LOOKUP(2,1/(LEN(B25:B47)>0),B25:B47)

If your data is numeric only then you could also use this formula to extract the last number in the range:

=LOOKUP(9.99E+307,B25:B47)

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • Or I could do this and it'll work perfectly without having to bodge it at all and spending ages changing all my previous IF functions to output to zero instead. Thanks. – Jonny Jon Jon Dec 07 '14 at 11:56
  • my data was an address field so contained both letters and numbers. your first function works perfectly for this application. – Jonny Jon Jon Dec 07 '14 at 12:08
  • OK, no problem, thanks - I'll leave that there as the latter version is arguably a better way for numeric data in case anybody else is looking..... – barry houdini Dec 07 '14 at 12:18