Embedded Reference

[Heather]

Using a formula, I replicate the appropriate name of one of the many
excel reference areas I have created. I would now like to use the name
I've generated in a VLOOKUP formula. However, the formatting is off
and I don't know how to fix it. The VLOOKUP formula does not like the
reference name to be in the text format and I'm not sure how to format
my generated reference name to get it to work. Any ideas?

Ex: Based on the position in the spreadsheet, I generate the name of
the area that has the data I need [ex: area1, area2, etc.]. I would
now like to use these generated names in a VLOOKUP formula [ex:
vlookup($d3,area1,2,false)] but the format of "area1" is tripping me up.

 

[Guest]

THe first thing I'd do would be to check to see if the named range AREA1
exists. Type CTRL G and enter AREA1 to see if it's there. If it is and
it's not the area you want, or it doesn't exist, post your definition for it
and maybe we can help. We'd need to know how you want it defined.

 

[Debra Dalgleish]

Use the INDIRECT function in the formula, e.g.:

=VLOOKUP(D2,INDIRECT(C3),2,FALSE)

where C3 contains the reference name.

Using a formula, I replicate the appropriate name of one of the many
excel reference areas I have created. I would now like to use the name
I've generated in a VLOOKUP formula. However, the formatting is off
and I don't know how to fix it. The VLOOKUP formula does not like the
reference name to be in the text format and I'm not sure how to format
my generated reference name to get it to work. Any ideas?

Ex: Based on the position in the spreadsheet, I generate the name of
the area that has the data I need [ex: area1, area2, etc.]. I would
now like to use these generated names in a VLOOKUP formula [ex:
vlookup($d3,area1,2,false)] but the format of "area1" is tripping me up.