VBA Evaluate Range and VLOOKUP

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
Hi,
As a beginner I may be going a bit above my head!!

After studying and participating at some considerable length in Threads and links to do with using the Evaluate function to speed things up, I thought I understood it. Here are some of those links and Threads. (www.excelfox.com/forum/f22/concatenating-balls-1891/ VBA Trick of the Week :: Avoid Loop for Range Calculations – Evaluate | Useful Gyaan )

. So … the following simplified example File (XL2007 .xlsm)
https://app.box.com/s/pr78mhna00advvhsrmvi
has a Spreadsheet LEFT Function and a Spreadsheet VLOOKUP Function

The results look good! (That is to say wot I expect!) :-


<b></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;border-top: 1px solid black;border-bottom: 1px solid black;;">Produnt</td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Name</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;;">Chocolate-europe aroma</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Choc</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;border-right: 1px solid black;;">Chocolate-Cookies</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Choc</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;border-right: 1px solid black;;">Banana-Chocolate-Split</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">10</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Bana</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;border-right: 1px solid black;;">Limette-Käsekuchen</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">16</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Lime</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;border-right: 1px solid black;;">Erdbeere-Quark</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">8</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Erdb</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;border-right: 1px solid black;;">Erdbeere-Mix</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Erdb</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;border-right: 1px solid black;;">Jamaica Sun</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">6</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Jama</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;border-right: 1px solid black;;">Waldbeeren</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Wald</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;;"></td><td style="text-align: center;;"></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;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;border-top: 1px solid black;border-left: 1px solid black;;">LOOKUP Table</td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;border-left: 1px solid black;;">Product Name</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;border-left: 1px solid black;;">Haselnuß-Walnuß-aromatisiert</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;border-left: 1px solid black;;">Tiramisu</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">2</td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;border-left: 1px solid black;;">Chocolate-colonial blend</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;border-left: 1px solid black;;">Chocolate-europe aroma</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">4</td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;border-left: 1px solid black;;">Chocolate-Cookies</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;border-left: 1px solid black;;">Jamaica Sun</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">6</td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;border-left: 1px solid black;;">Himbeere-Joghurt</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;border-left: 1px solid black;;">Erdbeere-Quark</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">8</td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;border-left: 1px solid black;;">Erdbeere-Mix</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;border-left: 1px solid black;;">Banana-Chocolate-Split</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">10</td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;border-left: 1px solid black;;">Waldbeeren</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;border-left: 1px solid black;;">Kirsche</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">12</td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;border-left: 1px solid black;;">Kirsche-grüner Apfel</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;border-left: 1px solid black;;">Kirsche-Ananas</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">14</td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;border-left: 1px solid black;;">Stracciatella</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: center;border-left: 1px solid black;;">Limette-Käsekuchen</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">16</td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: center;border-left: 1px solid black;;">grüner Apfel-Quark</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: center;border-bottom: 1px solid black;border-left: 1px solid black;;">Blutorange-Quark</td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></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">B3</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A3,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A4,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A5,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B6</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A6,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B7</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A7,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B8</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A8,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B9</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A9,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B10</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A10,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">=LEFT(<font color="Blue">A3,4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D4</th><td style="text-align:left">=LEFT(<font color="Blue">A4,4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=LEFT(<font color="Blue">A5,4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D6</th><td style="text-align:left">=LEFT(<font color="Blue">A6,4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D7</th><td style="text-align:left">=LEFT(<font color="Blue">A7,4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D8</th><td style="text-align:left">=LEFT(<font color="Blue">A8,4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D9</th><td style="text-align:left">=LEFT(<font color="Blue">A9,4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D10</th><td style="text-align:left">=LEFT(<font color="Blue">A10,4</font>)</td></tr></tbody></table></td></tr></table><br />



I apply this code

Code:
[color=darkblue]Sub[/color] Evaluate_Left()[color=darkblue]Dim[/color] rngName [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] rngName = ThisWorkbook.Worksheets("Sheet1").Range("A3:A10")
[color=darkblue]Dim[/color] rngEE [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] rngEE = ThisWorkbook.Worksheets("sheet1").Range("E3:E10")
[color=darkblue]Let[/color] rngEE = Evaluate("if(row(3:10),LEFT(" & rngName.Address & ",4))")
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'Evaluate_Left()[/color]


And get the following:-

<b></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 /><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><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Produnt</td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Name</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;;">Chocolate-europe aroma</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Choc</td><td style="text-align: center;;">Choc</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;border-right: 1px solid black;;">Chocolate-Cookies</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Choc</td><td style="text-align: center;;">Choc</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;border-right: 1px solid black;;">Banana-Chocolate-Split</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">10</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Bana</td><td style="text-align: center;;">Bana</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;border-right: 1px solid black;;">Limette-Käsekuchen</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">16</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Lime</td><td style="text-align: center;;">Lime</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;border-right: 1px solid black;;">Erdbeere-Quark</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">8</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Erdb</td><td style="text-align: center;;">Erdb</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;border-right: 1px solid black;;">Erdbeere-Mix</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Erdb</td><td style="text-align: center;;">Erdb</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;border-right: 1px solid black;;">Jamaica Sun</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">6</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Jama</td><td style="text-align: center;;">Jama</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;border-right: 1px solid black;;">Waldbeeren</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Wald</td><td style="text-align: center;;">Wald</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;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></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;;"></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 />

Which again is wot I expect.


Now I apply this code

Code:
[color=darkblue]Sub[/color] Evaluate_VLOOKUP()[color=darkblue]Dim[/color] rngName [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] rngName = ThisWorkbook.Worksheets("Sheet1").Range("A3:A10")
[color=darkblue]Dim[/color] rngCC [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] rngCC = ThisWorkbook.Worksheets("sheet1").Range("C3:C10")
[color=darkblue]Let[/color] rngCC = Evaluate("if(row(3:10),VLOOKUP(" & rngName.Address & ",$A$16:$C$33,3,FALSE))")
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'Evaluate_VLOOKUP()[/color]


…but get the following:-


<b></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 /><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><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Produnt</td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Name</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;;">Chocolate-europe aroma</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;;">Choc</td><td style="text-align: center;;">Choc</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;border-right: 1px solid black;;">Chocolate-Cookies</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;;">Choc</td><td style="text-align: center;;">Choc</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;border-right: 1px solid black;;">Banana-Chocolate-Split</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">10</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;;">Bana</td><td style="text-align: center;;">Bana</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;border-right: 1px solid black;;">Limette-Käsekuchen</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">16</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;;">Lime</td><td style="text-align: center;;">Lime</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;border-right: 1px solid black;;">Erdbeere-Quark</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">8</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;;">Erdb</td><td style="text-align: center;;">Erdb</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;border-right: 1px solid black;;">Erdbeere-Mix</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;;">Erdb</td><td style="text-align: center;;">Erdb</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;border-right: 1px solid black;;">Jamaica Sun</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">6</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;;">Jama</td><td style="text-align: center;;">Jama</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;border-right: 1px solid black;;">Waldbeeren</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;;">Wald</td><td style="text-align: center;;">Wald</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;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></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;;"></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 />

. I would have expected that result without The extra If Row() stuff, which I thought overcame this problem.
. can anyone suggest wot is going wrong, or how I obtain the correct results (Using the Evaluate Function for a range)
Thanks
Alan.

P.s. I will also post this Thread Here: Multiple Columns Into Single Column Using Data Text To Column - Page 2
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
Like so:

Rich (BB code):
   ……………….
Simples. ?????
	

	
	
		
		

		
			
		
		
	


	
clip_image001.gif
Rich (BB code):
…………….Thanks Rory.  Another Mega one liner!
 
.   Amazing. How on earth can you come up with stuff like this?  Again something new and totally un understandable to an old brain like mine! 
.   You can understand my frustration….Here the VBA N Function is doing some weird trick…
.. weird for example that I got as far as 
 

	
	
	
	
	
	


Code:
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("IF(1,MATCH($A$3:$A$10,$A$16:$A$33,0))")
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("IF(Row(),MATCH($A$3:$A$10,$A$16:$A$33,0))")
Which gives me sensible looking values…. …but then wot you did including the N( ) bit…….
Code:
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("N(IF(1,MATCH($A$3:$A$10,$A$16:$A$33,0)))")
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("N(IF(Row(),MATCH($A$3:$A$10,$A$16:$A$33,0)))")
… seems to given me just a single value again. So I would never of hit on it!! But the entire line works only with this mod!!! . If you ever get the time I am sure it would be a great contribution and an amazing help in learning if you could go through and explain the steps and your thoughts in detail in coming up with this “Range EVALUATE INDEX with MATCH one liner” and the previous “Range EVALUATE VLOOKUP one liner”. . I do understand the basic ideas we have been discussing here and elsewhere on “coercing” and working around to get try return an array of results from an array of inputs. But exactly how you come up with your Mega one Liners has already killed off a few of my remaining brain cells in trying to understand. . There is no rush… I think you can see from my ramblings in the test forum I haven’t been lazy and must give my brain a break!. . But if you could please Post a reply sometime explaining in some detail the workings behind those two “One liner’s” I would be very grateful. Or if you prefer I could start a new thread something along the lines of “Help in explaining……..” etc? Many “Mega” thanks again Alan Elston.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I basically just took the formula examples on XOR LX's site that was linked to earlier, and tweaked them to make Evaluate work. :)
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
I basically just took the formula examples on XOR LX's site that was linked to earlier, and tweaked them to make Evaluate work. :)


OK, thanks again. I see at first glance that INDEX: Returning an array of values | EXCELXOR talks about the N() stuff… So I may be able some time to work my way through that one.
. Indeed generally my second code request (the Range Evaluate INDEX with MATCH one liner) looks somewot easier than my first one (the Range Evaluate VLOOKUP one liner).

. Can you point me in any direction to understand all your TRANSPOSE etc. stuff, and generally your thinking on that first Range Evaluate VLOOKUP one liner

. Thanks again.
. I realize I have an (amazing) working solution from you and I am very grateful. But I am keen to understand as much as I am able. That would help me to do similar things again without always relying on (Bugging) the profi’s, so much. Hopefully then I can sooner help with answering more threads and put something back in appreciation to all the efforts from people like you , XOR LX etc.. ect..

Alan.

P.s. Explanations for now without the use of curly bracket {} VBA Array stuff would be very welcome as I am trying to learn to walk before I can run and have not got as far as learning and getting experience with VBA Array Functions yet
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

VBA doesn't really have array functions.

The TRANSPOSE is there because if you look at the examples given by XOR LX, the array being passed is a one dimensional 'row' of values({1,2,3}) but you want to pass a column of data so that needs transposing. This then also returns a 'row' of values but you want to put it in a column, so you need to transpose again.
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
VBA doesn't really have array functions.

The TRANSPOSE is there because if you look at the examples given by XOR LX, the array being passed is a one dimensional 'row' of values({1,2,3}) but you want to pass a column of data so that needs transposing. This then also returns a 'row' of values but you want to put it in a column, so you need to transpose again.

. Ok thanks. I think I was sort of seeing thatin my ramblings in the test Forum and in my empirical goes at getting codesworking… I always transposed and then transposed back.<o:p></o:p>
. Working backward once one knows is useful.Getting there in the first place is I guess just experience, which I do nothave. So your help is invaluable.<o:p></o:p>
. I will try to go through again very carefullyuntil I understand exactly wot is going on to do justice to your efforts <o:p></o:p>
<o:p> </o:p>
Thanks<o:p></o:p>
<o:p> </o:p>
Alan<o:p></o:p>
<o:p> </o:p>
P.s. I guess if insteadof using the typical default Index of Just row….<o:p></o:p>
=INDEX($C$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0))<o:p></o:p>
<o:p> </o:p>
Which can be re-writtenas <o:p></o:p>
<o:p> </o:p>
=INDEX($C$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0),1)<o:p></o:p>
( http://www.mrexcel.com/forum/excel-questions/808351-vlookup-lookup-value-255-character-limit-2.html#post3953521 )<o:p></o:p>
Then writing it out forthe single column rather than the single Row<o:p></o:p>
<o:p> </o:p>
=INDEX($C$16:$C$33, 1 , MATCH($A$3:$A$10,$A$16:$A$33,0) )<o:p></o:p>
<o:p> </o:p>
. Then it might be an interesting academic exerciseand learning exercise to see if I need to do some transposing to get similar results..<o:p></o:p>
<o:p> </o:p>
. But I think for now Ihave already strained my wee brain abit too far!!<o:p></o:p>
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336

ADVERTISEMENT

Hi RoryA,<o:p></o:p>
. Sorry to trouble you again on this one…….<o:p></o:p>
<o:p> </o:p>
. While I was working through again tounderstand your Mega codes I came up with a new problem<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
…….. Originally onereason for me investigating the INDEX with MATCH as an alternative to VLOOKUPwas to overcome the 255 character limit in VLOOKUP. Hence in that other thread I had the spreadsheetformulas from Dominic <o:p></o:p>
<o:p> </o:p>
=INDEX($A$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0),3)<o:p></o:p>
=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0),1)<o:p></o:p>
=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0))<o:p></o:p>
<o:p> </o:p>
Which (for no other thanacademic reason !!) I changed to this form<o:p></o:p>
<o:p> </o:p>
=INDEX($A$11:$C$14,MATCH(A3,$A$11:$A$14,0),3)<o:p></o:p>
=INDEX($C$11:$C$14,MATCH(A3,$A$11:$A$14,0),1)<o:p></o:p>
=INDEX($C$11:$C$14,MATCH(A3,$A$11:$A$14,0)) <o:p></o:p>
<o:p> </o:p>
Which can also bewritten slightly differently to match the final form you did for me <o:p></o:p>
<o:p> </o:p>
=INDEX(INDEX($A$11:$C$14,N(IF(1,MATCH(A3,$A$11:$A$14,0))),3),)<o:p></o:p>
=INDEX(INDEX($C$11:$C$14,N(IF(1,MATCH(A3,$A$11:$A$14,0))),1),)<o:p></o:p>
=INDEX(INDEX($C$11:$C$14,N(IF(1,MATCH(A3,$A$11:$A$14,0)))),)<o:p></o:p>
<o:p> </o:p>
Unfortunately I now seethat for reasons that are completely beyond me the last 6 formulas do notovercome the 255 character limit problem!?!<o:p></o:p>
<o:p> </o:p>
. The first three (from Dominic) do overcomethis problem!?!<o:p></o:p>
<o:p> </o:p>
. A solution for me for now maybe would be toget Dominic’s versions of the formulas to work in your Mega Evaluate Range INDEX with MATCH oneliner. But I have been unable to get them to work. Here is your Last Mega Codeagain applied to the example in thisthread. The First 7 codes are all variations of the one you did for me yesterdayand they all work. The last 9 are attempts by me to get a version of the originalDominic Formulas to work in that code form. Unfortunately they do not work!?!.Could you please take a look and see if you can get either of the last 9 towork.<o:p></o:p>
<o:p> </o:p>
Code:
[color=darkblue]Sub[/color]Evaluate_INDEXwithMATCH()<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Dim[/color] rngName               [color=darkblue]As[/color] Range<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]  <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Set[/color] rngName =ActiveSheet.Range("A3:A10")<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Dim[/color] rngJJ                 [color=darkblue]As[/color]Range<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Set[/color] rngJJ =ActiveSheet.Range("J3:J10")<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=green]'[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]   <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Let[/color] rngJJ.Value =Evaluate("INDEX(INDEX($C$16:$C$33,N(IF(1,MATCH($A$3:$A$10,$A$16:$A$33,0)))),)")'Works<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Let[/color] rngJJ.Value =Evaluate("INDEX(INDEX($C$16:$C$33,N(IF(Row(),MATCH($A$3:$A$10,$A$16:$A$33,0)))),)")[color=green]'Works[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Let[/color] rngJJ.Value =Evaluate("IF(Row(),INDEX($C$16:$C$33,N(IF(Row(),MATCH($A$3:$A$10,$A$16:$A$33,0)))))")'Works but has zeros<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Let[/color] rngJJ.Value =Evaluate("INDEX(INDEX($C$16:$C$33,N(IF(1,MATCH($A$3:$A$10,$A$16:$A$33,0))),1),)")[color=green]'Works[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Let[/color] rngJJ.Value =Evaluate("INDEX(INDEX($C$16:$C$33,N(IF(Row(),MATCH($A$3:$A$10,$A$16:$A$33,0))),1),)")'Works<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Let[/color] rngJJ.Value =Evaluate("INDEX(INDEX($A$16:$C$33,N(IF(1,MATCH($A$3:$A$10,$A$16:$A$33,0))),3),)")[color=green]'Works[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Let[/color] rngJJ.Value =Evaluate("INDEX(INDEX($A$16:$C$33,N(IF(Row(),MATCH($A$3:$A$10,$A$16:$A$33,0))),3),)")'Works<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Let[/color] rngJJ.Value =Evaluate("INDEX(INDEX($A$16:$C$33,N(IF(Row(),MATCH(TRUE,INDEX($A$16:$A$33=$A$3:$A$10,0),0))),3),)")[color=green]'Doesn't work[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Let[/color] rngJJ.Value =Evaluate("INDEX(INDEX($A$16:$C$33,N(MATCH(TRUE,INDEX($A$16:$A$33=$A$3:$A$10,0),0)),3),)")'Doesn't work<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Let[/color] rngJJ.Value =Evaluate("INDEX(INDEX($A$16:$C$33,IF(Row(),MATCH(TRUE,INDEX($A$16:$A$33=$A$3:$A$10,0),0)),3),)")[color=green]'Doesn't work[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Let[/color] rngJJ.Value =Evaluate("INDEX(INDEX($A$16:$C$33,MATCH(TRUE,INDEX($A$16:$A$33=$A$3:$A$10,0),0)),3),)")'Doesn't work<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Let[/color] rngJJ.Value =Evaluate("INDEX(INDEX($A$16:$C$33,N(IF(Row(),MATCH(N(TRUE,INDEX($A$16:$A$33=$A$3:$A$10,0),0))),3),)")[color=green]'Doesn't work[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Let[/color] rngJJ.Value =Evaluate("INDEX(INDEX($A$16:$C$33,N(MATCH(N(TRUE,INDEX($A$16:$A$33=$A$3:$A$10,0),0))),3),)")'Doesn't work<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Let[/color] rngJJ.Value =Evaluate("INDEX(INDEX($A$16:$C$33,IF(Row(),MATCH(N(TRUE,INDEX($A$16:$A$33=$A$3:$A$10,0),0))),3),)")[color=green]'Doesn't work[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Let[/color] rngJJ.Value =Evaluate("INDEX(INDEX($A$16:$C$33,MATCH(N()TRUE,INDEX($A$16:$A$33=$A$3:$A$10,0),0),3),)")'Doesn't work<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Let[/color] rngJJ.Value =Evaluate("INDEX(INDEX($C$16:$C$33,N(IF(1,MATCH(TRUE,INDEX($A$16:$A$33=$A$3:$A$10,0),0)))),)")[color=green]'Doesn't work[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]End[/color][color=darkblue]Sub[/color] [color=green]'Evaluate_INDEXwithMATCH()[/color]
<o:p></o:p>

<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
. So to summarize I am now attempting to get aversion of Dominic’s Original INDEX with MATCH alternative to VLOOKUP (ratherthan my modified versions) to work in your last Mega one-liner. <o:p></o:p>
. So to apply specifically to the examples wehave been working with in this Thread,I am attempting to get any versions of these basic formulas working in theEvaluate one liner <o:p></o:p>
<o:p> </o:p>
=INDEX($A$16:$C$33,MATCH(TRUE,INDEX($A$16:$A$33=$A$3:$A$10,0),0),3)<o:p></o:p>
=INDEX($C$16:$C$33,MATCH(TRUE,INDEX($A$3:$A$10,$A$16:$A$33,0),0),1)<o:p></o:p>
=INDEX($C$16:$C$33,MATCH(TRUE,INDEX($A$3:$A$10,$A$16:$A$33,0),0))<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
. In a similar way to we(you!) achieved yesterday with these basic formulas<o:p></o:p>
<o:p> </o:p>
=INDEX($A$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0),3)<o:p></o:p>
=INDEX($C$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0),1)<o:p></o:p>
=INDEX($C$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0)) <o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
Thanks<o:p></o:p>
Alan <o:p></o:p>
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I don't think it will work with that formula. I think you'd need something like this (It may be possible to simplify but frankly I wouldn't ever use this kind of code anyway!):
Code:
rngJJ.Value = Evaluate("INDEX(INDEX($C$16:$C$33,N(IF(1,MMULT(N(TRANSPOSE(A16:A33)=A3:A10),ROW(A16:A33)-ROW(A15))))),)")
 
Last edited:

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
RoryA
. I just tried your new code….
. Amazing again. It works. AND somehow overcomes the 255 Character limit also…….

.. I have modified the initial example tables from this Thread to include an entry (in Row 25 and 5) with over 255 Characters…

<b></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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Produnt</td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;color: #FF0000;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Name</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;color: #FF0000;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;;">Chocolate-europe aroma</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;border-right: 1px solid black;;">Chocolate-Cookies</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;border-right: 1px solid black;;">SCHMELZKÄSE/WWSPREAD philadelphia 179/15/7,6/4/,1/,5/,4/ kräuter 152/11/7,8/4,8/4,6/,7/,5/ leicht110/4,7/11,5/5/5/,4/,4/ kräuter 186/15/9/3,7/  /  /bärlauch 296/28/5/5/3/1/,7/ bresco 230/19,5/8,2/5,4////frischkäsekräuter 186/15/9/3,7/  /  /  /brunchkraut/gurk 215/20,5/4,2/3,5/3/,1/,45/cleop 220/20/4,5/6/5/,1/,35/ bresco 230/19,5/8,2/5,4////</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">#####</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">####</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;border-right: 1px solid black;;">Limette-Käsekuchen</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">16</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">16</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;border-right: 1px solid black;;">Erdbeere-Quark</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">8</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;border-right: 1px solid black;;">Erdbeere-Mix</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;border-right: 1px solid black;;">Jamaica Sun</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">6</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;border-right: 1px solid black;;">Waldbeeren</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">0</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;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;">  =SVERWEIS(A10;$A$16:$C$33;3;FALSCH)</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;">  =VLOOKUP(A10,$A$16:$C$33,3,FALSE)</td><td style="text-align: center;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;border-top: 1px solid black;border-left: 1px solid black;;">LOOKUP Table</td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;border-left: 1px solid black;;">Product Name</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;border-left: 1px solid black;;">Haselnuß-Walnuß-aromatisiert</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;border-left: 1px solid black;;">Tiramisu</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;border-left: 1px solid black;;">Chocolate-colonial blend</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;border-left: 1px solid black;;">Chocolate-europe aroma</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;border-left: 1px solid black;;">Chocolate-Cookies</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;border-left: 1px solid black;;">Jamaica Sun</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;border-left: 1px solid black;;">Himbeere-Joghurt</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;border-left: 1px solid black;;">Erdbeere-Quark</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;border-left: 1px solid black;;">Erdbeere-Mix</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;border-left: 1px solid black;;">SCHMELZKÄSE/WWSPREAD philadelphia 179/15/7,6/4/,1/,5/,4/ kräuter 152/11/7,8/4,8/4,6/,7/,5/ leicht110/4,7/11,5/5/5/,4/,4/ kräuter 186/15/9/3,7/  /  /bärlauch 296/28/5/5/3/1/,7/ bresco 230/19,5/8,2/5,4////frischkäsekräuter 186/15/9/3,7/  /  /  /brunchkraut/gurk 215/20,5/4,2/3,5/3/,1/,45/cleop 220/20/4,5/6/5/,1/,35/ bresco 230/19,5/8,2/5,4////</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;border-left: 1px solid black;;">Waldbeeren</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;border-left: 1px solid black;;">Kirsche</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;border-left: 1px solid black;;">Kirsche-grüner Apfel</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;border-left: 1px solid black;;">Kirsche-Ananas</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">14</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;border-left: 1px solid black;;">Stracciatella</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: center;border-left: 1px solid black;;">Limette-Käsekuchen</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">16</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: center;border-left: 1px solid black;;">grüner Apfel-Quark</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: center;border-bottom: 1px solid black;border-left: 1px solid black;;">Blutorange-Quark</td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;;"></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">Sheet4</p><br /><br />


…. Then applied versions of your latest code to put the calculated values in Column LL (As well as the first code for comparison still putting calculated values in Column JJ)

Code:
[color=darkblue]Sub[/color] Evaluate_INDEXwithMATCHMegaMegaRoryA2()
 
    [color=darkblue]Dim[/color] rngName               [color=darkblue]As[/color] Range
 
   
    [color=darkblue]Set[/color] rngName = ActiveSheet.Range("A3:A10")
 
    [color=darkblue]Dim[/color] rngJJ [color=darkblue]As[/color] Range, rngLL [color=darkblue]As[/color] Range
    [color=darkblue]Set[/color] rngJJ = ActiveSheet.Range("J3:J10")
    [color=darkblue]Set[/color] rngLL = ActiveSheet.Range("L3:L10")
    [color=green]'[/color]
  
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX(INDEX($C$16:$C$33,N(IF(1,MATCH($A$3:$A$10,$A$16:$A$33,0)))),)") 'Works
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX(INDEX($C$16:$C$33,N(IF(Row(),MATCH($A$3:$A$10,$A$16:$A$33,0)))),)") [color=green]'Works[/color]
   
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("IF(Row(),INDEX($C$16:$C$33,N(IF(Row(),MATCH($A$3:$A$10,$A$16:$A$33,0)))))") 'Works but has zeros
   
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX(INDEX($C$16:$C$33,N(IF(1,MATCH($A$3:$A$10,$A$16:$A$33,0))),1),)") [color=green]'Works[/color]
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX(INDEX($C$16:$C$33,N(IF(Row(),MATCH($A$3:$A$10,$A$16:$A$33,0))),1),)") 'Works
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX(INDEX($A$16:$C$33,N(IF(1,MATCH($A$3:$A$10,$A$16:$A$33,0))),3),)") [color=green]'Works[/color]
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX(INDEX($A$16:$C$33,N(IF(Row(),MATCH($A$3:$A$10,$A$16:$A$33,0))),3),)") 'Works
   
    [color=darkblue]Let[/color] rngLL.Value = Evaluate("INDEX(INDEX($C$16:$C$33,N(IF(1,MMULT(N(TRANSPOSE(A16:A33)=A3:A10),ROW(A16:A33)-ROW(A15))))),)") [color=green]'Works[/color]
    [color=darkblue]Let[/color] rngLL.Value = Evaluate("INDEX(INDEX($C$16:$C$33,N(IF(1,MMULT(N(TRANSPOSE(A16:A33)=A3:A10),ROW(A16:A33)-ROW(A15)))),1),)") 'Works
    [color=darkblue]Let[/color] rngLL.Value = Evaluate("INDEX(INDEX($A$16:$C$33,N(IF(1,MMULT(N(TRANSPOSE(A16:A33)=A3:A10),ROW(A16:A33)-ROW(A15)))),3),)") [color=green]'Works[/color]
   
   
 
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'Evaluate_INDEXwithMATCHMegaMegaRoryA2()[/color]

… and here are typical results


<b></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 /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">DomenicAlan</td><td style="text-align: center;;">Rory1</td><td style="text-align: center;;">Rory1</td><td style="text-align: center;;">Rory2</td><td style="text-align: center;;">Rory2</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">IndexFormula</td><td style="text-align: center;;">Range Evaluate INDEX with MATCH</td><td style="text-align: center;;">Spesadsheet Formula</td><td style="text-align: center;;">Range Evaluate INDEX with MATCH</td><td style="text-align: center;;">Spesadsheet Formula</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">4</td><td style="text-align: center;;">4</td><td style="text-align: center;;">4</td><td style="text-align: center;;">#WERT!</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">#WERT!</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">#WERT!</td><td style="text-align: center;;">#WERT!</td><td style="text-align: center;;">#WERT!</td><td style="text-align: center;;">10</td><td style="text-align: center;;">#WERT!</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">16</td><td style="text-align: center;;">16</td><td style="text-align: center;;">16</td><td style="text-align: center;;">16</td><td style="text-align: center;;">#WERT!</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">8</td><td style="text-align: center;;">8</td><td style="text-align: center;;">8</td><td style="text-align: center;;">8</td><td style="text-align: center;;">#WERT!</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">#WERT!</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">6</td><td style="text-align: center;;">6</td><td style="text-align: center;;">6</td><td style="text-align: center;;">6</td><td style="text-align: center;;">#WERT!</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">#WERT!</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">Sheet4</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">I3</th><td style="text-align:left">=INDEX(<font color="Blue">$C$16:$C$33,MATCH(<font color="Red">$A$3:$A$10,$A$16:$A$33,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I4</th><td style="text-align:left">=INDEX(<font color="Blue">$A$16:$C$33,MATCH(<font color="Red">$A$3:$A$10,$A$16:$A$33,0</font>),3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I5</th><td style="text-align:left">=INDEX(<font color="Blue">$A$16:$C$33,MATCH(<font color="Red">$A$3:$A$10,$A$16:$A$33,0</font>),3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I6</th><td style="text-align:left">=INDEX(<font color="Blue">$A$16:$C$33,MATCH(<font color="Red">$A$3:$A$10,$A$16:$A$33,0</font>),3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I7</th><td style="text-align:left">=INDEX(<font color="Blue">$A$16:$C$33,MATCH(<font color="Red">$A$3:$A$10,$A$16:$A$33,0</font>),3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I8</th><td style="text-align:left">=INDEX(<font color="Blue">$A$16:$C$33,MATCH(<font color="Red">$A$3:$A$10,$A$16:$A$33,0</font>),3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I9</th><td style="text-align:left">=INDEX(<font color="Blue">$A$16:$C$33,MATCH(<font color="Red">$A$3:$A$10,$A$16:$A$33,0</font>),3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I10</th><td style="text-align:left">=INDEX(<font color="Blue">$A$16:$C$33,MATCH(<font color="Red">$A$3:$A$10,$A$16:$A$33,0</font>),3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K3</th><td style="text-align:left">=INDEX(<font color="Blue">INDEX(<font color="Red">$C$16:$C$33,N(<font color="Green">IF(<font color="Purple">1,MATCH(<font color="Teal">$A$3:$A$10,$A$16:$A$33,0</font>)</font>)</font>)</font>),</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K4</th><td style="text-align:left">=INDEX(<font color="Blue">INDEX(<font color="Red">$C$16:$C$33,N(<font color="Green">IF(<font color="Purple">1,MATCH(<font color="Teal">$A$3:$A$10,$A$16:$A$33,0</font>)</font>)</font>)</font>),</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K5</th><td style="text-align:left">=INDEX(<font color="Blue">INDEX(<font color="Red">$C$16:$C$33,N(<font color="Green">IF(<font color="Purple">1,MATCH(<font color="Teal">$A$3:$A$10,$A$16:$A$33,0</font>)</font>)</font>)</font>),</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K6</th><td style="text-align:left">=INDEX(<font color="Blue">INDEX(<font color="Red">$C$16:$C$33,N(<font color="Green">IF(<font color="Purple">1,MATCH(<font color="Teal">$A$3:$A$10,$A$16:$A$33,0</font>)</font>)</font>)</font>),</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K7</th><td style="text-align:left">=INDEX(<font color="Blue">INDEX(<font color="Red">$C$16:$C$33,N(<font color="Green">IF(<font color="Purple">1,MATCH(<font color="Teal">$A$3:$A$10,$A$16:$A$33,0</font>)</font>)</font>)</font>),</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K8</th><td style="text-align:left">=INDEX(<font color="Blue">INDEX(<font color="Red">$C$16:$C$33,N(<font color="Green">IF(<font color="Purple">1,MATCH(<font color="Teal">$A$3:$A$10,$A$16:$A$33,0</font>)</font>)</font>)</font>),</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K9</th><td style="text-align:left">=INDEX(<font color="Blue">INDEX(<font color="Red">$C$16:$C$33,N(<font color="Green">IF(<font color="Purple">1,MATCH(<font color="Teal">$A$3:$A$10,$A$16:$A$33,0</font>)</font>)</font>)</font>),</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K10</th><td style="text-align:left">=INDEX(<font color="Blue">INDEX(<font color="Red">$C$16:$C$33,N(<font color="Green">IF(<font color="Purple">1,MATCH(<font color="Teal">$A$3:$A$10,$A$16:$A$33,0</font>)</font>)</font>)</font>),</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M3</th><td style="text-align:left">=INDEX(<font color="Blue">INDEX(<font color="Red">$C$16:$C$33,N(<font color="Green">IF(<font color="Purple">1,MMULT(<font color="Teal">N(<font color="#FF00FF">TRANSPOSE(<font color="Navy">$A$16:$A$33</font>)=$A$3:$A$10</font>),ROW(<font color="#FF00FF">$A$16:$A$33</font>)-ROW(<font color="#FF00FF">$A$15</font>)</font>)</font>)</font>)</font>),</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M4</th><td style="text-align:left">=INDEX(<font color="Blue">INDEX(<font color="Red">$C$16:$C$33,N(<font color="Green">IF(<font color="Purple">1,MMULT(<font color="Teal">N(<font color="#FF00FF">TRANSPOSE(<font color="Navy">$A$16:$A$33</font>)=$A$3:$A$10</font>),ROW(<font color="#FF00FF">$A$16:$A$33</font>)-ROW(<font color="#FF00FF">$A$15</font>)</font>)</font>)</font>)</font>),</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M5</th><td style="text-align:left">=INDEX(<font color="Blue">INDEX(<font color="Red">$C$16:$C$33,N(<font color="Green">IF(<font color="Purple">1,MMULT(<font color="Teal">N(<font color="#FF00FF">TRANSPOSE(<font color="Navy">$A$16:$A$33</font>)=$A$3:$A$10</font>),ROW(<font color="#FF00FF">$A$16:$A$33</font>)-ROW(<font color="#FF00FF">$A$15</font>)</font>)</font>)</font>)</font>),</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M6</th><td style="text-align:left">=INDEX(<font color="Blue">INDEX(<font color="Red">$C$16:$C$33,N(<font color="Green">IF(<font color="Purple">1,MMULT(<font color="Teal">N(<font color="#FF00FF">TRANSPOSE(<font color="Navy">$A$16:$A$33</font>)=$A$3:$A$10</font>),ROW(<font color="#FF00FF">$A$16:$A$33</font>)-ROW(<font color="#FF00FF">$A$15</font>)</font>)</font>)</font>)</font>),</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M7</th><td style="text-align:left">=INDEX(<font color="Blue">INDEX(<font color="Red">$C$16:$C$33,N(<font color="Green">IF(<font color="Purple">1,MMULT(<font color="Teal">N(<font color="#FF00FF">TRANSPOSE(<font color="Navy">$A$16:$A$33</font>)=$A$3:$A$10</font>),ROW(<font color="#FF00FF">$A$16:$A$33</font>)-ROW(<font color="#FF00FF">$A$15</font>)</font>)</font>)</font>)</font>),</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M8</th><td style="text-align:left">=INDEX(<font color="Blue">INDEX(<font color="Red">$C$16:$C$33,N(<font color="Green">IF(<font color="Purple">1,MMULT(<font color="Teal">N(<font color="#FF00FF">TRANSPOSE(<font color="Navy">$A$16:$A$33</font>)=$A$3:$A$10</font>),ROW(<font color="#FF00FF">$A$16:$A$33</font>)-ROW(<font color="#FF00FF">$A$15</font>)</font>)</font>)</font>)</font>),</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M9</th><td style="text-align:left">=INDEX(<font color="Blue">INDEX(<font color="Red">$C$16:$C$33,N(<font color="Green">IF(<font color="Purple">1,MMULT(<font color="Teal">N(<font color="#FF00FF">TRANSPOSE(<font color="Navy">$A$16:$A$33</font>)=$A$3:$A$10</font>),ROW(<font color="#FF00FF">$A$16:$A$33</font>)-ROW(<font color="#FF00FF">$A$15</font>)</font>)</font>)</font>)</font>),</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M10</th><td style="text-align:left">=INDEX(<font color="Blue">INDEX(<font color="Red">$C$16:$C$33,N(<font color="Green">IF(<font color="Purple">1,MMULT(<font color="Teal">N(<font color="#FF00FF">TRANSPOSE(<font color="Navy">$A$16:$A$33</font>)=$A$3:$A$10</font>),ROW(<font color="#FF00FF">$A$16:$A$33</font>)-ROW(<font color="#FF00FF">$A$15</font>)</font>)</font>)</font>)</font>),</font>)</td></tr></tbody></table></td></tr></table><br />



. So as you see…. Works perfectly!

. I note also your comments that maintaining this type of complicated formula may be unwise. I simply find it useful to be able to explore all possibilities
. I am not sure if I will live long enough to understand exactly how your codes work, but I will certainly spend many hours trying! But like pgc….
……I'm a ****** for understanding how it works, …..
. (In the meantime the project that I am working on combined with some of the codes you have given me may well help me to live a bit longer!!).


. I am extremely grateful

. Alan Elston

P.s. Strangely this time the spreadsheet version of your Formula
=INDEX(INDEX($C$16:$C$33,N(IF(1,MMULT(N(TRANSPOSE($A$16:$A$33)=$A$3:$A$10),ROW($A$16:$A$33)-ROW($A$15))))),)
does not work?? A very minor point but out of interest any ideas why that might be… I realize that for a spreadsheet a much simpler formula is sufficient, but having a spreadsheet equivalent close to the code version is sometimes helpful when I am trying to analyze the code
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You would have to array enter that into all the relevant cells in column M at once.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,250
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top