Forum Discussion
264 nested IF statements -- alternative?
- Jun 06, 2025
OK. I've modified your data.xlsx to make, I trust, how to do this clear. Here's the basic Input and Output area
I usually follow a convention of using yellow background in any cell that calls for entry by the user, and have followed that convention here. Other than the "Raw Score" cell, I've also used Data Validation to ensure that only acceptable values are entered. In the two cases, there are only two choices of appropriate text. In the others, a whole number is required, within the acceptable range.
Then, in the gray areas, the calculations are done, in both cases using INDEX and MATCH. The tables for those retrievals are on a separate "Behind the Scenes" tab. It looks like this:
There are formulas in the column of four category identifiers. And a final formula in the gray background area, where the column heading is calculated, simply by concatenating the strings above. I do this in the simplest way, using the "&" symbol. It could be done other ways, but really not necessary here. I also should note that some folks would combine all these formulas into a single one; that's entirely possible, but for your sake in understanding all the steps, I've chosen to break it into separate steps.
I also did not take the trouble to verify all the outputs when changing the inputs on that primary screen. You should do that.
Let me know if you have questions.
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),""))