data structure and my problem
 

Hi
thanks for the reply. I tried including the entire table,
but the error now is N/A for all the entries..
the problem is..
i have a small list of products and I want to know their
sales price(the last time they were sold). I have a big
excel sheet with historic sales data in seperate
worksheet. Now, I want to use vlookup to find the sales
price of the list of products. the sales proce is in
worksheet'historic_sales' in coloumn 'P'. The small list
of products whose prices i need are in worksheet 'list'.

Hope i made my problem clear.
thanks a lot for a help
cheers,
samantha

For demo purposes, I'll assume the following:
1)Sales are entered on the Historic Sales sheet in chronological order.
2)Products are listed in col O on that sheet

Enter a product in cell A1 on Sheet 1.

Enter this formula in cell B1:
=INDEX('Historic Sales'!$P$1:$P$1000,MAX(IF('Historic
Sales'!$O$1:$O$1000=A2,ROW('Historic Sales'!$O$1:$O$1000))),1)

Commit that array formula by pressing [Ctrl]+[Shifr]+[Enter]

ALSO: If you have more that 1000 rows of sales data, change the 1000 to
whatever is appropriate.

If that works, copy the formula into subsequent cells.

I hope that helps.

Regards,
Ron

Hi

Add into products table a column for last price change date. Now, when on
sales sheet dates start from A2, products start from B2, and prices start
from C2, and last row is 1000, and when on products sheet products start
from A2, LastChangeDate from B2 and LastPrice from C2, then:

Into cell B2 on products sheet enter array (with Ctrl+Shift+Enter) formula
like
=MAX((Sales!$B$2:$B$1000=$A2)*(Sales!$A$2:$A$1000) )

Into cell C2 on products sheet enter formula like
=SUMPRODUCT(--(Sales!$A$2:$A$100=$B2),--(Sales$B$2:$B$1000=$A2),Salesa!$C$2:
$C$1000)

Copy formulas down on products sheet

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"samantha" wrote in message
...
Hi
thanks for the reply. I tried including the entire table,
but the error now is N/A for all the entries..
the problem is..
i have a small list of products and I want to know their
sales price(the last time they were sold). I have a big
excel sheet with historic sales data in seperate
worksheet. Now, I want to use vlookup to find the sales
price of the list of products. the sales proce is in
worksheet'historic_sales' in coloumn 'P'. The small list
of products whose prices i need are in worksheet 'list'.

Hope i made my problem clear.
thanks a lot for a help
cheers,
samantha