I have Excel 365 Enterprise Version which includes the LET fx and have created LET formulas in other workbooks, but I am having difficulty with Excel accepting this formula:
=LET(
mon1,XLOOKUP($B46,'Current Month S&OP'!$A$67:$A$91,'Current Month S&OP'!H67:H91),
mon2,XLOOKUP(B46,'Current Month S&OP'!$A$67:$A$91,'Current Month S&OP'!i67:i91),
mon3,XLOOKUP(B46,'Current Month S&OP'!$A$67:$A$91,'Current Month S&OP'!i67:i91),
mon0,'Gross Sales'!$BC7,
if('Gross Sales'!$AC$44="YES",mon0+mon2+mon3,if('Gross Sales'!$AD$44="YES",mon0+mon1+mon3,mon0+mon1+mon2))
)
Excel is not recognizing this as a formula and returning the "There is a problem with this formula..." message box.
The named range of mon0 is also returning a NAME# error when I select that name, while the other names are returning 0.
Does anyone see an error in the formula that I am overlooking?
Hi Rodney,
mon1 is considered a cell reference. e.g. if you CTRL+G and enter MON1, you'll be taken to column MON.
Try changing mon1 to mon_1 and likewise for the other mon names you have.
Mynda
Duh! That was the problem. I made that fix and then reverted to a VLOOKUP as the I just didn't have the time to figure out how to make the XLOOKUP point to the right lookup array column, which is dynamic. I'll figure that one out in future, but this worked well. Thank you.
