Consulting

Results 1 to 6 of 6

Thread: VLOOKUP TO GET RESULT AS FORMULA

  1. #1

    VLOOKUP TO GET RESULT AS FORMULA

    Hello everyone!

    I have a formula where I have to use atleast 15 nested if functions to get my result, instead can we have a list of Criteria & formulas in a separate table, and bring back the formula based on my criteria. like vlookup, based on lookup value bring the formula from the table instead of values.

    I have attached a sample file for reference

    Regards
    Arvind
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,937
    Keep it simple

    in D2:

    =CHOOSE(CODE(B2)-64;10;7,5;5;2;1;-20;-15;-5)*C2/100
    =CHOOSE(CODE(B2)-64,10,7.5,5,2,1,-20,-15,-5)*C2/100

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,272
    Location
    I think he means

    =CHOOSE(CODE(B2)-64,10,7.5,5,2,1,80,85,95)*C2/100
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,357
    Location
    @snb & XLD --

    I was still wondering about the OP's intent

    1. The column is labeled 'Sales + Commission' but the formula 'appears' to only generate a 10%, 7.5%, 5%,2%, and 1% commission (no sales) for A - E types

    2. Making an assumption for a G, the formula C5-(15%*C5) returns 425 'Sales + Commission' for 500 'Sales', which doesn't seem right

    My guess was

    =CHOOSE(CODE(B2)-64,1.1,1.075,1.05,1.02,1.01,0.8,0.85,0.95)*C2


    Instead of guessing, I think I need more information

    Capture.JPG
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,937
    I fear the values in column B will be replaced by words in the OP's next reaction.

  6. #6
    Hi,

    Thanks for all your replies, sorry for not being clear. the file that I gave was only sample, there can be any formulas there, can be a if function, can be a lookup, I am looking for a macro or idea to get any formulas based on lookup

    Regards
    Arvind

Posting Permissions

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