Conditional formula based on multiple input variations

[Ozwilly]

I have a worksheet that I use to check lottery results. I gained some valuable assistance from forum members some years back in putting it together. It uses a series of formulas to identify the combination of numbers created, and then using an array formula provides a text entry based on that combination. The array formula is as follows:

=INDEX({"","","Four","Three","One";"","Five","Four ","Two","One"},MATCH(SUMPRODUCT(1-ISNA(MATCH(D5:I5,J$3:K$3,0))),{0;1}),MATCH(SUMPRODUCT(1-ISNA(MATCH(D5:I5,D$3:I$3,0))),{0,3,4,5,6}))

I would love to say I understand this formula however unfortunately don't, and now need to modify it. Currently it looks at the data in each row from D5:I5 on the attached, and checks it against the data in D3:K3. If it finds all 6 numbers from D3:I3 it will return the text 'One" in the column U. If it finds 5 of the numbers from D3:I3 and at least one if not both of J3 or K3, it will return the text "Two". If it finds 5 numbers from D3:I3 in each row it will return the text "Three". If it finds 4 numbers it will return the text "Four". If it finds 3 numbers from D3:I3, and either or both of J3 & K3, it will return the text "Five".

I now wish to modify it so that if it finds in each row down from D5:I5, at least one if not two numbers from D3:I3 and BOTH J3 & K3, it will return the text "Six"

I would greatly appreciate assistance on how to modify this formula to make this change. I would also really appreciate an explanation on how this formula works, as I am having difficulty fathoming out how it is finding these combinations and determining what text entry is relevant, and am keen to understand more how this is working for my own learning


PS. Attachment is in Excel 2007.

[NBVC]

Try this modification:

Please Login or Register  to view this content.

[Ozwilly]

Try this modification:

Please Login or Register  to view this content.

Thanks so much for your help I have entered the formula and it works except for two combinations from what I can see. I have updated on the attached file to show what the combinations are and what text should be reflected. The issue is when I enter 3 numbers and two supplementary numbers, the model is feeding back "Six" when it should be "Five", based on the higher combination of three numbers plus one or two supplementary numbers.

Technically both are correct, as one or two numbers plus two supplementaries generates "Six", however the higher combination of "Five" should be the default value. Similarly, four numbers and two supplementary numbers is showing "Six" when it should default to the higher combination of "Four" for four straight numbers. I had not thought of this when asked my query Hoping there is a way to modify the formula to include these defaults where there are conflicts?

[NBVC]

Try:

Please Login or Register  to view this content.

[Ozwilly]

Pure genius thanks That is awesome!!

Would it be possible if you don't mind to give me a brief overview of how this formula works for my own learning? I understand INDEX MATCH, however cannot quite understand some of the other elements of this formula. In particular, the use of the array at the start showing {"","","","Four","Three","One";"","","Five","Four ","Two","One";"","Six","Five","Four","",""}. Why does it use the multiple parentheses at the start and then show 'four three one' before going into further parentheses? Similarly the use of an array to show {0,1,2} in the MATCH section, and at the end using an array showing all 6 numbers in {}.

Much appreciated

[NBVC]

It's not that easy to explain. If you go to the Formulas tab, then go to Evaluate Formula in the Formula Auditing section, you can follow the evaluation process to see how it arrives at the final result.

Basically if you notice, within the INDEX({...} part, there are several entries separates by commas, and then there are some separated by semi-colons. The semicolons represent row breaks, so really we have a 3 row x 6 column matrix to be indexed against. The first Match() function determines the row number to index to, and the second Match() function indicates the Column number to index to.

The first Match function: MATCH(SUMPRODUCT(1-ISNA(MATCH(D5:I5,J$3:K$3,0))),{0;1;2}) works like this:

First the inner MATCH() looks to see if any matches to D5:I5 range are found in J3:K3, the ISNA() checks if any #N/A errors returned indicated no matches, the 1-ISNA(MATCH()) basically reverses those TRUE/FALSES and coerces them to 1s for TRUE and 0 for FALSE. (note: we could have reduced that by using (ISNUMBER(MATCH(D5:I5,J$3:K$3,0)))+0 to eliminate the reversing, but I was just following the method of the original formula you had been given). The Sumproduct adds up the 1's to give a total of matched items. Then the outer MATCH() tries to match that sum to the array {0;1;2}. Note that this MATCH() doesn't have the 3rd optional 0 argument to indicate exact match. It finds the last number that is less than or equal to the lookup value... so if the sum is 0, it returns 0, if the sum is 1, it returns 1, if the sum is 2 or greater, it returns 2. I added the 3rd argument, because you indicate a condition that if both numbers in J3:K3 are matched, you want a different result. Previously it was enough to have just 1 match to get an alternate result to no matches. So now that result determines the row position in the INDEX array (i.e. it determines which of the semi-colon separated groups it will look in).

Then next MATCH function: MATCH(SUMPRODUCT(1-ISNA(MATCH(D5:I5,D$3:I$3,0))),{0,1,3,4,5,6})) works exactly the same way determining ultimately in which row (or comma separated value within the chosen semi-colon separated group to go to) and get the final indexed value. Again the {0,1,3,4,5,6} lookup range determines is used since the MATCH() function doesn't have the 0 exact argument, so it looks for last number that is less than or equal to lookup (summed) value.... there is no 2, since a sum result of 2 or 3 would let you choose the 3rd column of the specific row chosen in first MATCH().

Note: The "" values correspond to blanks for when there are no matches to your various conditions.. it is just a matter of coordinating them correctly to get that desired result.

It's a bit long-winded, but it you use the EVALUATE function as you follow the above, I think it will become clearer.