Forum Discussion
Return a combination of numbers from a list
I'm looking to learn how to return a winning combination from the list on the right (winning combinations) without sorting the Input from the user In my example "combination 3" would show in the ...
I'd use a helper column. In the screenshot below it is column P.
The formula in P3 is
=SUMPRODUCT(COUNTIF(J3:N3,$C$3:$G$3))
It counts the number of matches (correct entries) between the input and Combination 1.
Fill down from P3 to P5.
The winning combination is given by the formula
=INDEX(I3:I5,MATCH(5,P3:P5,0))
If the user hasn't entered a winning combination, this will return #N/A. You can change this by using IFERROR:
=IFERROR(INDEX(I3:I5,MATCH(5,P3:P5,0)),"No winning combination")