Lookup Lookup Lookup
 

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!!

Try:

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

--

Vasant


"MR" wrote in message
...
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!!

Hi!

This works for me:

Summary sheet:

B1 = 2002
B2 = Mar
B3 = EMEA

=VLOOKUP(B2,INDIRECT(B1&"!A1:D13"),MATCH(B3,INDIRE CT(B1&"!
A1:D1"),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

-----Original Message-----
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!!
.