VLOOKUP problem

[John Gilchrist]

I have following formula in cell D24
=VLOOKUP(C24,QB_Info!$C$1:$D$50,2,FALSE)

I have following value in cell C24:
147873
------------------------
Scenario #1:
My VLOOKUP works fine if I manually key the value 147873 into the cell C24

Scenario #2:
The VLOOKUP does not work (I get #N/A) if I COPY and PASTE SPECIAL - VALUES
into the same cell C24

Scenario #3:
After performing Scenario #3 and getting #N/A, I then place the cursor at
the end of the "147873" string (in the formula bar), and press ENTER.
The VLOOKUP formula then evaluates properly. Is there some type of
terminating character which is invisible??

I've checked cell formatting, and everything else I can think of to find any
differences between Scenario #1 and Scenario #2. Why does #1 work and not
#2????

Thanks,
John

 

[Tyro]

Senario #1. Are you entering the number 147873 in C24 or text?
Senario #2 What are you copying and pasting? Text 147873 is still text
after pasting special
Is your lookup range C150 numeric or text?

Tyro

 

[John Gilchrist]

Tyro - thanks for responding
see my response below- after your questions.
------

Senario #1. Are you entering the number 147873 in C24 or text? --> TEXT
Senario #2 What are you copying and pasting? Text 147873 is still text
after pasting special; ---> I'm copying GENERAL & pasting it into a TEXT cell
Is your lookup range C150 numeric or text? - ---> TEXT

-------
I am copyinng from B24 (GENERAL) to C24(TEXT)
I forced C24 to TEXT to try to get the same type
After that, I used COPY and PASTE SPECIAL-VALUES to retain C24 as TEXT
After the PASTE SPECIAL-VALUES, C24 is text, but VLOOKUP does not work
I need to place my cursor in the formula bar at the end of the string &
press ENTER
After that, the cell is still TEXT, but now the VLOOKUP works OK

What are the VLOOKUP restrictions with TYPE?
Must the "lookup_value" argument have the same type as the "table-array"
argument?
Can the "lookup_value" argument be the result of a formula, or must it
be a "number" or "text" value??
I would really like my "lookup_value" to be the result of a formula, but
the VLOOKUP does not seem to work - that is why I am going thru this COPY &
PASTE SPECIAL-VALUE step -

I tried changing type of everyting (lookup value & range) to GENERAL - no
help - same results
Thanks for your help
John

 

[Shane Devenshire]

Hi John,

The problem is that when you manually type in 147873 the cell is numeric the
first column of the lookup range C1:C50 is numeric so since they are the
same data type VLOOKUP works. When you choose Copy, Paste Special, Values
if the source cell is text then the result of the paste is text and the data
types are not the same and VLOOKUP fails. When you click in the cell and
press enter it becomes a numeric entry and VLOOKUP works.

Here are a number of solutions, see which one best suits you:

1. You can change your formula to read:
=VLOOKUP(VALUE(C24),QB_Info!$C$1:$D$50,2,FALSE)
2. or you can use the array formula (to enter this you need to press Shift
Ctrl Enter):
=VLOOKUP(C24,TEXT(QB_Info!$C$1:$D$50,"General"),2,FALSE)
3. You could modify your table so that the column C data are text entered
numbers. In that case select column C and Format the cells as Text and then
retype all the numbers.
4. You could modify the range where you are copying the values that you put
into C24 from to be values. If this is a large range select an empty cell
and click copy, select the range and choose Edit, Paste Special, Add.

To appreciate your problem you can write the following formulas
=ISTEXT(C24)
=ISNUMBER(C24)
then go through the steps you normally do - copy the value to C24 using
paste values (by the way that doesn't do anything here, you really should
just paste), note the results of the formulas above, then click on c24 and
on the formula bar and press Enter and note the results of the formulas
again.

Cheers,
Shane Devenshire