I've populated several columns (based on Spilled Ranges), which each return a subset of a larger list. The relevant named column (List1, List2, List3) is be populated based on an entry in Column D of the same row.

So my formula looks like this: INDIRECT(SUBSTITUTE(XLOOKUP(XLOOKUP(D2,INDIRECT("Table1[Header1]"),INDIRECT("Table1[Header2]")),INDIRECT("Table2[Header1]"),INDIRECT("Table2[Header2]"))," ",""))

Table1:

Table2:

This works fine. If D2 has an entry which, following each of the lookups returns "List1", the range for List1 will be shown (Lookups!$W$6#).

But here's the tricky bit. I need to accommodate invalid entries. If D2 has an item which doesn't return any of these three lists, I need it to point to the full list: Lookups!$L$6#. Using my formula above, I'm given an #N/A error if D2 contains an item which, following the lookups doesn't fall into any List.

"That's easy," you'd think. "Just add IFNA or IFERROR and all your problems will be solved!" But that's where the unexpected behaviour comes in. Using either IFNA or IFERROR means that Lookups!$L$6# is always returned, regardless of the entry in D2.

So, how do I get the List to show when it should, and get the overflow to show elsewhere?

I should also point out, each row can return a different List, so I can't have a single column at the end to take care of it all for me. And I'm going to put this formula into a Data Validation pane, so I can't add FILTER() to the formula (because Data Validation doesn't like that).

CodePudding user response：

This is because Excel 'artificially' expands the array passed as IFNA's value argument so as to be of an equal dimension to that function's value_if_na argument, populating extraneous entries with #N/A.

For example, assuming List1 is {"A";"B";"C"} and FullList is {"K";"L";"M";"N";"O";"P";"Q"}, then:

=IFNA(INDIRECT(SUBSTITUTE(XLOOKUP(XLOOKUP(D2,INDIRECT("Table1[Header1]"),INDIRECT("Table1[Header2]")),INDIRECT("Table2[Header1]"),INDIRECT("Table2[Header2]"))," ","")),FullList)

will resolve to:

=IFNA({"A";"B";"C"},{"K";"L";"M";"N";"O";"P";"Q"})

which, by virtue of the aforementioned expansion, evaluates to:

=IFNA({"A";"B";"C";#N/A;#N/A;#N/A;#N/A},{"K";"L";"M";"N";"O";"P";"Q"})

i.e.:

{"A";"B";"C";"N";"O";"P";"Q"}

Switching your INDIRECT and IFNA formulas around should resolve this:

=INDIRECT(IFNA(SUBSTITUTE(XLOOKUP(XLOOKUP(D2,INDIRECT("Table1[Header1]"),INDIRECT("Table1[Header2]")),INDIRECT("Table2[Header1]"),INDIRECT("Table2[Header2]"))," ",""),"FullList"))