GOLD STAR to anyone who can help with this conundrum!

I am trying to create a custom function for the formula below. I can't find directions on how to create a custom function with multiple nested formulas.

Even though this is long, there are only three input values that will change, so I was hoping that the function could only require those inputs.

My dream is to have a formula that looks something like this:

**LookupBand(LookupWorksheet, Criteria, LookupValue)**

The example formula below is in cell S2

Input descriptions:

**-LookupWorksheet:**In this example, the LookupWorksheet is ‘Pre-Test’!. The worksheet called will be different each time, but cells referenced will always be the same ($A$A and $1:$10485576). It is used as the reference array and the return array in the XLOOKUP formula.

**-Criteria:**In this example, the Criteria is S$1. The criteria will

*always*be the header of the cell in which the formula is written. It is the "text" in the LEFT formula and "within text" in FIND formula.

**-LookupValue**: This is the lookup value in each XLOOKUP. Honestly, if it weren’t for the SPILL error, I would have made the lookup value $A$A because the value is always in column A and the row of the formula. This the formula is in row 2, so the LookupValue in my dream function is $A2

Current Formula:

=IFERROR(IFS(IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<10,"0-9",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<20,"10-19",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<30,"20-29",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<40,"30-39",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<50,"40-49",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<60,"50-59",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<70,"60-69",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<80,"70-79",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<90,"80-89",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<100,"90-100"),"")

The following image is the same as the content above... just color-coded.

Thanks so much for reading... and hopefully helping!