Help with the basic formula

[pratiksuhasaria]

Hi everyone

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]wholeseller[/TD]
[TD]price/kg[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]ABC[/TD]
[TD]Rs 50[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]ABC[/TD]
[TD]Rs 30[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]XYZ[/TD]
[TD]Rs 40[/TD]
[/TR]
</tbody>[/TABLE]

Using Product as the key i want to extract all the datas in new table like
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]ABC(Price)[/TD]
[TD]XYZ(Price)[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Rs 50[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Rs 30[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I used formula to get Rs 50

= if("Apple" = index(A:C,MATCH("Apple",$A:$A,0),MATCH("Product",$A$1:$C$1,0),if("ABC" = index(A:C,MATCH("Apple",$A:$A,0),MATCH("wholeseller",$A$1:$C$1,0),INDEX($A$1:$C$4,MATCH("Apple",$A:$A,0),MATCH("Date",$A$1:$C$1,0)),""),"")

but when i use same formula except in place of "ABC" "XYZ" is used at XYZ column the following fuction becomes false

= if("Apple" = index(A:C,MATCH("Apple",$A:$A,0),MATCH("Product",$A$1:$C$1,0),if("XYZ" = index(A:C,MATCH("Apple",$A:$A,0),MATCH("wholeseller",$A$1:$C$1,0),INDEX($A$1:$C$4,MATCH("Apple",$A:$A,0),MATCH("Date",$A$1:$C$1,0)),""),"")

after debugging i came to know as there are two same value in product ie Apple it is considering only the first index it encounter but ignoring the rest.

But i dont want this i want each apple's index to be different.

Please Help

 

[RoryA]

Your desired output appears to be exactly what a pivot table would produce - is there a reason you can't just use one of those?

 

[RasGhul]

Maybe,

Book1
	A	B	C
1	Product	wholeseller	price/kg
2	Apple	ABC	Rs 50
3	Orange	ABC	Rs 30
4	Apple	XYZ	Rs 40
5
6	Product	ABC	XYZ
7	Apple	Rs 50	Rs 40
8	Orange	Rs 30
Sheet1

Cell Formulas
Range	Formula
B7	=IF(COUNTIFS($A$2:$A$4,$A7,$B$2:$B$4,B$6)=0,"",INDEX($C$2:$C$4,SUMPRODUCT(($A$2:$A$4=$A7)*($B$2:$B$4=B$6)*(ROW($A$2:$A$4)-ROW($A$1)))))

 

[pratiksuhasaria]

Thanks A lot it worked

 

[Jborg]

=IFERROR(INDEX($C$2:$C$4,SMALL(IF(($A$2:$A$4=$A7)*($B$2:$B$4=B$6)=1,ROW($C$2:$C$4)-ROW($C$2)+1),1)),"")

Ctrl + Shift + Enter

Should work