Hello Fellow Ozgrids:
I have been have a tough time on finding a solution to my problem. I’ve search both Ozgrid Excel help forum and YouTube, but could not seem to find a resolve. Here’s the issue:
I am trying to find matches of any order from the Draw 4 or Draw 3 listed in columns E and K when compared against the solution columns D and J.
The formula that I currently have here is:
=SUMPRODUCT(--ISNUMBER(SEARCH(E3,D3)))=COUNTA(E3)……copied down seems to only work if the solution number is in the exact order of the corresponding draw 4 or draw 3 number.
This is not what I need the formula to do. I need the formula to be able to provide a result if the solution is in any order or exact order of the DRAW 3 or 4. Therefore, the draw 4 and draw 3 results would have also included the highlighted cells as TRUE. When the formula is working like I require the results would show 3 TRUE results for both Draw 4 and Draw 3.
As an example: Solution #4 in cell D4 is 252333; hence a TRUE result is provided only because the formula finds Draw 4 2333 in exact order of the solution number.
However, Solution#9 in cell D9 is 33213; and Solution#11 in cell D11 is 73529335 both results in a FALSE match because it’s not in order of Draw 4, but it does contain all four numbers to yield a resulting match.
As an example: Solution #1 in cell J1 is 45901; hence a TRUE result is provided only because the formula finds Draw 3 459 in exact order of the solution number.
However, Solution#3 in cell J3 is 349654; and Solution#13 in cell J13 is 456894 both results in a FALSE match because it’s not in order of Draw 3, but it does contain all three numbers to yield a resulting match.
Sample file attached to illustrate the issue.