Lookup Lookup Lookup

[MR]

Here is how my workbook looks -

It has 7 sheets named Summary, 2000, 2001, 2002, 2003 etc
In each sheet, there are 12 rows with headings - Jan, Feb, Mar etc
In each sheet, there are 7 columns with headings - NA, EMEA, AP etc

In the Summary Sheet, I need to pull up the value for, say 2002, Mar, EMEA.
However, these values (2002, Mar and EMEA) are entered by the user in cells
B1,B2 and B3. (I do not want to hardcode the values).

Can anyone tell me how to do this? I tried MATCH function, but it does not
accept a variable value for the sheet name. Other functions have similar
issues.

Thanks!!

 

[Vasant Nanavati]

Try:

=OFFSET(INDIRECT(B1&"!A1"),MATCH(B2,INDIRECT(B1&"!A:A"),0)-1,MATCH(B3,INDIRE
CT(B1&"!1:1"),0)-1)

 

[Biff]

Hi!

This works for me:

Summary sheet:

B1 = 2002
B2 = Mar
B3 = EMEA

=VLOOKUP(B2,INDIRECT(B1&"!A113"),MATCH(B3,INDIRECT(B1&"!
A11"),0),0)

An INDEX/MATCH/MATCH would also work.

I suspect your problem is in using (or NOT using)INDIRECT
to build the proper references.

If you want to see a sample file of this post back with an
email address and I'll be happy to send you one.

Biff