i would like to use the text values in cells as the sheet name reference in a
lookup function

on sheet 1 we have the following data in each cell
a2 = 5
b2 = 'sheet2 (text)
c2 = 'a1:a10 (text)
d2= CONCATENATE(b2,c2) (text)

i would like to replace the formula =lookup(a2,sheet2!a1:a10,sheet2!c1:c10)
with the formula that uses the values in cells b2 and c2
=lookup(a2,d2,sheet2!c1:c10)

What am i missing in order to make this possible?

Look in HELP for the INDIRECT() function

Post again in this thread if you can't get it done

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"rsmith" wrote in message ...
|i would like to use the text values in cells as the sheet name reference in a
| lookup function
|
| on sheet 1 we have the following data in each cell
| a2 = 5
| b2 = 'sheet2 (text)
| c2 = 'a1:a10 (text)
| d2= CONCATENATE(b2,c2) (text)
|
| i would like to replace the formula =lookup(a2,sheet2!a1:a10,sheet2!c1:c10)
| with the formula that uses the values in cells b2 and c2
| =lookup(a2,d2,sheet2!c1:c10)
|
| What am i missing in order to make this possible?

try

a2 = 5
b2 = sheet2!
c2 = 'a1:c10
d2 = b2&c2

formula =lookup(a2,indirect(d2))

hth

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"rsmith" escreveu:

i would like to use the text values in cells as the sheet name reference in a
lookup function

on sheet 1 we have the following data in each cell
a2 = 5
b2 = 'sheet2 (text)
c2 = 'a1:a10 (text)
d2= CONCATENATE(b2,c2) (text)

i would like to replace the formula =lookup(a2,sheet2!a1:a10,sheet2!c1:c10)
with the formula that uses the values in cells b2 and c2
=lookup(a2,d2,sheet2!c1:c10)

What am i missing in order to make this possible?

that looks like it will work

thanks for the help

"Marcelo" wrote:

try

a2 = 5
b2 = sheet2!
c2 = 'a1:c10
d2 = b2&c2

formula =lookup(a2,indirect(d2))

hth

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"rsmith" escreveu:

i would like to use the text values in cells as the sheet name reference in a
lookup function

on sheet 1 we have the following data in each cell
a2 = 5
b2 = 'sheet2 (text)
c2 = 'a1:a10 (text)
d2= CONCATENATE(b2,c2) (text)

i would like to replace the formula =lookup(a2,sheet2!a1:a10,sheet2!c1:c10)
with the formula that uses the values in cells b2 and c2
=lookup(a2,d2,sheet2!c1:c10)

What am i missing in order to make this possible?