I have a formula that relies on two different inputs: the first is an integer value between 0 and 5, and the second is the rounded sum of values in an array. I would like to have users input values for both of these and then have a formula determine the desired output. The problem is right now I have 6 combinations for the first variable and each one results in a change in how the second variable is used. As such, I would need a very large number of IF statements to get this working.
I have tables set up elsewhere, one for each of 0 to 5, that determine the correct values. My question is, is there a way to get Excel to do the following?
Check whether 0 to 5 has been placed in, say, cell
Go to the sheet containing that number.
INDEX(...,MATCH())from the table in that sheet based on both the number of values placed in the array and what the values are.
I'm wondering if it might make more sense to avoid the tables altogether and instead try to calculate directly, but I can't see a way to do that that doesn't result in a massive set of nested
IFs. Let me know if anything here is unclear and I'll try to explain it as best I can.