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