Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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).

share|improve this question
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 Feb 24 '12 at 15:20
@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 Feb 24 '12 at 15:43
If the cell is formatted text that might happen, try hitting F9, but ultimately what are you trying to do with this question? – Raystafarian Feb 24 '12 at 15:44
@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 Feb 24 '12 at 16:34
up vote 0 down vote accepted

In trying to use vlookup I got a combination of #n/a errors due, in all likelihood, to misuse;

Trying to figure out what was going wrong in the syntax of my =vlookup I saw a repeated resolution to sort ascending by the lookup value. The table had been sorted by region, then client name.

Once sorted in ascending order by the lookup key, the lookups are working. This initial step was where the tables went awry.

share|improve this answer
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 Feb 24 '12 at 17:59
@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 Feb 24 '12 at 18:26
No, if LOOKUP works for you then stick with that - it's much quicker than VLOOKUP.... – barry houdini Feb 24 '12 at 20:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .