Vlookup + Indirect

Originally Posted by dilbert

I'm trying to use an indirect to pull in a range from a table array that is also obtained using a vlookup.

For example: the range is located in a named table (ziplookup). When I choose a city name the cell updates with the range to lookup the data in another area of the workbook. I want to select a city, then use the range ($B$5:$HQ$8) in cell C7 obtained thru a vlookup in another formula extracting the data for a chart.

My chart data cell has the formula; =vlookup($D$8,indirect($C$7),3,false)
This will pull the correct chart data for the city selected using the range for that city. The problem is I get #REF! in that cell and excel tells me that table_array is volitile. How can I get around this??

I would appreciate any suggestions!

I think that the complaint is that your Lookup_Value and your Indirect cell are within the Table area specified, thus going close to a circular reference.

Try using cells that are not within the Table range (B5:HQ8) for your value and indirect.


noted, the third fourth and subsequent times didn't give an error, but just did the lookup.


hth
---