vlookup works when i type, doesn't when i paste?? 1

[bindi]

i am using vlookup, ( just started) can get it to work fine when i make up the table, but when i paste a table or try to apply it to an existing table it keeps comming up #na, have tried it with lookup and hlookup as well and none of them work.
Is there something here that i am missing i looked at the format of the cells and they are appear general???
oh yeah the data is sorted in ascending order.

 

[xlbo]

If you are pasting formulae - make sure that the 1st argument is referencing what you want it to reference. If it is a cell reference and is "relative", it may be that you are trying to lookup the wrong thing. The N/A error means that it can't find what you are trying to lookup
Rgds
~Geoff~

 

[bindi]

thanks, but i am not pasting the formulas just the tables,
i have simplified it to a 2x3 and tried this, and what i am looking up is correct its just the pasting process, or something to do with the cell formats??

 

[xlbo]

Ok - you might have a problem with formats - try using =ISNUMBER(A1) (where table data is in A1 and =ISTEXT(A1) - these will tell you what data type you have in the table. Are you looking up numbers or text ??
Can you supply the vlookup formula and an example table ?? Rgds
~Geoff~

 

[bindi]

sure,
table would be of form
a1 b1
8840 alaskan mountain range
8843 canadian format 1
8846 canadian format 2

i want to return the text, in b by looking up in a 4 digit number in a
i am using =vlookup(8840,a1:b3,2)
i have tried variations on this ie true, false statements, asand also lookup
thanks

 

[callcentreman]

Bindi,
It might just be a bug in Word, because I had a user with the same issue. I am positive that all formulas and settings were correct, but it just didn't work and kept comming up with #n/a. when we replicated the vlookup in a new sheet, it worked grand. yet when we copied the sheets within the same workbook..... nada

I have a querie running with Microsoft tech support on this, still waiting for a reply. '

rgrds,

Callcentreman

 

[xlbo]

Hmmm - try
=ISTEXT(A2) where A2 is a the cell reference for 8840
I think you'll get TRUE
In which case, your formula needs to be
=vlookup("8840",a1:b3,2)
I would also add the 4th argument of FALSE
ie =vlookup("8840",a1:b3,2,False)
This will look for an exact match only

Rgds
~Geoff~

 

[wwgmr]

Could it be that when you paste in the table you move cells so the Lookup is pointing to new location? Maybe name the range a1:b3 then ref the name that way when you paste in your table it will add to the range.

Not sure if this will help trying to throw out ideas.

Eric

 

[bindi]

thanks very much to all who responded,,,,
very helpfull, geoff u r the uberstar however,
prob solved
thanks again
bindi

 

[jeff]

I also noticed if you have calculation set to manual in Excel then when you fill down with vlookup it wont recalculate but only return results in copied cells of the vlookup in the initial cell. Resetting calculation to automatic in tools>options>calculation fixed this for me.

Regards, jEff.