The following is the data
Name Product Cost
Tom Banana 10
Tom Mango 20
Tom Apple 30
Harry Banana 40
Harry Mango 50
Harry Apple 60

A9 = Tom
B9 = Apple
C9 =INDEX($A$1:$C$7,
MATCH(1,(($A$1:$A$7=A9)*($B$1:$B$7=B9)),0),MATCH(" Cost",$A$1:$C$1,))
change the column heads and range according to your data.
You can find help about array formula in excel.
copy down the formula in C9 and press ctrl+shift+enter.
with regards
Sridhar

"yshridhar" wrote:

Array formula has to be enetered by ctrl+shift+enter. Not just by pressing
enter.
a1 = name
b1 = product
c1 = cost
change the column heads according to your data.
You have to enter the formula by pressing ctrl+shift+enter
with regards
Sridhar

"Nimish Shah" wrote:

Hi Sridhar,

I have tried the formula but not working, showing as #N/A.
I could not understand: match("cost",$a$1:$c$1,)).
I have never used the ARRAY formula.

Please help.

Regards,

Nimish






"yshridhar" wrote:

Hi Nimish
Say if your data table is in A1:C7;
Try it in C9
C9 =INDEX($A$1:$C$7, MATCH(1,(($A$1:$A$7=A9)*($B$1:$B$7=B9)),0),
MATCH("Cost",$A$1:$C$1,))
it is an array formula. you have to use ctrl+shift+enter not just enter
A9= Name
B9 = Product
with regards
Sridhar


"Nimish Shah" wrote:

Dear Sir,

I am in search of a formula to fill up the cells of "Price per each" column ?

Data Available
Farmer Product Price Per Each Farmer Product Price Per Each

Tom Apple Tom Apple 10
Harry Apple Tom Mango 15
Harry Banana Tom Banana 5
Tom Apple Harry Apple 20
Harry Mango Harry Mango 30
Harry Apple Harry Banana 7
Tom Mango
Harry Mango
Tom Banana
Tom Mango
Tom Banana
Tom Apple
Harry Apple
Tom Mango

Regards,

Nimish