# Choose formula - reference a formula to use

1. ## Choose formula - reference a formula to use

I have a Table (tMaster) in Excel with many rows. Say Column A [Code] has unique Code values of "X", "Y", and "Z". Col B will run different formulas based on Col. A value. Ex. Col A = "X", then Col B should add Cols C and D. If col A = "Y", then Col B should multiply col. C by 1.5. Etc. I can do this with a nested If statement (or IFS statement), but I thought I'd like to do it using Choose. I have another table (tIndex) that has the unique values from COl A, and assigns an index number for each (some will have the same index). Then I have another table (tFormula) that has the index number, and then the formula I'd like Column B (in tMaster) to use.

(NOTE: The below had some "at" signs, but the forum would not let me post with that symbol so I removed them)

So in Column B of tMaster, I'd like to have something like:
=CHOOSE(VLOOKUP([Code],tIndex,2,FALSE),VLOOKUP(VLOOKUP([Code],tIndex,2,FALSE),tFormula,2,FALSE),[repeated])

So if Col A ([Code]) was "X", if my tIndex has "2" as the index for "X" , and my tFormula has "[Col C]+[Col D]" as the formula for index "2", then Col B in tMaster should run the formula [Col C]+[Col D]

I hope this makes sense. I thought CHOOSE would work, but if there's another solution, please let me know.

2. ## Re: Choose formula - reference a formula to use

It makes sense in words but it is almost impossible to paint a picture in our head of the actual sheet. Also building a solution is only possible with the sheet available so please upload an example workbook (remove sensitive data if need be) so we can build of that.

3. ## Re: Choose formula - reference a formula to use

Thanks for the quick reply. I attached an example file.

4. ## Re: Choose formula - reference a formula to use

=CHOOSE(VLOOKUP([@Code],tIndex,2,FALSE),[@data1]+[@data2],[@data1]*2,[@data1]*[@data2])

5. ## Re: Choose formula - reference a formula to use

Yes, that's right - it would work, and in this case I would do that, but if my formulas were more complicated (which they are), and there were more codes (which there are), then the Choose formula may be harder to edit if needed, so I thought putting the formulas in their own table would make it easier to edit the formulas. But this is good - thank you!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1