Custom VBA Functions with Nested Formulas

jbiehl

Board Regular
Joined
Jul 30, 2020
Messages
58
Office Version
  1. 365
Platform
  1. Windows
Good Evening,

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.
1598566828020.png


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

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Watch MrExcel Video

Forum statistics

Threads
1,127,504
Messages
5,625,191
Members
416,079
Latest member
lizziebee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top