vlookup -- table_array as reference to named range

J

Janet Panighetti

Dear geniuses,

I have price sheets coming from several sources with the same format and
products, but containing difference prices.

I am trying to build a dataset (table) from these sources which I could then
link to Access for querying.

Given each price sheet source is a seperate worksheet in a workbook, I have
given each sheet the named range of its pricing rate. For example, RATE_165
represents the pricing sheet which contains the prices for the "165" rate;
RATE_180 represents the pricing sheet which contains the prices for the "180"
rate.

I want to be able to build another sheet called "PricingData" and have it
look something like this:

PricingData:

A B C
1 Price Sheet Product Price
2 RATE_165 Widget $5.98
3 RATE_165 Gadget $10.00
4 RATE_180 Widget $8.45
5 RATE_180 Gadget $14.95

Where the formula in C2 would be something like:

=vlookup(b2,<value of a2 for the table_array>, 5,false)

And would bring back the price from the range "RATE_165" for a "Widget" in
that range.

Is it possible to convert the value in A2 into a reference for the named
range "RATE_165" within the vlookup function?

Thanks in advance! :)

Janet
 
J

Janet Panighetti

This is also posted as subject VLOOKUP -- DYNAMIC TABLE_ARRAY VALUE.

Apologies for the duplicate postings. Answer either or both if you like. :)

Janet
 
J

JW

Have a look at the INDIRECT function. Should allow you to use the A2
value as a reference name.
 
J

Janet Panighetti

Thanks. I tried indirect but it wasn't working.

I think maybe I hadn't saved the workbook first.

*blush*

Thanks!

Janet
 

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