Selective -Multiple Lookup - Using Formulas Only

[Anwer]

I an trying to flag each pattern generated out of a permutation of (9,3) as valid and invalid and need some guidance. The data and the approach take no far is listed below. Please feel free to email directly to anwer@shahabuddin if the copy and paste below is unreadable or should there be any other question.

thanks & regards,

Col A B C D E F G H I J J L M
Row 1 T1 T2 Tie1 invalid Possible Results of Fixture 1 T1 T2 Tie1 1 < Group
Row 2 T1 T2 T3 invalid Possible Results of Fixture 2 T3 T4 Tie2 2 < Group
Row 3 T1 T3 T5 ok Possible Results of Fixture 3 T5 T6 Tie3 3 < Group
Row 4 T1 T4 T5 ok
Row 594 altogether up to row 504

Objectives
1. For row place "ok" or "invlid" in Col D
ok - if An, Bn, Can contain only 1 value from eachgroup
invalid - all other patterns
2. Solution is required using formulas not macro
Bakground
Rows 1 to 504 contain pemutation of (9,3)=9x8x7
Each row contains one patern of results from three fixtures
Each fixture can have three results shown in I3:K3
A valid pattern implies at most one entry from each set of posible results
This is shown as Group 1, Group 2, Group 3 in I1: M3
Approach Look up each A1, B1, C1, try to determine pattern and flag
Was able to look up A1, B1, C1 via Vlookup ad got a concatenatd "111".
Intended to countthe 1's, 2's and 3's and if the freq was > 1, label "invalid"
The difficulty ecountered with Vlookup was that it worked fine till the range
was I1:L1. When extened to I1:L3, it produced #NA.
Thought of trying left lookup but Match seems to require a Column array.


Email: anwer@Shahabuddin.com

Note: I can send the spreadsheet if an email address is provided.

 

[Anwer]

The OR with Transpose did not work. Since then I have broken the problem into parts, added three columns to tabulate each An, Bn, Cn search. Using those to determine valid and invalid patterns is fairly straighforward and works fine.

Thanks for all the help and time. It is greatly appreciated.

Regards,

anwer

 

[Jerry Sullivan]

Glad you found a workable solution, but it's unfortunate that you have to use the helper columns. The formula I posted with OR and Transpose worked for me on xl2007. Is that the version you are using?