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?

  1. Check whether 0 to 5 has been placed in, say, cell A2.

  2. Go to the sheet containing that number.

  3. 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.

  • It might help if you included an example with the value of A2, the second user input value, the name of the desired sheet, what the lookup table on the desired sheet looks like, and what the expected output is. – Excellll Jul 25 '14 at 16:24
  • You might want to use some cells somewhere for intermediate calculations. This is easier to manage IMO than giant nested formulas. If you don't want the intermediate cells to show remember you can hide rows or columns. Another trick for hiding the contents of a single cell (without hiding whole rows or columns) is to use white font on white background. – Tyson Jul 25 '14 at 16:40
  • @Excellll Good point, I'll try to create a sample document. – 114 Jul 25 '14 at 17:27
  • Another option I've used before is to create your own "formula" (Function) in VBA, and then reference that in the cell. – DarkMoon Jul 25 '14 at 22:36

If i am understanding correctly, you need to create a formula that does an Index/Match operation against tables that are in different worksheets, where the user selects which worksheet should be searched

Sounds like you may want to use CHOOSE and INDIRECT to dynamically build the path to the appropriate worksheet based on the value placed in A2.

For example the below would run Match using the value found in B2 against cells in A1:A3 on the appropriate worksheet, where the worksheet is based on the value (1,2,3) that the user put in A2

=MATCH(A2,(INDIRECT(CHOOSE(B2,"Sheet1","Sheet2","Sheet3") & "!" & "A1:A3")),0)

CHOOSE is being used here to watch a cell (A2) and based on the value entered there outputs the object with the corresponding index, which in this case is a workbook name.

INDIRECT allows you to build a cell path on the fly and is therefore embedding the correct worksheet name into the path to be used by the SUM function.

Of course if your table's are named ranges or are different sizes, you could always change the static "A1:A3" in the example above into another CHOOSE that would output the correct portion of the path based on the user's input in another cell.

Forgive me if this is entirely off-base with what you are trying to do - I am not sure i understand the step 3 in your list...

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.