Lookup across workbook

[Carla]

Hello,
I'd like some help with the lookup formula you would use to reference cells
across different worksheets. For example, if a certain word is typed into
cell A1, then I would like cell A2 to reflect a value that appears on another
worksheet (if the word changes, then the worksheet reference changes). I
have tried LOOKUP(A1,{x,x},{x,x}) and basic IF formulas.
Hope someone can assist, if more information is needed, let me know.
Thanks

 

[Sheeloo]

Assuming you have your data to lookup in Book1.xls in Sheet1 Col A & B
then enter this in A2 in the other one;
=VLOOKUP(A1,'[Book1.xls]Sheet1'!$A:$B,2,FALSE)

An easier way is to enter the formula upto =VLOOKUP(A1, and then click on
the other workbook, select cols A & B then enter the rest of the formula i.e.
enter ,2,False)

 

[Gord Dibben]

How many possible words could be entered in A1 to return values from
different worksheets?

You could end up with an extensive nested IF formula if a multitude of
words.

For two only...............

=IF(A1="qwert",VLOOKUP(A1,Sheet2!$C$1:$F$24,2,FALSE),IF(A1="hoohah",VLOOKUP(A1,Sheet3!$A$1:$D$24,2,FALSE)))

You may be better off with a Data Validation dropdown menu in A1 and some
sheet event code if you have multiple choices in A1


Gord Dibben MS Excel MVP

 

[Carla]

Hi Gord,
This could work - I have 15 possible words that could appear in A1. Would
you mind also elaborating on what I could do with the "Data Validation
dropdown menu in A1 and some sheet event code"?
Thanks for your help.

 

[Gord Dibben]

Would the lookup tables be the same on each sheet?

i.e. just the lookup value(word in A1) and sheet names would differ.

Might be easiest if you email me directly with the workbook and an
description of the ranges to lookup on the sheets.

Change the AT and DOT to get my email address.

I have a plan but should get some specifics as above.

Should be able to send you back a working model.


Gord

 

[Carla]

Hi Gord,
Thanks for your assistance. I played around with the formula you supplied a
few days ago and came up with a solution. I created a dropdown menu for A1
and used the following formula in the lookup cells (so that it was displaying
the value in that particular cell on the different sheets):

=IF($A$1="Bob",LOOKUP(Sheet1!$G$53,Sheet1!$G$53),IF($A$1="John",LOOKUP(Sheet2!$G$74,Sheet2!$G$74),IF($A$1="David",LOOKUP(Sheet3!$G$98,Sheet3!$G$98))))

Thanks again.

 

[Gord Dibben]

Ok

That's not quite 15 names but you're happy so I'm happy.


Gord