I have a workbook where in sheet 1 there is a list of items (such as apple,
pear, orange). In sheet 2 these items are listed with various prices. How
can I set up a formula that will take each item in the list in sheet 1 and
find all the matches in sheet 2 and then determine the average price
associated with it? I am looking for a forumla I can put in sheet 1 and then
copy for the entire list so I won't have to retype the forumla each time.
Your help is greatly appreciated! Thanks.

Sheet 1
apple
orange
pear

Sheet 2
apple $1
apple $2
pear $3
pear $4
pear $3
orange $6
orange $7
orange $3

If your sheet1 criteria (apple, orange, pear) are in A1 down and list of
fruits is in sheet2 column A with dollar amounts in sheet2 column B then
use this formula in sheet1 B1 copied down

=SUMIF(Sheet2!A:A,A1,Sheet2!B:B)/MAX(1,COUNTIF(Sheet2!A:A,A1))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=561876