IF formula or Xlookup?

[Antmob]

I am having some troubles figuring out how to make this work.

What I am trying to achieve:
Sheet 1 is an all reference sheet where all information is entered. Information entered is from column A through to column BA (row one of each column having its own title). I want sheet 2, to pull and display information from sheet 1 based on what is entered in column AD2,AH2 or AL2 of sheet 1.

If cell AD2 in sheet 1 contains the word “MLARC”, and cell AH2 and AL2 does not contain the word "MLARC", then display the information from row A2 through to column BA2 in sheet 2 row A2 through to BA2, If cell AH2 in sheet 1 contains the word “MLARC”, and cell AD2 and AL2 does not contain the word "MLARC", then display the information from row A2 through to column BA2 in sheet 2 row A2 through to BA2, If cell AL2 in sheet 1 contains the word “MLARC”, and cell AD2 and AH2 does not contain the word "MLARC", then display the information from row A2 through to column BA2 in sheet 2 row A2 through to BA2.

What formulas I've tried:
I tried this first:
=IF('All Referrals - Open to MLPS'!$AD2="MLARC",'All Referrals - Open to MLPS'!B2,"")

but it didnt take it into consideration of the AH2 or AL2.
=IF(AND('All Referrals - Open to MLPS'!AD2="MLARC",NOT(OR('All Referrals - Open to MLPS'!AH2="MLARC",'All Referrals - Open to MLPS'!AL2="MLARC"))),'All Referrals - Open to MLPS'!A2:BA2,IF(AND('All Referrals - Open to MLPS'!AH2="MLARC",NOT(OR('All Referrals - Open to MLPS'!AD2="MLARC",'All Referrals - Open to MLPS'!AL2="MLARC"))),'All Referrals - Open to MLPS'!A2:BA2,IF(AND('All Referrals - Open to MLPS'!AL2="MLARC",NOT(OR('All Referrals - Open to MLPS'!AD2="MLARC",'All Referrals - Open to MLPS'!AH2="MLARC"))),'All Referrals - Open to MLPS'!A2:BA2)))
Dont know if you are able to help with this. All and any suggestions appreciated.

 

[Antmob]

I've attached the spreadsheet so you can have a visual of what I am trying to do.

 

[p45cal]

Does your version of Excel have CHOOSECOLS as a function? (I'm not sure what version 16 means.)

Cell A2 of MLARC sheet:

=FILTER(IF('All referrals'!A2:BA9="","",'All referrals'!A2:BA9),BYROW('All referrals'!AD2:AL9,LAMBDA(a,SUM(--ISNUMBER(SEARCH("MLARC",CHOOSECOLS(a,1,5,9))))))=1,"none")

or a bit shorter:

=LET(a,'All referrals'!A2:BA9,FILTER(IF(a="","",a),BYROW(a,LAMBDA(a,SUM(--ISNUMBER(SEARCH("MLARC",CHOOSECOLS(a,30,34,38))))))=1,"none"))

Another, small variation which also makes it into a LAMBDA formula meaning it can be added as a Name then used as a function:

=LAMBDA(a,FILTER(IF(a="","",a),BYROW(a,LAMBDA(a,XOR(ISNUMBER(SEARCH("MLARC",CHOOSECOLS(a,30,34,38)))))),"none"))

and used as in cell A2 in the attached thus:
=FilterMLARC('All referrals'!A2:BA9)
I've left you to add the headers in row 1 of that sheet.