Vlookup Return A Formula Instead Of A Value?

    Hi,


    The main sheet on my workbook has 3 fields- 'Type' , 'Data1', 'Result'


    For a given row, a certain formula will be applied on 'Data1' to calculate 'Result'. The formula will vary based on the value of 'Type'.


    So example if Type = A, Result= Data1*10


    Type = B, Result= Data1*20.


    The actual formulas are a lot more complicated.


    I know this can be done using a nested if statement, but I would like to know if this can be done using Vlookup (where the formula is returned and it is applied on 'Data1')?


    I don't mind any other solution as well as long as I don't need to write a long nested if statement!!


    Thanks a lot....

    :angrypc:

    Re: Vlookup Return A Formula Instead Of A Value?


    you will have to use the INDIRECT formula...see the example file attached


    =C2*INDIRECT(CHOOSE(MATCH(B2,{"A","B"},0),"B7","B8"))*INDIRECT(CHOOSE(MATCH(B2,{"A","B"},0),"C7","C8"))


    here based upon "Type" input in Cell B2 the formula is constructed either as "B7*C7" or "B8*C8" which is multiplied by the value in Cell C2

    Re: Vlookup Return A Formula Instead Of A Value?


    To answer your question, it would be good to see your workbook layout attached, just a sample will do. I think I would use SUMPRODUCT, not VLOOKUP.

    Re: Vlookup Return A Formula Instead Of A Value?


    Quote from ByTheCringe2

    To answer your question, it would be good to see your workbook layout attached, just a sample will do. I think I would use SUMPRODUCT, not VLOOKUP.


    can you give an example formula

    Re: Vlookup Return A Formula Instead Of A Value?


    Thanks everyone, however, I don't think the solution will work.


    Let me try to explain the problem in detail. Sorry, I cannot upload/download sheets.


    If Type= "A", Result= Max (22, 2 *(67- Data1))
    If Type="B", Result= Round ( 1.7 * Data1)


    Type has at least 10 values, which have similar formulas.


    And Data1 is not constant, but varies with the row - C1, C2, C3 etc.


    Thank you once again.

    :angrypc:

    Re: Vlookup Return A Formula Instead Of A Value?


    I would use VLOOKUP then. Column 1 is A, B, etc. Column 2 is all the formulas, for example, =MAX(22, 2 *(67- Data1)), but Data1 would need to be spelt out in each formula.

    Re: Vlookup Return A Formula Instead Of A Value?


    can you give an example?


    in any case, for a given cell in column 2, how will the formula calculate based on values in column 1?


    tks.

    :angrypc:

    Re: Vlookup Return A Formula Instead Of A Value?


    Hi


    Try the CHOOSE function.


    Along the lines of


    =CHOOSE(MATCH(A1,{"A","B","C"},0),MAX(22,2*(67-data1)),ROUND(1.7*data1,3),99)


    The match function determines the position of the type, and then it selectes that from the various formula options.



    HTH


    Tony

    nilaymohit


    Please start your own question, provide a link to this post if you think it will help.. Post your question in someone else's thread is known as hijacking and is not allowed.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login