Wednesday, August 29, 2012

yogi_Set Up A Sheet For Using Vlookup Functionality For Different Formulas Not Values

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #720   Aug 29, 2012     www.energyefficientbuild.com.

user Leon Ivanov said: (http://productforums.google.com/forum/?zx=lbtwq7u8p585#!category-topic/docs/spreadsheets/91w4YrnIz5M)
Vlookup for different formulas, not values
Hello.
I have a list of priorities, names and formulas. I want to be able to vlookup for a certain formula from a list of formulas. Vlookuping for values or names is straightforward, but that's not what I want.
That is, when I change priorities, I want the formula assosiated with each priority to change as well.
Thank you very much in advance.
Regards,
Leon.
-------------------------------------------------------------------------
VLOOKUP formulas lookup values and not formulas ... so in the following I have provided a workaround solution to the problem where I had to change the source formulas


4 comments:

  1. Thanks very much, Yogi.

    Everything works, but I guess I just didn't ask the right question:
    1. Is it possible to make it without ArrayFormula in B17? I'm generally comfortable copying formula throughout the sheet
    2. Generally speaking, how to quote o formula?
    For example, A1=1, A2 has A1+1 as Text.
    What should I put in A3, to get A1+1 in A3 as Formula, but by referencing only A2 from A3?

    Cheers,
    Leon.

    ReplyDelete
  2. Hi Leon Ivanov:

    In regard to 1. YES it is possible to have a single cell non-array formula for what is hown in cell B17 (although I don't know why you would want to do that) I have updated my blog post to shown the single cell formula in cell B32

    now for 2., in cell A3 you will key-in the formula =A2

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
  3. Sorry everyone for the delay, I was away.

    Yogi,

    1. Thanks for the solution with vlookup. I would need a solution without ArrayFormula because I need to transport the file to MS Excel sometimes.
    1a. Assume, I use your vlookup solution. Then if I need a big mass of information, could I expect that vlookup of big array (you proposed B32=vlookup($A17,$A$3:$J$6,column(B:B),0), where $A$3:$J$6 is a relatively small array) will not slow it all down?
    1b. May it be done differently (because your vlookup solution actually lookups values, not formulas). Is it possible to have the same output values (by dragging formula to the rate as far as needed, not only to J column), but assuming you can use as input only $A$3:$C$6, not $A$3:$J$6?

    2. In the spreadsheet I shared first I've made a tab "A1+1". As I put "=A2" in A3, it merely returns "A1+1", but it doesn't return "2".

    Thanks in advance for your help.

    Cheers,
    Leon.

    ReplyDelete
  4. Hello, Yogi.

    Thank you very much for your help with my questions. I would appreciate if you could also comment on the last one.

    Regards,
    Leon

    ReplyDelete