Text vs Number when using lookup table of numbers

[fishsponge]

Hello,

I have a load of cells which are generated by a formula. The results of the formula are all numbers.

I then have a lookup table in which these numbers are looked up and the corresponding values printed (obviously!).

Problem is... some of the results from the original formula really are numbers (10), and some of them are text that look like numbers (="10").

The values that are equivalent to ="10" are not being looked up correctly in the lookup table, due to not strictly being a "number" and therefore my spreadsheet isn't working correctly.

Does anyone know how to fix this?

And no - i can't replace ="10" with just 10 due to the way the values are worked out, unfortunately.

Is there a function that says "even though this is text, treat it as a number"?

Thanks in advance for any suggestions! :D

[davesexcel]

Hi,

What is the lookup formula you are using?




Isn't ="10" the same as '10 ?

[fishsponge]

Please Login or Register  to view this content.

and no, ="10" is not the same as 10 due to the quotes. The quotes mean it is treated as text, not a number, despite the characters being made up from numbers!

=10 is the same as 10, but it is not the same with the quotes.

[fishsponge]

anyone else got any ideas? Surely this problem must have been stumbled across before?

[davesexcel]

Please Login or Register  to view this content.

and no, ="10" is not the same as 10 due to the quotes. The quotes mean it is treated as text, not a number, despite the characters being made up from numbers!

=10 is the same as 10, but it is not the same with the quotes.

Maybe try vlookup(b28,$A$11:$B$25,2,false)

By the way I typed '10 not 10

[daddylonglegs]

A couple of points...

If you're using LOOKUP then your lookup range, A11:A25 should be sorted ascending.

You can probably convert your text "numbers" to real numbers by amending your original formula, e.g. enclosing in a VALUE function or adding +0 at the end then your LOOKUP should work, unamended.

Alternatively leave your original formula alone and convert within LOOKUP, i.e.

=LOOKUP(B28+0,$A$11:$B$25)

[fishsponge]

perfect! thank you!

I used VALUE() in the end :D