Using Index Match etc. for price list

[Pierre]

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

 

[Duke Carey]

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

 

[Pierre]

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.

 

[Duke Carey]

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?

 

[Pierre]

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