use cell text in lookup function

R

rsmith

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?
 
N

Niek Otten

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

|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?
 
M

Marcelo

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:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top