Vlookup cell reference

[Johno]

I have two spreadsheets, each containing a column of ID numbers common to
both sheets. I am attempting to extract data from one sheet and insert into
the other sheet using the common ID numbers. My formula is
=Vlookup(A5,A100:X200, 3) where A5 is the ID number I'm referencing on the
first sheet, A100:X200 is the range of data on the second sheet and 3 is the
column offset. I. get the formula or N/A as a result. If I enter the ID
number in place of the cell reference A5, it works.

The formula works on other spreadsheets so there must be something in this
one that prevents the cell reference from working in vlookup.

Can someone help?

 

[Fred Smith]

Two things:

1. If you are trying to reference two different sheets, you need to tell
Excel that. Right now your formula references only one sheet. There's no
mention of any other. Try something like:
=vlookup(a5,Sheet2!a100:x200,3)

2. If your ID numbers aren't sorted, you need to add the fourth parameter,
as in:
=vlookup(a5,Sheet2!a100:x200,3,false)

Regards,
Fred.

 

[T. Valko]

See if this helps:

http://contextures.com/xlFunctions02.html#Trouble

 

[Johno]

Thanks fred, but as to #1, I pasted the second spreadsheet range onto the
first spreadsheet, so I only have one sheet.

As to #2, I tried it with the fourth parameter and it still doesn't work.

any other suggestions, it's as though there is something in the spreadsheet
that is causing the problem. What's really weird, is that when i enter the
formula preceded by the = sign, I get the formula as a result.

As I think I said before, everything works on another spreadsheet.

 

[Johno]

Thanks, but I checked all of those options. I exported the data from a web
based data warehouse. Out of frustration, I deleted the ID numbers that were
exported and retyped them so I know there are no spaces or other characters.
It still did not work.

 

[LesG]

Hi Johno,
when I get this error, it is usually because one of the lookup up cells is
recognisd as text and the other numeric... I am assuming the ID numbers are
numeric only.

To get around this, I usually am succesful with =Vlookup(A5*1,A100:X200, 3,
false) ... the *1 usually converts the "string" number to a number

 

[Johno]

Thanks Leo, that was it. Even though I had formatted the row for number, it
still treated it as text.