VBA Evaluate Range and VLOOKUP

DocAElstein

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!) :-



Book1
ABCD
1Produnt
2Name
3Chocolate-europe aroma4Choc
4Chocolate-Cookies0Choc
5Banana-Chocolate-Split10Bana
6Limette-Ksekuchen16Lime
7Erdbeere-Quark8Erdb
8Erdbeere-Mix0Erdb
9Jamaica Sun6Jama
10Waldbeeren0Wald
11
12
13
14LOOKUP Table
15Product Name
16Haselnu-Walnu-aromatisiert
17Tiramisu2
18Chocolate-colonial blend
19Chocolate-europe aroma4
20Chocolate-Cookies
21Jamaica Sun6
22Himbeere-Joghurt
23Erdbeere-Quark8
24Erdbeere-Mix
25Banana-Chocolate-Split10
26Waldbeeren
27Kirsche12
28Kirsche-grner Apfel
29Kirsche-Ananas14
30Stracciatella
31Limette-Ksekuchen16
32grner Apfel-Quark
33Blutorange-Quark
Sheet1
Cell Formulas
RangeFormula
B3=VLOOKUP(A3,$A$16:$C$33,3,FALSE)
B4=VLOOKUP(A4,$A$16:$C$33,3,FALSE)
B5=VLOOKUP(A5,$A$16:$C$33,3,FALSE)
B6=VLOOKUP(A6,$A$16:$C$33,3,FALSE)
B7=VLOOKUP(A7,$A$16:$C$33,3,FALSE)
B8=VLOOKUP(A8,$A$16:$C$33,3,FALSE)
B9=VLOOKUP(A9,$A$16:$C$33,3,FALSE)
B10=VLOOKUP(A10,$A$16:$C$33,3,FALSE)
D3=LEFT(A3,4)
D4=LEFT(A4,4)
D5=LEFT(A5,4)
D6=LEFT(A6,4)
D7=LEFT(A7,4)
D8=LEFT(A8,4)
D9=LEFT(A9,4)
D10=LEFT(A10,4)




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:-


Book1
ABCDE
1Produnt
2Name
3Chocolate-europe aroma4ChocChoc
4Chocolate-Cookies0ChocChoc
5Banana-Chocolate-Split10BanaBana
6Limette-Ksekuchen16LimeLime
7Erdbeere-Quark8ErdbErdb
8Erdbeere-Mix0ErdbErdb
9Jamaica Sun6JamaJama
10Waldbeeren0WaldWald
11
12
Sheet1


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:-



Book1
ABCDE
1Produnt
2Name
3Chocolate-europe aroma44ChocChoc
4Chocolate-Cookies04ChocChoc
5Banana-Chocolate-Split104BanaBana
6Limette-Ksekuchen164LimeLime
7Erdbeere-Quark84ErdbErdb
8Erdbeere-Mix04ErdbErdb
9Jamaica Sun64JamaJama
10Waldbeeren04WaldWald
11
12
Sheet1


. 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
 
Sort by date Sort by votes

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Hi pgc or XOR LX or RoryA,

. Thanks for making this a very interesting Thread!

. I am still learning as I work my way through it!.

. Coming back to my original problem up to about post #8 and post #17

. Any clues on If / how I can get my VBA Evaluate line to give me the correct values in Column E as obtained by the other methods discussed in this thread and shown in the Screen shot below. (Again I agree the other solutions may be more practical- Again it is part of a learning exercise for me to explore all possibilities. I expect it may be of interest also to others viewing this thread)



Book1
ABCDE
1Produnt
2Name
3Chocolate-europe aroma444
4Chocolate-Cookies000
5Banana-Chocolate-Split101010
6Limette-Ksekuchen161616
7Erdbeere-Quark888
8Erdbeere-Mix000
9Jamaica Sun666
10Waldbeeren000
11
12
13
14LOOKUP Table
15Product Name
16Haselnu-Walnu-aromatisiert
17Tiramisu2
18Chocolate-colonial blend
19Chocolate-europe aroma4
20Chocolate-Cookies
21Jamaica Sun6
22Himbeere-Joghurt
23Erdbeere-Quark8
24Erdbeere-Mix
25Banana-Chocolate-Split10
26Waldbeeren
27Kirsche12
28Kirsche-grner Apfel
29Kirsche-Ananas14
30Stracciatella
31Limette-Ksekuchen16
32grner Apfel-Quark
33Blutorange-Quark
Sheet1
Cell Formulas
RangeFormula
B3=VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE)
B4=VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE)
B5=VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE)
B6=VLOOKUP(A6,$A$16:$C$33,3,FALSE)
B7=VLOOKUP(A7,$A$16:$C$33,3,FALSE)
B8=VLOOKUP(A8,$A$16:$C$33,3,FALSE)
B9=VLOOKUP(A9,$A$16:$C$33,3,FALSE)
B10=VLOOKUP(A10,$A$16:$C$33,3,FALSE)
C3=VLOOKUP($A$3:$A$10,$A$16:$C$33,3,FALSE)
C4=VLOOKUP($A$3:$A$10,$A$16:$C$33,3,FALSE)
C5=VLOOKUP($A$3:$A$10,$A$16:$C$33,3,FALSE)
C6=VLOOKUP($A$3:$A$10,$A$16:$C$33,3,FALSE)
C7=VLOOKUP($A$3:$A$10,$A$16:$C$33,3,FALSE)
C8=VLOOKUP($A$3:$A$10,$A$16:$C$33,3,FALSE)
C9=VLOOKUP($A$3:$A$10,$A$16:$C$33,3,FALSE)
C10=VLOOKUP($A$3:$A$10,$A$16:$C$33,3,FALSE)
D3:D10{=VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE)}
Press CTRL+SHIFT+ENTER to enter array formulas.



. Here are some code attempts from me that do not work:

Code: 
[color=darkblue]Sub[/color] Evaluate_VLOOKUPpgcXORLXRoryA()

[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] vEE [color=darkblue]As[/color] [color=darkblue]Variant[/color], TEE [color=darkblue]As[/color] [color=darkblue]Variant[/color]
[color=darkblue]Dim[/color] rngEE [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] rngEE = ThisWorkbook.Worksheets("sheet1").Range("E3:E10")
[color=green]'Let rngEE = Evaluate("if(row(3:10),{=VLOOKUP(" & rngName.Address & ",$A$16:$C$33,3,FALSE)} )")[/color]
[color=green]'Let rngEE = Evaluate("{=VLOOKUP(" & rngName.Address & ",$A$16:$C$33,3,FALSE)} ")[/color]
[color=green]'Let rngEE = Evaluate("VLOOKUP(" & rngName.Address & ",$A$16:$C$33,3,FALSE) ")[/color]
[color=green]'Let rngEE = Evaluate("if(row(3:10),VLOOKUP(" & rngName.Address & ",$A$16:$C$33,3,FALSE) )")[/color]
[color=green]'Let rngEE = Evaluate("VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE) ")[/color]
[color=green]'Let rngEE = Evaluate("VLOOKUP($A$3:$A$10,$A$16:$C$33,3,FALSE) ")[/color]
[color=green]'Let vEE = Evaluate("VLOOKUP($A$3:$A$10,$A$16:$C$33,3,FALSE) ")[/color]
[color=green]'Let vEE = Evaluate("VLOOKUP(T($A$3:$A$10),$A$16:$C$33,3,FALSE) ")[/color]
[color=green]'Let vEE = Evaluate("VLOOKUP(TEE($A$3:$A$10),$A$16:$C$33,3,FALSE) ")[/color]
[color=green]'Let vEE = Evaluate("VLOOKUP(TEE($A$3:$A$10,$A$16:$C$33,3,FALSE) ) ")[/color]
[color=green]'Let rngEE = Evaluate("if(row(3:10),VLOOKUP($A$3:$A$10,$A$16:$C$33,3,FALSE) ) ")[/color]
[color=green]'Let rngEE = Evaluate("if(row(3:10),VLOOKUP(T($A$3:$A$10),$A$16:$C$33,3,FALSE) ) ")[/color]
[color=green]'Let rngEE = Evaluate("if(row(3:10),VLOOKUP(T($A$3:$A$10,$A$16:$C$33,3,FALSE)) ) ")[/color]
[color=green]'Let rngEE = Evaluate("INDEX(VLOOKUP($A$3:$A$10,$A$16:$C$33,3,FALSE),) ")[/color]
[color=green]'Let rngEE = Evaluate("if(row(3:10),INDEX(VLOOKUP($A$3:$A$10,$A$16:$C$33,3,FALSE),) ) ")[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'Evaluate_VLOOKUPpgcXORLXRoryA()[/color]

Thanks.

Alan.

P.s. I originally hit on Evaluate as an alternative to looping to speed things up. … But I was not using VLOOKUP then…. So it may well be that the best code for me is

Code: 
[color=darkblue]Sub[/color] Test3c_pgc()

[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 = Application.WorksheetFunction.VLookup(rngName, Range("A16:C33"), 3, [color=darkblue]False[/color])


[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'Test3c_pgc()[/color]

… as it does not involve looping. However to rap the thread up a VBA Evaluate line solution would be useful…or a definite statement as to if it is possible or not??
 
Upvote 0
. Bump ( on Post # 22). Any guesses on If / how I can get (“coerce”) my VBA Evaluate line to give me the correct values in Column E

Thanks
Alan Elston.
 
Upvote 0
. Just another Bump ( on Post # 22). Any guesses on a VBA Evaluate line to give me the correct values in Column E. Or a definite statement / reason if/ why it is not possible


Thanks
Alan
 
Upvote 0
Like this:

Code: 
Sub Evaluate_VLOOKUPpgcXORLXRoryA()

    Dim rngName               As Range
    Dim rngEE                 As Range
    
    Set rngName = ThisWorkbook.Worksheets("Sheet1").Range("A3:A10")
    Set rngEE = ThisWorkbook.Worksheets("sheet1").Range("E3:E10")

    rngEE.Value = Evaluate("transpose(INDEX(VLOOKUP(T(IF(1,TRANSPOSE(" & _
                            rngName.Address & "))),A16:C33,3,FALSE),))")
End Sub
 
Upvote 0
RoryA said:
Like this:
Click to expand...
RoryA said:


……….
Click to expand...
<o:p></o:p>
<o:p> </o:p>
. RoryA… I can only say thanks and WOW!... howdoes the saying go…” The very difficult you do straight away.. The totallyimpossible takes a bit longer!..” <o:p></o:p>
. How on earth can you come up with somethinglike that?? I only hope I can get the time to figure out how it works. <o:p></o:p>
<o:p> </o:p>
. I already have it in use in your codeversion <o:p></o:p>
…..and spreadsheetforms….(arbitrarily for VlookUp value A3)<o:p></o:p>
German:<o:p></o:p>

=MTRANS( INDEX( SVERWEIS( T( WENN( 1;MTRANS( A5 ) ) );$A$16:$C$33;3;0 ); ) )<o:p></o:p>
<o:p> </o:p>
English:<o:p></o:p>
=TRANSPOSE(INDEX(VLOOKUP(T(IF(1,TRANSPOSE(A3))),$A$16:$C$33,3,0),)) <o:p></o:p>
<o:p> </o:p>
Many thanks<o:p></o:p>
Alan Elston<o:p></o:p>
 
Upvote 0
. Hi RoryA (or anyone else viewing!)

. I have really tried more hours than I like to remember now in Excel, Google, and “Rambling” endless in the Test Area Forum to try and pull apart and understand your “Mega one liner”.
. The reason for doing this is that I wish to learn and I was hoping then to be able to use that knowledge to get my own INDEX MATCH Alternative “One liner” to your “VLOOKUP one liner” ( I guess I may reference another of my threads on this one as it is very relevant and you were also in it.. http://www.mrexcel.com/forum/excel-...-value-255-character-limit-2.html#post3953521 )
. Unfortunately it is just proving a bridge too far for my beginner’s brain! I am sure I have been very close many times now. I was able to make some minor mods which did not stop it from working. But basically everything needs to be there even though the logic is well above me..

. If you have the time to explain it in a bit more detail exactly I would be very grateful. And I think it would be of great interest to many.. I have heard a few times said that wot you achieved was probably not possible!!

. Any hint then in the direction of a corresponding INDEX MATCH alternative would also be welcome, but I am not lazy and will keep battling at that.

Thanks again for your efforts
Alan
 
Upvote 0
Hi again RoryA!


. So as I said my aim is understand your Mega “one liner” and therefore, for example, to apply it to a similar “Range INDEX MATCH EVALUATE” equivalent “One Liner”…

. I went crazy over the last week in the test area both with trying to understand your Range Evaluate VLOOKUP and trying to work out a similar version for Range Evaluate INDEX with MATCH

. So as regards
RoryA said:
Can you post a simple table of what you have (and where) and what results you want from your code?
................
Click to expand...
…….. Let’s say I try to put in Range JJ exactly wot you achieved for me in Range EE with your Mega one liner (Range Evaluate VLOOKUP) code, but for the results in range JJ I try to use the INDEX with MATCH alternative for VLOOKUP as discussed in (( mhttp://www.mrexcel.com/forum/excel-questions/808351-vlookup-lookup-value-255-character-limit-2.html#post3958205 ) to which you replied this morning)



I had a go……these all seem Ok, at least they compile OK, but do not work


Code: 
[color=darkblue]Sub[/color] Evaluate_VLOOKUPorINDEX()
[color=green]'[/color]
'................VLOOKUP
    [color=darkblue]Dim[/color] rngName               [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] rngEE                 [color=darkblue]As[/color] Range
   
    [color=darkblue]Set[/color] rngName = ActiveSheet.Range("A3:A10")
    [color=darkblue]Set[/color] rngEE = ActiveSheet.Range("E3:E10")
 
    [color=green]'Let rngEE.Value = Evaluate("transpose(INDEX(VLOOKUP(T(IF(1,TRANSPOSE(" & rngName.Address & "))),A16:C33,3,FALSE),))")[/color]
    [color=green]'                                   =VLOOKUP(A10,$A$16:$C$33,3,FALSE)[/color]
    [color=green]'                                  =VLOOKUP($A$3:$A$10,$A$16:$C$33,3,FALSE)[/color]
    [color=green]'Let rngEE.Value = Evaluate("VLOOKUP(A3,$A$16:$C$33,3,FALSE)")[/color]
    [color=green]'Let rngEE.Value = Evaluate("VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE)")[/color]
    [color=green]'Let rngEE.Value = Evaluate("transpose(INDEX(VLOOKUP(T(IF(Row(),TRANSPOSE(A3:A10))),A16:C33,3,FALSE),))")[/color]
    [color=darkblue]Let[/color] rngEE.Value = Evaluate("transpose(INDEX(VLOOKUP(T(IF(Row(),TRANSPOSE(" & rngName.Address & "))),A16:C33,3,FALSE),))")
 
[color=green]'................INDEX[/color]
    [color=darkblue]Dim[/color] rngJJ                 [color=darkblue]As[/color] Range
    [color=darkblue]Set[/color] rngJJ = ActiveSheet.Range("J3:J10")
    [color=green]'                                   =INDEX($C$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0))[/color]
    [color=green]'                                  =INDEX($C$16:$C$33;VERGLEICH($A$3:$A$10;$A$16:$A$33;0))[/color]
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("MATCH($A$3:$A$10,$A$16:$A$33,0)")
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("IF(Row()," & "MATCH(" & rngName.Address & ",$A$16:$A$33,0)" & ")")
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("IF(Row()," & "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))")
   
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX($C$16:$C$33,IF(Row(),MATCH($A$3:$A$10,$A$16:$A$33,0)))")
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("IF(Row(),INDEX($C$16:$C$33,IF(Row(),MATCH($A$3:$A$10,$A$16:$A$33,0))))")
   
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("TRANSPOSE(INDEX(TRANSPOSE($C$16:$C$33),IF(Row(),MATCH($A$3:$A$10,$A$16:$A$33,0))))")
   
   
   
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX($C$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0))")
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("TRANSPOSE(INDEX($C$16:$C$33,MATCH(TRANSPOSE($A$3:$A$10),$A$16:$A$33,0)))")
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("TRANSPOSE(INDEX($C$16:$C$33,MATCH(TRANSPOSE($A$3:$A$10),$A$16:$A$33,0)))")
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX($C$16:$C$33,TRANSPOSE(MATCH(TRANSPOSE($A$3:$A$10),$A$16:$A$33,0)))")
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX($C$16:$C$33,TRANSPOSE(MATCH(IF(Row(),TRANSPOSE($A$3:$A$10)),$A$16:$A$33,0)))")
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX($C$16:$C$33,TRANSPOSE(MATCH(T(IF(Row(),TRANSPOSE($A$3:$A$10))),$A$16:$A$33,0)))")
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX($C$16:$C$33,TRANSPOSE(MATCH(T(IF(1,TRANSPOSE($A$3:$A$10))),$A$16:$A$33,0)))")
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX($C$16:$C$33,TRANSPOSE(INDEX(MATCH(T(IF(Row(),TRANSPOSE($A$3:$A$10))),$A$16:$A$33,0),)))")
   
 
   
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("TRANSPOSE(INDEX(TRANSPOSE($C$16:$C$33),TRANSPOSE(INDEX(MATCH(T(IF(Row(),TRANSPOSE($A$3:$A$10))),$A$16:$A$33,0),))))")
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("TRANSPOSE(INDEX(IF(Row(),TRANSPOSE($C$16:$C$33)),TRANSPOSE(INDEX(MATCH(T(IF(Row(),TRANSPOSE($A$3:$A$10))),$A$16:$A$33,0),))))")
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("TRANSPOSE(INDEX(T(IF(Row(),TRANSPOSE($C$16:$C$33))),TRANSPOSE(INDEX(MATCH(T(IF(Row(),TRANSPOSE($A$3:$A$10))),$A$16:$A$33,0),))))") [color=green]'Gives empty entry!?[/color]
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("TRANSPOSE(INDEX(T(IF(1,TRANSPOSE($C$16:$C$33))),TRANSPOSE(INDEX(MATCH(T(IF(Row(),TRANSPOSE($A$3:$A$10))),$A$16:$A$33,0),))))") [color=green]'Gives empty entry!?[/color]
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("TRANSPOSE(INDEX(IF(Row(),TRANSPOSE($C$16:$C$33)),TRANSPOSE(INDEX(MATCH(T(IF(Row(),TRANSPOSE($A$3:$A$10))),$A$16:$A$33,0),))))")
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("TRANSPOSE(INDEX(INDEX(IF(Row(),TRANSPOSE($C$16:$C$33)),TRANSPOSE(INDEX(MATCH(T(IF(Row(),TRANSPOSE($A$3:$A$10))),$A$16:$A$33,0),))),))")
   
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX(INDEX(IF(Row(),$C$16:$C$33),INDEX(MATCH(T(IF(Row(),$A$3:$A$10)),$A$16:$A$33,0),)),)")
   
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX($C$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0))")
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("TRANSPOSE(INDEX(TRANSPOSE($C$16:$C$33),MATCH($A$3:$A$10,$A$16:$A$33,0)))")
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("TRANSPOSE(INDEX(IF(Row(),TRANSPOSE($C$16:$C$33)),MATCH($A$3:$A$10,$A$16:$A$33,0)))")
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("TRANSPOSE(INDEX(T(IF(Row(),TRANSPOSE($C$16:$C$33))),MATCH($A$3:$A$10,$A$16:$A$33,0)))") [color=green]'Gives empty entry!?[/color]
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("TRANSPOSE(INDEX(IF(Row(),TRANSPOSE($C$16:$C$33)),MATCH($A$3:$A$10,$A$16:$A$33,0)))")
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("TRANSPOSE(INDEX(INDEX(IF(Row(),TRANSPOSE($C$16:$C$33)),MATCH($A$3:$A$10,$A$16:$A$33,0)),))")
      
      
  
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'Evaluate_VLOOKUPorINDEX()[/color]


…. Any help in understanding the code would be appreciated. I realize it is not easy. –

. Hope I have got everything clear. Sorry for any confusion. (I was confused that you replied to the other thread and not here??)


. Alan

P.s.
Maybe in this case it might be appropriate to start another couple of Threads along the lines

. 1. Help in understanding Evaluate Range VLOOKUP

. 2. Evaluate Range INDEX with MATCH
 
Upvote 0
Like so:

Code: 
    Let rngJJ.Value = Evaluate("INDEX(INDEX($C$16:$C$33,N(IF(1,MATCH($A$3:$A$10,$A$16:$A$33,0)))),)")

Simples. ;)
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Vlookup: unexplainable - for me - #N/A
Replies
3
Views
81
Hans Troost
H
HansM
  • Question Question
Looking for a specific number in a cell that can have multiple numbers separated by a comma (Using Mac Excel)
Replies
5
Views
312
Jeffrey Mahoney
Jeffrey Mahoney
P
  • Solved
How to vlookup the characters between a delimiter
Replies
3
Views
734
Fluff
Fluff
K
  • Solved
COUNTIFS formula.
2
Replies
14
Views
624
Kayslover
K
L
  • Question Question
Help with formula to keep leading zero in cell - cant format as date
Replies
4
Views
72
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,224,862
Messages
6,181,458
Members
453,042
Latest member
AbdelrahmanExcel

Share this page

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
Back
Top