MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Can I do lookup in different XLS spreadsheet?

Posted by Rick Koenig on March 10, 2001 12:13 PM

I want to do a lookup and add values based on data in my current worksheet by looking up entries in a separately-maintained spreadsheet. Is that possible with Vlookup or one of the variants? All I've read seems to say the data MUST be in the current worksheet. But if I have a long list of 500 or so values, then I first have to get them from somewhere and PASTE them into the spreadsheet. (Or can *that* be automated with a macro?)

What I want is pretty simple. Take a value from a cell, look up that value externally, return another value that's in the external table (or text file) into my present worksheet. Is it really SO HARD to do a simple function like that?

Posted by Dave Hawley on March 10, 2001 2:45 PM

Hi Rick

VLOOKUP and HLOOKUP will work just fine on another workbook. Here is an example:

=VLOOKUP("DOG",'K:\AustProd Legacy\Prodcnrl\[Book2.xls]Sheet1'!$C$1:$E$12,2,FALSE)

Let me know if you need any more help.


OzGrid Business Applications

Posted by Rick Koenig on March 10, 2001 10:20 PM

Wow, that worked like a charm. Thanks, Dave! I'm now going back to see why it is that isn't really documented anywhere.

You saved the day, and will save us HOURS of labour on Monday.