Use Vlookup to store formulas?

[patbarb]

Hi, I'm trying to figure out if it's practical to use Vlookup to bring back complete formulas to a spreadsheet when you have a ridiculous number of formula possibilities for a given cell

I want to create a varying formula in cell E1 based on the text entered in A1. In the example below, the user selects first the condition of the pet (wet or dry) in A1. The user may optionally also select a name (Fluffy or Fido) in B1. Cells C1 and D1 simply say Scram and Welcome, respectively.

Row1, cells A-E - Wet/Dry Fluffy/Fido Scram Welcome (formula

The idea is that if the user picks Wet, then the formula automatically places "Scram (pet name selected)" in cell E1. If Dry is picked, then it says "Welcome (pet name selected)". Pretty easy to do for a small formula just nesting a couple of IFs

=IF(A1="Wet",D1&B1,IF(A1="Dry",C1&B1,"")

In reality, I actually have about 40 choices in the dropdown (=40 different formulas!) so I got excited when I had the idea of a Vlookup that could simply retrieve the correct formula based on the item selected. This works great for the first line, and when I drag down the formula it can easily keep sending the correct Wet/Dry choice from the dropdown (A1, A2, A3, ...), like so

=Vlookup(A1,Lookup_MoistCondition,2)& Vlookup(A1,Lookup_ MoistCondition,3)&..., etc., to bring back as many lookup table columns as are needed for the complete formul

However, since the lookup table has no direct connection to the other rows back in the main spreadsheet, it doesn't realize that the user has entered a different pet name in B2, B3, B4... and it keeps thinking that the pet selected in B1 is always the right one.

Anybody got ideas on how to do this or if there's some other way to store formulas to keep using over and over again

Thanks
Patric

 

[Roger Govier]

Hi Patrick

I'm not sure that I am following your example but, wouldn't it be B1 in
the second expression
=Vlookup(A1,Lookup_MoistCondition,2)& Vlookup(B1,Lookup_ MoistCondition,3)

 

[Roger Govier]

Hi Patrick

I'm not sure that I am following your example but, wouldn't it be B1 in
the second expression
=Vlookup(A1,Lookup_MoistCondition,2)& Vlookup(B1,Lookup_ MoistCondition,3)

 

[patrick barbour]

Thanks for trying to wade through this long post, Roger! -) I realize it's pretty durned confusing, but it wouldn't be B1 in the second Vlookup the way I'm doing it. The columns I need to pull from change every row(!). The idea is that I'm pulling all the formula parts based only on whether Wet or Dry is chosen in column A. (It's pretty clunky


I check on Wet/Dry and then go to the lookup table to see which column I should be grabbing first in the main worksheet. I then use that same Wet/Dry choice to go back to the lookup table and find out what the second column is that I should be grabbing from the main worksheet, etc. This is because depending on what the user chooses in column A, I could be pulling columns B, C, F and Q or maybe a completely different set, like C, F, L, S, U and Z

I guess what I'm saying is that in an ideal world I could just check on Wet/Dry and then do one single lookup to return the correct formula text, e.g., 'B1&C1&F1&Q1'. I could then turn this text into a formula after updating the row # to reflect the actual row I was on (with Offset, or whatever). Again, thanks. --patric

 

[Steve Dunn]

Hi Patrick,

I'm guessing from the lack of responses that others are as confused as I am,
but maybe this will help you. MATCH() will return a position in your lookup
range, so you could use something like

=MATCH(A1, OFFSET(Lookup_MoistCondition,,,,1), 0)

in a helper column (say E in this example), then use INDEX() to retrieve the
details from each respective column

=INDEX(OFFSET(Lookup_MoistCondition,,2,1), E1) &
INDEX(OFFSET(Lookup_MoistCondition,,3,1), E1) &
INDEX(OFFSET(Lookup_MoistCondition,,6,1), E1)


Now, the question is, can you define a method for us to select which columns
should be chosen? If you can put it in words, we may be able to refine the
formula further.

HTH
Steve D.

 

[patrick barbour]

Thanks for the input Steve. I'll be able to try your idea later today and will report back. -)

 

[patbarb]

Well, I don't think that the Index/Match/Offset stuff above gets me where I want to go - but that was cool learning that Offset can create a vector on the fly for Match to use! (That is, take an existing array and reshape it to only be 1 column wide, like above, which is what Match wants.) I'll try another post and do a better job explaining. Thanks all! --patrick