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 attachment with a screen shot of part of my data. I have 264 individual tables of data based on specific characteristics (age, education, etc.). Each table has 2 columns: score X, score Y. I'd like to be able to pull the t-score (right column) based on the scaled score (left column) depending on the characteristics I select. For example, if A1 is someone's age and B2 is someone's years of education, etc, I'd like to pull the data from the table that matches those characteristics.
If I had only a handful of tables, I could use something like:
=IF(AND(A1<34,B2<9 [etc. for other characteristics]),LOOKUP(D2:E21),IF(AND(A1<40,B2<9 [etc. for other characteristics]),LOOKUP(H2:I21), ....
however, I know this will not work because I have 264 different tables/combinations of characteristics.
Appreciate any recommendations. Thank you!
6 Replies
In the attached I've created a single table, as I suggested was possible based on the image you've shared. I then create a column heading that captures the salient demographic data, which is then used in an INDEX and MATCH formula to find the corresponding T-score that applies to a person of the selected gender, age group, education level........
It's all in the design.
If you are able to share your full set of tables, I'd be happy to show how you could extend this approach further. Basic point: there is absolutely no need to multiple IFs and VLOOKUPs for different tables. You can do this with a single table.
Thank you for you explanation. I've spent some time learning about INDEX MATCH MATCH and have a better understanding of how that could be used for this. I'm now stuck on how to create a value for the MATCH (columns) part of the formula (what you have highlighted in yellow). I went ahead and changed my table to using your approach (single table with column headings for each demographic combination). Really appreciate your help and recommendations!
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),""))Yes, it's better to convert your data into a single table, then you can lookup easily.
First of all, although you may indeed currently have 264 separate tables, it looks (and I'll acknowledge maybe I'm missing something crucial) ...it looks as if all those you show in that image could in fact be combined into a more complex--yes!--single table. They all have exactly the same first column, for starters. So the second column could be headed with some short title that refers to the unique demographic characteristic you have designating the table....make that (in some shortened form) a column heading. The data IN the column would still be the T-score for that segment of the population that falls in the appropriate row. In fact, I'll go out on a limb and speculate that all of the other 240-odd tables may be similarly arranged, some referring to females of varying age cohorts, etc.......that all 264 current tables could be made into a single table. Again, maybe I'm missing something, but I'm certain that could be done with what you've shown.
If you were to do that change, you could use INDEX and MATCH to retrieve the desired result.
By the way, an image is at best moderately helpful. You could help us help you a lot more by posting the actual workbook.
