Embedded Reference

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Heather - 27 Oct 2006 22:27 GMT

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.

Reply to this Message

Barb Reinhardt - 28 Oct 2006 15:20 GMT

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.

> 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
[quoted text clipped - 7 lines]
> 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.

Reply to this Message

Debra Dalgleish - 28 Oct 2006 15:35 GMT

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

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

where C3 contains the reference name.

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message