How to create a lookup table with an added varable?

[Guest]

I need to create a lookup formula to address two variables simultaneously,
one a product number, the other a product date, to return a price for that
specific product on that date. The lookup table has column headers of dates
by quarter, e.g., 20031, 20032, and row headers of SKUs or individual product
numbers. The table contains price data per SKU per quarter. I have a
working table of thousands of sales by quarter for which I need to insert the
price for that quarter. I thought a lookup table would be one way to
automatically insert these prices.

Any suggestions?
Thank you

 

[Guest]

Try an INDEX and MATCH combo. Something like:

=INDEX(your_values,MATCH(product_number,Row_Headings,0),MATCH(quater,Column_Headings,0))

your_values will be the table of data excluding the row and column headings.
product_number is the product you are looking up.
quarter is the quarter you are looking up.

For a detailed breakdown see:
http://www.contextures.com/xlFunctions03.html

tj

 

[sean]

I am using a drop down list. depending on the numbers I select I want those
to reference a number in that row and a number in that column and return the
number in that intersection. I am using a separate tab for my worksheet and
another tab with my chart. For example, if I select 3.0 in one cell and .94
in another cell, on my worksheet, those numbers relate to row and column
value on a separate tab containing a chart. I want the return value to be
where those values intersect in my chart.

 

[MyVeryOwnSelf]

I am using a drop down list. depending on the numbers I select I want

those to reference a number in that row and a number in that column
and return the number in that intersection. I am using a separate tab
for my worksheet and another tab with my chart. For example, if I
select 3.0 in one cell and .94 in another cell, on my worksheet, those
numbers relate to row and column value on a separate tab containing a
chart. I want the return value to be where those values intersect in
my chart.

More details about the data layout and about the expected results would
allow a helpful response. Maybe you could post a small example.

Terms like "relate to" are kind of vague. Better would be terms like "equal
to" or "between two values" or whatever is intended.

Does the "chart" worksheet have numbers in row 1 for "relating to" the 3.0
and numbers in column A "relating to" the .94? Are those numbers
monotonically increasing? Decreasing?