Using Index Match etc. for price list
 

Maybe someone here knows:
Each tab represents a single item for sale
Have several customers listed in Col A.
There are multiple quotes to a customer for that item, however I want
to pickup the most
recent price. . .
IOW

Cust Qty Date Price
Acme 5 1/1/05 15.00
Acme 10 1/1/05 12.00
Acme 25 1/1/05 10.00
Acme 50 1/1/05 8.00
Acme 5 1/1/06 18.00
Acme 10 1/1/06 16.00
Acme 25 1/1/06 13.00
Acme 50 1/1/06 11.00
Ford 5 1/1/05 18.00
Ford 10 1/1/05 16.00
Ford 25 1/1/05 13.00
Ford 50 1/1/05 11.00
Ford 5 1/1/06 18.00
Ford 10 1/1/06 16.00
Ford 25 1/1/06 13.00
Ford 50 1/1/06 11.00

We'd like to enter the customer name, and pull in the most recent
pricing.

Note:
This will match the first instance of the customer, but am looking for
the most recent date to follow:
=INDEX('Price List by Product'!$A:$J,MATCH($E20,'Price List by
Product'!$E:$E,0),6)

Thanks for your thoughts on this.

Pierre

Using Index Match etc. for price list
 

Your example shows several prices for the same date, varying on the basis of
volume. How do you take that into account?

"Pierre" wrote:

Maybe someone here knows:
Each tab represents a single item for sale
Have several customers listed in Col A.
There are multiple quotes to a customer for that item, however I want
to pickup the most
recent price. . .
IOW

Cust Qty Date Price
Acme 5 1/1/05 15.00
Acme 10 1/1/05 12.00
Acme 25 1/1/05 10.00
Acme 50 1/1/05 8.00
Acme 5 1/1/06 18.00
Acme 10 1/1/06 16.00
Acme 25 1/1/06 13.00
Acme 50 1/1/06 11.00
Ford 5 1/1/05 18.00
Ford 10 1/1/05 16.00
Ford 25 1/1/05 13.00
Ford 50 1/1/05 11.00
Ford 5 1/1/06 18.00
Ford 10 1/1/06 16.00
Ford 25 1/1/06 13.00
Ford 50 1/1/06 11.00

We'd like to enter the customer name, and pull in the most recent
pricing.

Note:
This will match the first instance of the customer, but am looking for
the most recent date to follow:
=INDEX('Price List by Product'!$A:$J,MATCH($E20,'Price List by
Product'!$E:$E,0),6)

Thanks for your thoughts on this.

Pierre

Using Index Match etc. for price list
 

Duke Carey wrote:
Your example shows several prices for the same date, varying on the basis of
volume. How do you take that into account?


Duke, thanks for having a look.

If I can locate the most recent line of a given customer, I can add a
+1, +2 etc. before the desired column which contains the pricing. . .
=INDEX('Price List by Product'!$A:$J,MATCH($E20,'Price List by
Product'!$E:$E,0),6)

(Before the "6", it would show . . .0)+1,6) or so it would seem.

Am attempting to populate the recent 5 price breaks into a table.

Thanks.

Using Index Match etc. for price list
 

Duke Carey wrote:
I think you can find the row number of the first occurrence of the most
recent dates for ACME by using this formula, entered with Ctrl-Shift-Enter

=MATCH("Acme"&MAX(IF(A1:A21="Acme",C1:C21)),A1:A21 &C1:C21,0)

where A1:A21 contains the customer names and C1:C21 contains the dates

Does that get you what you want?


Duke,

{=MATCH("Ford"&MAX(IF(A3:A100="Ford",E3:E100)),A3: A100&E3:E100,0)}

successfully returns the row number on the page which contains the
latest data.
Thank you.
2 questions:
How do I obtain the price in this row from a cell 5 columns to the
right?
and
How do drop down one (or more) row(s) to obtain the same information
from an additional price break?

many thanks.
Pierre