+ Reply to Thread
Results 1 to 5 of 5

Choose formula - reference a formula to use

  1. #1
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,826

    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. #2
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,483

    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. #3
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,826

    Re: Choose formula - reference a formula to use

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

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Choose formula - reference a formula to use

    Please try at B2

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

  5. #5
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,826

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. CHOOSE Formula
    By Aphyx in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-30-2018, 04:05 PM
  2. [SOLVED] Formula help to choose lowest cost from multiple vendors and then choose vendor
    By roland_arv in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-27-2013, 07:48 PM
  3. Need formula to choose between two dates
    By onlinesunshine in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 02-22-2013, 05:12 PM
  4. Choose formula mimitations
    By nunocabete in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-03-2012, 08:38 AM
  5. [SOLVED] formula to choose top 5
    By AGA in forum Excel General
    Replies: 2
    Last Post: 04-26-2012, 11:03 AM
  6. [SOLVED] If formula to choose which value to use
    By manharji in forum Excel General
    Replies: 4
    Last Post: 03-03-2011, 05:36 PM
  7. Using the CHOOSE formula
    By tropezfn in forum Excel General
    Replies: 1
    Last Post: 09-28-2006, 11:21 AM

Bookmarks

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