Vlookup with variable worksheet reference

[trempnvt]

Hello,

I'm trying to run a vlookup where the worksheet (within the file
referenced would depend on the value of a certain cell.

For example, if B5 could be a number different fruits, and there wer
different worksheets named after the fruit options, I could have on
cell where I would look up something on worksheet 'Apple' if B5="Apple
or on worksheet 'Grape' if B5="Grape".

When B5="Apple",
=VLOOKUP(A2,'Apple'!A1:C37,3,FALSE) will give me the correct value, bu
both
=VLOOKUP(A2,B5!A1:C37,3,FALSE)
and
=VLOOKUP(A2,'B5'!A1:C37,3,FALSE)
will open a browse window and ask me to update the values for B5.

Any help would be much appreciated

 

[roly]

Hi,

How many criteria are there in the list? If there only a few, you
could put it in an IF statement.

All the best,


Roly

 

[trempnvt]

How many criteria are there in the list? If there only a few, yo
could put it in an IF statement.

Eighteen. I could do it that way or by naming the ranges that I wan
to select from, but if there's a cleaner way to do it, that's m
preference.

Thanks for your help

 

[Larry S]

From your example below, I will assume column B will contain the COMPLETE
name of the corresponding worksheet you want to pull the data from. then in
your VLOOKUP formula below, replace 'Apple'!A1:C37 with

INDIRECT(CONCATENATE("'",B5,"'!A1:C37"))

The CONCATENATE works like the "&" in that it is building a text string, in
the case of peaches 'PEACHES'!A1:C37 and the INDIRECT allows you to
addressess the cell range.

Hope this helps.

 

[Guest]

Think we could use INDIRECT in the vlookup, viz.:
=VLOOKUP(A2,INDIRECT("'"&B5&"'!A1:C37"),3,FALSE)
where B5 houses the sheetname

---

 

[trempnvt]

Thank you all for your help! The INDIRECT trick worked

 

[Guest]

Thank you all for your help! The INDIRECT trick worked.

Glad it worked for you !

---