Vlookup on External Data query

[clipper]

I am trying to do a vlookup on another sheet which contains an external data query. The result of the lookup is #N/A eventhough I know that there is a valid match there.

For the moment I am working around this by copy the lookup reference from the external query to the destination sheet, which allows for the lookup to work. This however is too much maintenance work when new data is required to be looked up on.

I have seen on the microsoft website that there is an article (315961) which provides a couple of solutions such as trunc within the vlookup. I have tried these and they still don't work. The only solution that works is hard coding the value into the vlookup statement

Can anybody help me?

 

[SydneyGeek]

Not sure if it would work, but have you tried making the external data (in Book2) into a named range in Book1? Yoiu can then use the defined name in your VLOOKUP.
You'll need to use Insert | Name | Define and enter the range definition there.
Something like: ='[Book2]Sheet1'!$A$2:$J$250, assuming you are in the same folder.

Regards
Denis

 

[Aladin Akyurek]

Are the lookup values supposed to be numbers? If so, care to post the lookup formula that you're using?

 

[clipper]

I have played around with the formating of the cells (to text) where the lookup reference resides and now I seem to have a compromise. By copy and poasting another working cell then replacing the value with the new item.

I'll see how that goes, just have to wait for data to be available for the new item.

 

[Aladin Akyurek]

I have played around with the formating of the cells (to text) where the lookup reference resides and now I seem to have a compromise. By copy and poasting another working cell then replacing the value with the new item.

I'll see how that goes, just have to wait for data to be available for the new item.

Is the formula that you use a secret?