Use vlookup to choose an array formula

[worldwidewall]

Hi <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am using Windows XP and Excel 2003. <o></o>
<o></o>
In cells B1 to B10 I have different ARRAY formulas entered with no equal signs at the front and no CTRL SHIFT ENTER so they are just the literal text of the array formulas and excel does not evaluate them. In Cells A1 to A10 I have the numbers 1 through 10. In Cells C1 to C20 I have random numbers between 1 and 10. In Cells D1 to D20 I would like to have vlookup formulas that will use the value in the cell to the left in column C to determine which array formula to evaluate.<o></o>
<o></o>
So if Cell C1 contained a 3 then Cell D1 would evaluate the Array formula in Cell B3 as though it were entered in cell D1.<o></o>
<o></o>
Of course I am just assuming that vlookup is the way to go. I am open to any other ideas too.<o></o>
<o></o>
Thanks<o></o>
<o></o>
Dave<o></o>

 

[Marcelo Branco]

Maybe this can work

First create an UDF like below

Alt+F11 to open the VBEditor
Insert > Module
paste the code below in the right-panel

Function funcEval(t As String)
    funcEval = Evaluate("=" & t)
End Function

Then in D1
=funcEval(VLOOKUP(C1,$A$1:$B$10,2,0))

copy down

HTH

M.

 

[T. Valko]

Hi <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am using Windows XP and Excel 2003. <o></o>
<o></o>
In cells B1 to B10 I have different ARRAY formulas entered with no equal signs at the front and no CTRL SHIFT ENTER so they are just the literal text of the array formulas and excel does not evaluate them. In Cells A1 to A10 I have the numbers 1 through 10. In Cells C1 to C20 I have random numbers between 1 and 10. In Cells D1 to D20 I would like to have vlookup formulas that will use the value in the cell to the left in column C to determine which array formula to evaluate.<o></o>
<o></o>
So if Cell C1 contained a 3 then Cell D1 would evaluate the Array formula in Cell B3 as though it were entered in cell D1.<o></o>
<o></o>
Of course I am just assuming that vlookup is the way to go. I am open to any other ideas too.<o></o>
<o></o>
Thanks<o></o>
<o></o>
Dave<o></o>

Let's see if we understand this...

If C1 = 3 then you want cell D1 to evaluate and return the RESULT of the formula string that's in cell B3?

If that's what you want then just enter the formulas in B1:B10 and in D1:D20 you can use a formula like this:

=INDEX(B$1:B$10,C1)

 

[markmzz]

Maybe this:

Note: you will need a UDF.

Function myEvaluate(myFormula As String)
    Application.Volatile
    myEvaluate = Evaluate("=" & myFormula)
End Function

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">SUM(IF(A1:A$10>9,A1:A$10,0))</td><td style="text-align: center;;">7</td><td style="text-align: center;;">34</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">SUM(IF(A2:A$10>8,A2:A$10,0))</td><td style="text-align: center;;">1</td><td style="text-align: center;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">3</td><td style="text-align: center;;">SUM(IF(A3:A$10>7,A3:A$10,0))</td><td style="text-align: center;;">8</td><td style="text-align: center;;">27</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">4</td><td style="text-align: center;;">SUM(IF(A4:A$10>6,A4:A$10,0))</td><td style="text-align: center;;">4</td><td style="text-align: center;;">34</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">5</td><td style="text-align: center;;">SUM(IF(A5:A$10>5,A5:A$10,0))</td><td style="text-align: center;;">1</td><td style="text-align: center;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">6</td><td style="text-align: center;;">SUM(IF(A6:A$10>4,A6:A$10,0))</td><td style="text-align: center;;">3</td><td style="text-align: center;;">27</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">7</td><td style="text-align: center;;">SUM(IF(A7:A$10>3,A7:A$10,0))</td><td style="text-align: center;;">3</td><td style="text-align: center;;">27</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">8</td><td style="text-align: center;;">SUM(IF(A8:A$10>2,A8:A$10,0))</td><td style="text-align: center;;">9</td><td style="text-align: center;;">19</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">9</td><td style="text-align: center;;">SUM(IF(A9:A$10>1,A9:A$10,0))</td><td style="text-align: center;;">6</td><td style="text-align: center;;">40</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">10</td><td style="text-align: center;;">SUM(IF(A10:A$10>0,A10:A$10,0))</td><td style="text-align: center;;">8</td><td style="text-align: center;;">27</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">2</td><td style="text-align: center;;">19</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">8</td><td style="text-align: center;;">27</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">5</td><td style="text-align: center;;">40</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">5</td><td style="text-align: center;;">40</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">6</td><td style="text-align: center;;">40</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">10</td><td style="text-align: center;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">8</td><td style="text-align: center;;">27</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">8</td><td style="text-align: center;;">27</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">7</td><td style="text-align: center;;">34</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">******</td><td style="text-align: center;;">***************</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D1</th><td style="text-align:left">=myEvaluate(<font color="Blue">LOOKUP(<font color="Red">C1,A$1:B$10</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[worldwidewall]

Thanks for the replies. I would really like to keep vba out of this.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Biff<o></o>
<o></o>
I like yours because it's simple but when I use it the folumas in column D return the literal text of the array formulas as opposed to the results. Your formula picks the right array formula but displays the array formula itself as opposed to the result.<o></o>
<o></o>
Is there something I missed?<o></o>
<o></o>
I realize perhaps I need to clarify something. The array formulas contain relative ranges, so their results will very depending on which cell they are evaluated in. I am using R1C1 reference style.
<o></o>
Thanks<o></o>
<o></o>
Dave<o></o>

 

[Marcelo Branco]

Thanks for the replies. I would really like to keep vba out of this.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<o></o>

I dont know how to do this without VBA.

M.

 

[worldwidewall]

Marcelo

If I cannot get the formula approach to work I will definitely try your VBA solution. Thanks for your time.

Dave

 

[Marcelo Branco]

Marcelo

If I cannot get the formula approach to work I will definitely try your VBA solution. Thanks for your time.

Dave

Dave,

Maybe its possible without VBA, i'm just saying that i tried and could not find a way.

M.

 

[T. Valko]

Thanks for the replies. I would really like to keep vba out of this.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Biff<o></o>
<o></o>
I like yours because it's simple but when I use it the folumas in column D return the literal text of the array formulas as opposed to the results. Your formula picks the right array formula but displays the array formula itself as opposed to the result.<o></o>
<o></o>
Is there something I missed?<o></o>
<o></o>
I realize perhaps I need to clarify something. The array formulas contain relative ranges, so their results will very depending on which cell they are evaluated in. I am using R1C1 reference style.
<o></o>
Thanks<o></o>
<o></o>
Dave<o></o>

In column B, instead of entering a string that looks like the formula enter the formula itself. For example, if B3 contains this:

MAX(IF(Y1:Y10="X",Z1:Z10))

Enter the actual array formula:

=MAX(IF(Y1:Y10="X",Z1:Z10))

 

[worldwidewall]

Biff

The problem with that is my array formulas contain relative references in R1C1 reference Style. So the value that the array formula returns in cell B2 will be different than the value it returns in D12 for example. The formula would be identical but the result varys depending on which cell it is evaluated in.

Does this make sense?

Thanks

dave