# 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

## Files

• 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?

Hi, pangolin, I was thinking of a three-clause
SUMPRODUCT((range=condition)*(range=condition)*(range=condition))

• Re: Vlookup Return A Formula Instead Of A Value?

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

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?

OK, See the attachment.

## Files

• 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

• Hi,

I have used certain conditions in Table A to get Pass and fail result. And I have used Vlookup in Table B to return value "Pass" or "Fail". But I want Vlookup to return a "formula" in Table B and take reference cell "Value" from table B and return PASS or Fail accordingly.

## Files

• 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!