Forum Discussion
Index matching numbers across a row with dupes
Hey folks! I'm pretty green to excel functions and could use some help:
I inherited a report (that has been scrubbed) that is using a simple index-matching formula to pull the top 4 items purchased, however, recurring numbers in the row return duplicate matches. In this scenario, the first column are the names of people who purchased the items and the top columns are the names of the items purchased. The below numbers are the quantity purchased.
How would I construct a formula that...
1) returns unique item names when the item purchase quantities are identical and
2) when the item quantity is zero, the formula returns a blank rather than the first item with a zero?
Any help would be greatly appreciated!! Thanks so much in advance!
John
Maybe something like:
=TAKE(SORTBY(FILTER($B$1:$I$1,$B2:$I2>0,""),FILTER($B2:$I2,$B2:$I2>0,0),-1),,4)
Maybe something like:
=TAKE(SORTBY(FILTER($B$1:$I$1,$B2:$I2>0,""),FILTER($B2:$I2,$B2:$I2>0,0),-1),,4)- I got this formula to work well for me! Thank you so much!!
=IFERROR(INDEX($B$1:$I$1,MATCH(1,($B3:$I3>0)*($B3:$I3=LARGE($B3:$I3,COLUMN(A$1)))*(COUNTIF($N3:N3,$B$1:$I$1)=0),0)),"")An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell O3 and filled across range O3:R5.
- Thanks so much for your response!! I played around with the function a bit and got it to work. I'm going to continuing picking it apart to learn more from it. Thanks again!
