Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
GIZZMO
 
Posts: n/a
Default How to create a lookup table with an added varable?

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

  #2   Report Post  
tjtjjtjt
 
Posts: n/a
Default

Try an INDEX and MATCH combo. Something like:

=INDEX(your_values,MATCH(product_number,Row_Headin gs,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

"GIZZMO" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 208
Default How to create a lookup table with an added varable?

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.
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 143
Default How to create a lookup table with an added varable?

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?
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I create a pivot table if the pivot table icon or menu ite. Lynn@WS Charts and Charting in Excel 1 December 16th 04 03:36 AM
How to create a calculated field formula based on Pivot Table resu dha17 Excel Discussion (Misc queries) 1 December 15th 04 06:39 AM
Index table lookup anomaly Carole O Excel Worksheet Functions 9 December 9th 04 05:33 PM
create space in line chart between points, linked to pivot table Mike -Z- Charts and Charting in Excel 1 December 7th 04 10:39 PM
Lookup Table Ben Excel Worksheet Functions 7 November 30th 04 08:05 PM


All times are GMT +1. The time now is 02:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"