SOLVED

# Index matching numbers across a row with dupes

Copper Contributor

# 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

4 Replies
best response confirmed by johngrade (Copper Contributor)
Solution

# Re: Index matching numbers across a row with dupes

Maybe something like:
=TAKE(SORTBY(FILTER(\$B\$1:\$I\$1,\$B2:\$I2>0,""),FILTER(\$B2:\$I2,\$B2:\$I2>0,0),-1),,4)

# Re: Index matching numbers across a row with dupes

``=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.

# Re: Index matching numbers across a row with dupes

I got this formula to work well for me! Thank you so much!!

# Re: Index matching numbers across a row with dupes

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!