Help with If, Match, Index, and Randbetween all in one formula

Please help with three different formulas.

1. Test Plan tab, Cell E4 "POS" - Look for the Max date on the General tab (Column L "Fee Effective Date" should be 4/1/2017), If there is a P2 in Column O of the General tab ("Note Code 1") then enter 12, if not enter at random 11 or 22 in cell E4 on the Test Plan tab

2. Test Plan tab, Cell C4 "Procedure Code" - Look for the Max date on the General tab Column L, if the POS on the Test Plan tab equals 12 and Column O of the General tab equals P2, Test Plan tab Cell G4 "FRM Age" equals Column F "Frm" on the General tab, Test Plan tab Cell H4 "Thru Age" equals Colum G "Thru" on the General tab, then enter at random a Procedure code from Column C on the General tab in cell C4 on the Test Plan Tab

Look for the Max date on the General tab Column L, If the POS on the Test Plan tab equals 11, Test Plan tab Cell G4 "FRM Age" equals Column F "Frm" on the General tab, Test Plan tab Cell H4 "Thru Age" equals Colum G "Thru" on the General tab, then enter at random a Procedure code from Column C on the General tab in cell C4 on the Test Plan Tab


Look for the Max date on the General tab Column U, If the POS on the Test Plan tab equals 12, and Test Plan tab Cell G4 "FRM Age" equals Column F "Frm" on the General tab, and Test Plan tab Cell H4 "Thru Age" equals Colum G "Thru" on the General tab, then enter at random a Procedure code from Column C on the General tab in cell C4 on the Test Plan Tab


3. Test Plan tab, Cell D4 "Modifier" - IF the POS on the template tab is either 11 or 12, Look for the Max date on the General tab Column L, if Procedure Code Cell C4 equals General Tab Column C, Test Plan tab Cell G4 "FRM Age" equals Column F "Frm" on the General tab, and Test Plan tab Cell H4 "Thru Age" equals Colum G "Thru" on the General tab, then enter at random a Modifier code from Column D on the General tab in cell D4 on the Test Plan Tab


IF the POS on the template tab is 22, Look for the Max date on the General tab Column U, if Procedure Code Cell C4 equals General Tab Column C, Test Plan tab Cell G4 "FRM Age" equals Column F "Frm" on the General tab, and Test Plan tab Cell H4 "Thru Age" equals Colum G "Thru" on the General tab, then enter at random a Modifier code from Column D on the General tab in cell D4 on the Test Plan Tab

I am definitely open to suggestions and simplification. Thanks in advance for your support.

Sherline

Yes, that is why I wanted to also use the randbetween to look at all of them and just select one where all the criteria is met.

That's ok so as of the date of 4/1/2017 there are no P2. Maybe on a future date there will as the General tab is updated quarterly.
Nonetheless the formula should still account for that possibility. Since there is no P2 then the output should either be 11 or 22.

Yes you are correct. Column E would be either all 12's or a mix of 11's and 22's.

Ok so now we know that the POS will be either all 12's or 11 and 22's.

2. If the POS is 12 (Test Plan Column E) then find the Max date (General Column L) and P2 (General Column O) the following criteria must all match:
Test Plan Column G "Frm Age" = General Column F "Frm"
Test Plan Column H "Thru Age" = General Column G :"Thru"

If the POS is 11 (Test Plan Column E) then find the Max date (General Column L) and General Column O is blank the following criteria must all match:
Test Plan Column G "Frm Age" = General Column F "Frm"
Test Plan Column H "Thru Age" = General Column G :"Thru"

If the POS is 22 (Test Plan Column E) then find the Max date (General Column U) and the following criteria must all match:
Test Plan Column G "Frm Age" = General Column F "Frm"
Test Plan Column H "Thru Age" = General Column G :"Thru"

The output would be placed in Test Plan Column C which is a randbetween of the procedure codes (General Column C) that match the criteria. Each line on the test plan may have a different age grouping, which is fine, and duplicated are fine also. The formula would be copied down beginning with C4 on the Test Plan

Ok, if row 4 on the test plan has a POS of 11 Frm age 21 Thru age 999 find the same value on the General Frm and Thru column (which would be several) that has the 4/1 date return any one at random of the procedure codes on the test plan C4 ie "80050". I hope that makes sense..

If anyone else have any suggestions I would appreciate it. This is the formula I am working on for the procedure code

=IF(AND(SUMPRODUCT((Table_General[Fee Effect Date]=MAX(Table_General[Fee Effect Date]))*(Table_General[Frm]=[@[FRM Age]])*(Table_General[Thru]=[@[THRU Age]]))),INDEX(Table_General[Proc Code],RANDBETWEEN(1,COUNTA(Table_General[Proc Code]))),0)

it does return a value, however it does not match the date so therefore its the wrong value.