Hello, I'm new VBA and have been trying to covert a standard Index Match function to a User Defined Function in VBA to simplify the formula for users. (As I understand it, since the cell the function is in will change a UDF is required.) I have three functions I'm trying to figure out. I'm using the index match because the column position being searched for could change (although I'm not tied to that if there are better options). I'm using PowerPivot as well as referencing different sheets/tabs within the same workbook. I'm hoping to next these formulas within sumifs if possible. I've attached a picture of the full formula and the tabs, hopefully that helps. Unfortunately, I can't install the XL2BB add-in for the spreadsheet. Could someone please help? Thank you in advance!
INDEX(Financials!$A:$XDF,1,MATCH("Year",Financials!$A$1:$XDF$1,0))
"<= "&MAX(INDEX(DateSelection!$A:$XDF,1,MATCH("Week Number",DateSelection!$A$1:$XDF$1,0)))
XLOOKUP(MAX(INDEX(DateSelection!$A:$XDF,1,MATCH("Week Number",DateSelection!$A$1:$XDF$1,0))),INDEX(DateSelection!$A:$XDF,1,MATCH("Week Number",DateSelection!$A$1:$XDF$1,0)),INDEX(DateSelection!$A:$XDF,1,MATCH("Year",DateSelection!$A$1:$XDF$1,0)))
INDEX(Financials!$A:$XDF,1,MATCH("Year",Financials!$A$1:$XDF$1,0))
"<= "&MAX(INDEX(DateSelection!$A:$XDF,1,MATCH("Week Number",DateSelection!$A$1:$XDF$1,0)))
XLOOKUP(MAX(INDEX(DateSelection!$A:$XDF,1,MATCH("Week Number",DateSelection!$A$1:$XDF$1,0))),INDEX(DateSelection!$A:$XDF,1,MATCH("Week Number",DateSelection!$A$1:$XDF$1,0)),INDEX(DateSelection!$A:$XDF,1,MATCH("Year",DateSelection!$A$1:$XDF$1,0)))
