vlookup problem

[Laurence Smith]

Hi,

This should be an easy one but it has me frustrated.

I have a small table that looks like this:

1+ 9
1 8
1- 7
2+ 6
etc
down to
4 0

I have formatted the left column as text and have called the table
"lkup"

When I insert the correct formula in another worksheet I get a proper
answer of 7 for instance when I put in 1-
Any of the items in the first column that have a minus or plus sign
after them return correct answers.
But if I insert 1, 2, 3, or 4 then I get #N/A

It seems to me that the program will see 1+ as text but insists on
seeing 1 as a number.

What can I do to make this work?

TIA

 

[Laurence Smith]

Hi,

This should be an easy one but it has me frustrated.

I have a small table that looks like this:

1+ 9
1 8
1- 7
2+ 6
etc
down to
4 0

I have formatted the left column as text and have called the table
"lkup"

When I insert the correct formula in another worksheet I get a proper
answer of 7 for instance when I put in 1-
Any of the items in the first column that have a minus or plus sign
after them return correct answers.
But if I insert 1, 2, 3, or 4 then I get #N/A

It seems to me that the program will see 1+ as text but insists on
seeing 1 as a number.

What can I do to make this work?

TIA

I think I found out what I was doing wrong. While I had the numbers
formatted at text in the lookup table I did not make sure to take the
column that I was entering the query into as text. It was naturally
seeing "3" as a number and couldn't match it to the "3" as text in the
lookup table.
Once I formatted that column as text all the test cases worked out.

 

[Pete_UK]

Or, instead of using

=VLOOKUP(A1, ...

you could have used:

=VLOOKUP(A1&"", ...

Hope this helps.

Pete

 

[Gord Dibben]

To be sure of proper results I would preface the lookup value 1, 2, 3, 4 with
an apostrophe.

Sometimes simply formatting as text is not reliable.


Gord Dibben MS Excel MVP