Forum Discussion
264 nested IF statements -- alternative?
Apologies if this is a duplicate entry. I can't seem to find what I thought I posted earlier. I'm looking for alternatives to using LOOKUP when there are numerous nested IF statements. Please see a...
One way could be naming your table in a logical manner so that we can use formulas to detect the table. In the attached file I have named your tables in this rules Table_7_20, Table_7_35 and so on.... Here 7 is the lowest education year and 20 is the lowest age for that table. Same rules for other tables naming.
And then use the following formula. See the attached file for more details.
=LET(t,INDIRECT("Table_" & XLOOKUP(B2,{7,9},{7,9},,-1) & "_" & XLOOKUP(A2,{20,35,44},{20,35,44},,-1)),
XLOOKUP(C2,CHOOSECOLS(t,1),CHOOSECOLS(t,2),""))