Dual Lookup??

  • Thread starter Thread starter Doodlemeyer
  • Start date Start date
D

Doodlemeyer

I've been wrestling with this one for a while, and finally have given up and
am going to the experts:

I have a price list. It has different pricing depending on time (this
year's rates, next year's etc) in columns. It has different products in
rows.

I need a formula that will lookup a date cell, lookup the product cell, and
return the correct price.

Any takers?

Thanks!

Marc
 
Can you give an example of what you have so far? Not an attachment, just
some formulae and cell references.
 
Hi Mark

Often the best way is to simply use a spare column, say Column "A".
Let's say your other 2 columns are "B" and "C" and Column "D" has the
value to return. In A1 put

=B1&C1

and copy down. Now select, say cell D1 and go to Data>Validation choose
the List option and use A1:A100 (or your last row) and click ok. Now in
any cell put

=VLOOKUP(D1,$A$1:$D$100,4,False)



***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Sheet 1 is the calculation sheet

A1=Date
B1=Product Code
C1= (Desired Price)

Sheet 2 is the table with the rates in it

A1:A50= Product Codes
B1:B50= Prices for Date range 1 (2004)
C1:C50= Prices for Date range 2 (2005)
D1:D50= Prices for Date range 3 (2006)

I want to type in a date and a product code in Sheet 1, and lookup the
correct price in Sheet 2 and put the value in Sheet1:C1.

Marc
 
Hi
enter the following in C1
=INDEX('sheet2'!A1:D50,MATCH(B1,'sheet2'!A1:A50,0),MATCH(YEAR(A1),'shee
t2'!A1:D1,0))
 
Hi

In case when combination of date and product code in your price list is
unique, you can use the formula
=SUMPRODUCT((Date=YourDate)*(ProductCode=YourProduct)*(Price))
where Date, ProductCode and Price are ranges in your price list, and
YourDate and YourProduct specific values (they all, or some of them, can be
cell references, or named ranges).
 
Hi
can you give an example (that is are you using an exact date or are you
looking for a date range?)
 
Plugged it in....didn't work?


Frank Kabel said:
Hi
enter the following in C1
=INDEX('sheet2'!A1:D50,MATCH(B1,'sheet2'!A1:A50,0),MATCH(YEAR(A1),'shee
t2'!A1:D1,0))
Click to expand...
 
Yes, price increases are effective as of certain date, not a YEAR.

Sheet2!B1=1/1/2004
Sheet2!C1=4/1/2004
Sheet2!D1=1/1/2005
 
Hi
try
=INDEX('sheet2'!A1:D50,MATCH(B1,'sheet2'!A1:A50,0),MATCH(YEAR(A1),'shee
t2'!A1:D1,1))
 
sorry
ignroe the previous post. Try
=INDEX('sheet2'!A1:D50,MATCH(B1,'sheet2'!A1:A50,0),MATCH(A1,'sheet2'!A1
:D1))
 
I've been wrestling with this one for a while, and finally have given up and
am going to the experts:

I have a price list. It has different pricing depending on time (this
year's rates, next year's etc) in columns. It has different products in
rows.

I need a formula that will lookup a date cell, lookup the product cell, and
return the correct price.

Any takers?

Thanks!

Marc
Click to expand...

With a table on Sheet2 set up as follows:

Product | Beginning | Beginning | Beginning
Code | of Per 1 | of Per 2 | of Per3

try this formula:

=VLOOKUP(A2,Sheet2!A1:D1000,MATCH(Dt,Sheet2!B1:D1)+1,FALSE)

A2 is the Product Code whose price you want.
Dt is the effective date of the pricing. You could substitute TODAY()

If the code does not exist, the formula will return #N/A





--ron
 
YOU ROCK.

It works!!

THANK YOU!!!


Ron Rosenfeld said:
With a table on Sheet2 set up as follows:

Product | Beginning | Beginning | Beginning
Code | of Per 1 | of Per 2 | of Per3

try this formula:

=VLOOKUP(A2,Sheet2!A1:D1000,MATCH(Dt,Sheet2!B1:D1)+1,FALSE)

A2 is the Product Code whose price you want.
Dt is the effective date of the pricing. You could substitute TODAY()

If the code does not exist, the formula will return #N/A





--ron
Click to expand...
 
Back
Top