I’m working with a complicated spreadsheet that has lots of connected formulas. Two of my columns stopped showing values and I need to figure out what went wrong.
The formulas in columns P and O are really confusing me. Can someone help explain how these work?
=IF(IsError(IFS($E:$E = "Product","")),IF($B:$B = "SKU","RFC 2m avg",""),IF(ISNUMBER(Index(DATA,$G:$G,5)),Index(DATA,$G:$G,5),0))
=IF(ISERROR(IFS($E:$E = "Product","")),IF($B:$B = "SKU","RFC current month",""),Index('Products DATA'!$S:$S,MATCH($B4,'Products DATA'!$B:$B,0)))
I think the issue might be in how these nested conditions work together but I can’t trace through the logic properly.
hey, those formulas are a mess. ur IFS function inside ISERROR is backwards - IFS throws an error when no conditions match, so IF(ISERROR()) will always trigger. break them into separate cells first to see what’s failing.
Your IFS logic is broken. The main issue is IFS($E:$E = "Product","") - you can’t compare entire columns like that. It’ll always throw an error, so ISERROR keeps returning TRUE and never gets to your INDEX parts. I ran into this same thing when I tried using whole column references with IFS. Change $E:$E = "Product" to $E4 = "Product" or use a specific range like $E$1:$E$1000. IFS needs row-by-row evaluation, not array comparisons. Fix those column references and your nested IFs should start working. Then you can see if your INDEX functions are actually pulling the right data.
Your formulas have a bigger issue than just the IFS problem. That INDEX syntax is wrong - Index(DATA,$G:$G,5) doesn’t work because you’re mixing a named range with column references. Pick one: either INDEX(DATA,row,column) if DATA is actually a range, or INDEX($G:$G,5) to grab the 5th item from column G. And those full column references like $B:$B and $E:$E? They’re killing your performance. I made this mistake before and my spreadsheet kept timing out. Switch to actual ranges like $B$1:$B$1000 and fix the INDEX structure first. Deal with the IFS stuff after. Just fixing the performance might clear up those missing values you’re seeing.