Help with the basic formula

pratiksuhasaria

New Member
Joined
Mar 26, 2019
Messages
24
Hi everyone

Productwholesellerprice/kg
AppleABCRs 50
OrangeABCRs 30
AppleXYZRs 40

<tbody>
</tbody>

Using Product as the key i want to extract all the datas in new table like
ProductABC(Price)XYZ(Price)
AppleRs 5040
OrangeRs 30

<tbody>
</tbody>
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
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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?
 
Upvote 0
Maybe,


Book1
ABC
1Productwholesellerprice/kg
2AppleABCRs 50
3OrangeABCRs 30
4AppleXYZRs 40
5
6ProductABCXYZ
7AppleRs 50Rs 40
8OrangeRs 30
Sheet1
Cell Formulas
RangeFormula
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)))))
 
Upvote 0
=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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top