Lookup is failing to accurately use the lookup value

I am using two Excel workbooks; one is the backend for the other. In Book 1, there are sheets sheets with a $Timeline, $ClientInfo, and $VendorInfo. Book 2 is meant to be a summary record of different elements pulled from Book 1.

Essentially, my intent was to have a key using on our internal client tracking number (listed in Book 1) that would be entered into Book 2, thereby populating all required fields using the lookup function.

Basically, where B6 is the key, Book 2 is a table with a bunch of variations on;

=LOOKUP(B6, '[book1.xlsx]ClientInfo'!$B:$B,'[book1.xlsx]ClientInfo'!$N:$N)

=LOOKUP(B6, '[book1.xlsx]VendorInfo'!$B:$B,'[book1.xlsx]VendorInfo'!$D:$D)

=LOOKUP(B6, '[book1.xlsx]Timeline'!$B:$B,'[book1.xlsx]Timeline'!$E:$E)

Currently, no matter what values are entered into B6(listed values or made up ones), all of the returned lookup values are for the last entity listed, ergo the lookup is not actually matching anything correctly.

Some of the result vectors contain formulas (more =lookup's), some contain hand entered values; in both cases the yield is the wrong match.

The question then is two part;

Are there some initial steps I should have known to take to ensure that this kind of interdependence would work? Or perhaps is this one of those situations where you need to enable some feature or setting of the cells, formula, sheet, or workbook to make it work?
Am I pushing lookup further than it is meant to go (i.e. is it only meant for intra-workbook functionality)? If so what would be a better command (i.e. one more suited to inter-workbook functionality)? (How can I translate the lookup syntax into the proposed command's syntax efficiently?)
- I have no VBA abilities, so sticking to Excel formulas would be preferred in answering

Also, if I am doing this all wrong, let me know. My guess is that this should really be in a database (there will be 27 of the Book2s by the end of this).

mfg

Posted 2012-02-24T14:38:34.350

Reputation: 579

Why not just use a vlookup? =vlookup(B6,[Book1]Timeline!$B:$E,3,false) to return the value in column D? What are you trying to return? multiple values? – Raystafarian – 2012-02-24T15:20:04.370

@Ray Using =VLOOKUP isn't returning anything, the cell just displays the formula as entered (whether by hand or using the insert function dialog) – mfg – 2012-02-24T15:43:50.603

If the cell is formatted text that might happen, try hitting F9, but ultimately what are you trying to do with this question? – Raystafarian – 2012-02-24T15:44:32.373

@Ray Tried the F9, which then gave me a #n/a. Ultimately I am trying to make the lookups look up correctly (?). Found the problem and posted an answer; the table was not sorted by the key (B6), but rather another field. – mfg – 2012-02-24T16:34:45.547

VLOOKUP would probably be better. no sorting required and if you don't have exact match you get an error. LOOKUP may return wrong value if lookup value doesn't exist – barry houdini – 2012-02-24T17:59:47.327

@barry I am not worried about incorrect values or sorting. However, would VLOOKUP improve startup times of the workbook? The Book 2isn't taking very long to open, however the backend (Book 1) takes a bit of time to get going. – mfg – 2012-02-24T18:26:22.333

No, if LOOKUP works for you then stick with that - it's much quicker than VLOOKUP.... – barry houdini – 2012-02-24T20:09:06.153

Lookup is failing to accurately use the lookup value

Answers