Lookup Formula Just Stopped Working :(

[oscark37]

So strange. I use a spreadsheet that is updated monthly. I use the following formula to find the value of the last cell with data in a row.

All the sudden, it returns nothing. (data is still there in the rows) What could have gone wrong?

=LOOKUP(1,1/(A10:Z10<>""),A10:Z10)

Thanks,

Oscar

 

[steve the fish]

Id imagine you have a space or something that you cant see in the last cell with a value. Clear the cells after your last cell with data and see if it starts producing what you expect.

 

[oscark37]

No idea what there is in the cells after the last cell with a number, but that worked! But I would have to delete the columns every week. Is there another formula I can use as a more efficient solution? Maybe one that will spit back the last cell with a number, regardless if there are cells after with spaces?

Thanks,

Oscar

 

[steve the fish]

There is one for a number but it depends if that number is a true number:

=LOOKUP(9.9E+307,A10:Z10)

Or you could adapt yours slightly:

=LOOKUP(9.9E+307,1/(A10:Z10*(A10:Z10<>"")),A10:Z10)

 

[oscark37]

2nd one worked like an absolute charm! Thanks Steve!

There is one for a number but it depends if that number is a true number:

=LOOKUP(9.9E+307,A10:Z10)

Or you could adapt yours slightly:

=LOOKUP(9.9E+307,1/(A10:Z10*(A10:Z10<>"")),A10:Z10)

 

[Aladin Akyurek]

If you are looking for the last numeric value in A10:Z10, the simpler and faster

=LOOKUP(9.99E+307,A10:Z10)

would the job.

 

[steve the fish]

If you are looking for the last numeric value in A10:Z10, the simpler and faster

=LOOKUP(9.99E+307,A10:Z10)

would the job.

Yeah i put that but im thinking theres numbers that are text here.

 

[oscark37]

Yeah i put that but im thinking theres numbers that are text here.

It appears you are right, as the first solution did not work. Thanks again!

 

[Aladin Akyurek]

Yeah i put that but im thinking theres numbers that are text here.

Are you looking for a numeric value or text value? Are your numbers text numbers, not true numbers?