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 25__2333__; 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 __332__1__3__; and Solution#11 in cell D11 is 7__3__5__2__9__33__5 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__ 459__01; 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 3__49__6__5__4; and Solution#13 in cell J13 is __45__68__9__4 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.