VBA Evaluate Range and VLOOKUP

DocAElstein · Sep 19, 2014

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

A

B

C

D

1

Produnt

2

Name

3

Chocolate-europe aroma

4

Choc

4

Chocolate-Cookies

0

Choc

5

Banana-Chocolate-Split

10

Bana

6

Limette-Ksekuchen

16

Lime

7

Erdbeere-Quark

8

Erdb

8

Erdbeere-Mix

0

Erdb

9

Jamaica Sun

6

Jama

10

Waldbeeren

0

Wald

11

12

13

14

LOOKUP Table

15

Product Name

16

Haselnu-Walnu-aromatisiert

17

Tiramisu

2

18

Chocolate-colonial blend

19

Chocolate-europe aroma

4

20

Chocolate-Cookies

21

Jamaica Sun

6

22

Himbeere-Joghurt

23

Erdbeere-Quark

8

24

Erdbeere-Mix

25

Banana-Chocolate-Split

10

26

Waldbeeren

27

Kirsche

12

28

Kirsche-grner Apfel

29

Kirsche-Ananas

14

30

Stracciatella

31

Limette-Ksekuchen

16

32

grner Apfel-Quark

33

Blutorange-Quark

Sheet1

Cell Formulas
Range	Formula
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

A

B

C

D

E

1

Produnt

2

Name

3

Chocolate-europe aroma

4

Choc

4

Chocolate-Cookies

0

Choc

5

Banana-Chocolate-Split

10

Bana

6

Limette-Ksekuchen

16

Lime

7

Erdbeere-Quark

8

Erdb

8

Erdbeere-Mix

0

Erdb

9

Jamaica Sun

6

Jama

10

Waldbeeren

0

Wald

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

A

B

C

D

E

1

Produnt

2

Name

3

Chocolate-europe aroma

4

Choc

4

Chocolate-Cookies

0

4

Choc

5

Banana-Chocolate-Split

10

4

Bana

6

Limette-Ksekuchen

16

4

Lime

7

Erdbeere-Quark

8

4

Erdb

8

Erdbeere-Mix

0

4

Erdb

9

Jamaica Sun

6

4

Jama

10

Waldbeeren

0

4

Wald

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

XOR LX · Oct 6, 2014

RoryA said:
Simples.

Quite.

DocAElstein · Oct 6, 2014

RoryA said:

Like so:

Rich (BB code):

   ……………….
Simples. ?????

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 · Oct 6, 2014

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 · Oct 6, 2014

RoryA said:
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 · Oct 6, 2014

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 · Oct 6, 2014

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

></o

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

></o

>
. I will try to go through again very carefullyuntil I understand exactly wot is going on to do justice to your efforts <o

></o

>
<o

> </o

>
Thanks<o

></o

>
<o

> </o

>
Alan<o

></o

>
<o

> </o

>
P.s. I guess if insteadof using the typical default Index of Just row….<o

></o

>
=INDEX($C$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0))<o

></o

>
<o

> </o

>
Which can be re-writtenas <o

></o

>
<o

> </o

>
=INDEX($C$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0),1)<o

></o

>
( http://www.mrexcel.com/forum/excel-questions/808351-vlookup-lookup-value-255-character-limit-2.html#post3953521 )<o

></o

>
Then writing it out forthe single column rather than the single Row<o

></o

>
<o

> </o

>
=INDEX($C$16:$C$33, 1 , MATCH($A$3:$A$10,$A$16:$A$33,0) )<o

></o

>
<o

> </o

>
. Then it might be an interesting academic exerciseand learning exercise to see if I need to do some transposing to get similar results..<o

></o

>
<o

> </o

>
. But I think for now Ihave already strained my wee brain abit too far!!<o

></o

>

DocAElstein · Oct 7, 2014

Hi RoryA,<o

></o

>
. Sorry to trouble you again on this one…….<o

></o

>
<o

> </o

>
. While I was working through again tounderstand your Mega codes I came up with a new problem<o

></o

>
<o

> </o

>
<o

> </o

>
…….. 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

></o

>
<o

> </o

>
=INDEX($A$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0),3)<o

></o

>
=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0),1)<o

></o

>
=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0))<o

></o

>
<o

> </o

>
Which (for no other thanacademic reason !!) I changed to this form<o

></o

>
<o

> </o

>
=INDEX($A$11:$C$14,MATCH(A3,$A$11:$A$14,0),3)<o

></o

>
=INDEX($C$11:$C$14,MATCH(A3,$A$11:$A$14,0),1)<o

></o

>
=INDEX($C$11:$C$14,MATCH(A3,$A$11:$A$14,0)) <o

></o

>
<o

> </o

>
Which can also bewritten slightly differently to match the final form you did for me <o

></o

>
<o

> </o

>
=INDEX(INDEX($A$11:$C$14,N(IF(1,MATCH(A3,$A$11:$A$14,0))),3),)<o

></o

>
=INDEX(INDEX($C$11:$C$14,N(IF(1,MATCH(A3,$A$11:$A$14,0))),1),)<o

></o

>
=INDEX(INDEX($C$11:$C$14,N(IF(1,MATCH(A3,$A$11:$A$14,0)))),)<o

></o

>
<o

> </o

>
Unfortunately I now seethat for reasons that are completely beyond me the last 6 formulas do notovercome the 255 character limit problem!?!<o

></o

>
<o

> </o

>
. The first three (from Dominic) do overcomethis problem!?!<o

></o

>
<o

> </o

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

></o

>
<o

> </o

>

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

></o

>
<o

> </o

>
<o

> </o

>
<o

> </o

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

></o

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

></o

>
<o

> </o

>
=INDEX($A$16:$C$33,MATCH(TRUE,INDEX($A$16:$A$33=$A$3:$A$10,0),0),3)<o

></o

>
=INDEX($C$16:$C$33,MATCH(TRUE,INDEX($A$3:$A$10,$A$16:$A$33,0),0),1)<o

></o

>
=INDEX($C$16:$C$33,MATCH(TRUE,INDEX($A$3:$A$10,$A$16:$A$33,0),0))<o

></o

>
<o

> </o

>
<o

> </o

>
. In a similar way to we(you!) achieved yesterday with these basic formulas<o

></o

>
<o

> </o

>
=INDEX($A$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0),3)<o

></o

>
=INDEX($C$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0),1)<o

></o

>
=INDEX($C$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0)) <o

></o

>
<o

> </o

>
<o

> </o

>
<o

> </o

>
Thanks<o

></o

>
Alan <o

></o

>

RoryA · Oct 7, 2014

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))))),)")

DocAElstein · Oct 7, 2014

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…

Book1

A

B

C

1

Produnt

2

Name

3

Chocolate-europe aroma

4

Chocolate-Cookies

0

5

SCHMELZKSE/WWSPREAD philadelphia 179/15/7,6/4/,1/,5/,4/ kruter 152/11/7,8/4,8/4,6/,7/,5/ leicht110/4,7/11,5/5/5/,4/,4/ kruter 186/15/9/3,7/ / /brlauch 296/28/5/5/3/1/,7/ bresco 230/19,5/8,2/5,4////frischksekruter 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////

#####

####

6

Limette-Ksekuchen

16

7

Erdbeere-Quark

8

Erdbeere-Mix

0

9

Jamaica Sun

6

10

Waldbeeren

0

11

12

=SVERWEIS(A10;$A$16:$C$33;3;FALSCH)

13

=VLOOKUP(A10,$A$16:$C$33,3,FALSE)

14

LOOKUP Table

15

Product Name

16

Haselnu-Walnu-aromatisiert

17

Tiramisu

2

18

Chocolate-colonial blend

19

Chocolate-europe aroma

4

20

Chocolate-Cookies

21

Jamaica Sun

6

22

Himbeere-Joghurt

23

Erdbeere-Quark

8

24

Erdbeere-Mix

25

SCHMELZKSE/WWSPREAD philadelphia 179/15/7,6/4/,1/,5/,4/ kruter 152/11/7,8/4,8/4,6/,7/,5/ leicht110/4,7/11,5/5/5/,4/,4/ kruter 186/15/9/3,7/ / /brlauch 296/28/5/5/3/1/,7/ bresco 230/19,5/8,2/5,4////frischksekruter 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////

10

26

Waldbeeren

27

Kirsche

12

28

Kirsche-grner Apfel

29

Kirsche-Ananas

14

30

Stracciatella

31

Limette-Ksekuchen

16

32

grner Apfel-Quark

33

Blutorange-Quark

Sheet4

…. 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

Book1

I

J

K

L

M

1

DomenicAlan

Rory1

Rory2

2

IndexFormula

Range Evaluate INDEX with MATCH

Spesadsheet Formula

Range Evaluate INDEX with MATCH

Spesadsheet Formula

3

4

5

10

6

7

8

9

10

Sheet4

Cell Formulas
Range	Formula
I3	=INDEX($C$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0))
I4	=INDEX($A$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0),3)
I5	=INDEX($A$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0),3)
I6	=INDEX($A$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0),3)
I7	=INDEX($A$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0),3)
I8	=INDEX($A$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0),3)
I9	=INDEX($A$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0),3)
I10	=INDEX($A$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0),3)
K3	=INDEX(INDEX($C$16:$C$33,N(IF(1,MATCH($A$3:$A$10,$A$16:$A$33,0)))),)
K4	=INDEX(INDEX($C$16:$C$33,N(IF(1,MATCH($A$3:$A$10,$A$16:$A$33,0)))),)
K5	=INDEX(INDEX($C$16:$C$33,N(IF(1,MATCH($A$3:$A$10,$A$16:$A$33,0)))),)
K6	=INDEX(INDEX($C$16:$C$33,N(IF(1,MATCH($A$3:$A$10,$A$16:$A$33,0)))),)
K7	=INDEX(INDEX($C$16:$C$33,N(IF(1,MATCH($A$3:$A$10,$A$16:$A$33,0)))),)
K8	=INDEX(INDEX($C$16:$C$33,N(IF(1,MATCH($A$3:$A$10,$A$16:$A$33,0)))),)
K9	=INDEX(INDEX($C$16:$C$33,N(IF(1,MATCH($A$3:$A$10,$A$16:$A$33,0)))),)
K10	=INDEX(INDEX($C$16:$C$33,N(IF(1,MATCH($A$3:$A$10,$A$16:$A$33,0)))),)
M3	=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))))),)
M4	=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))))),)
M5	=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))))),)
M6	=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))))),)
M7	=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))))),)
M8	=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))))),)
M9	=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))))),)
M10	=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))))),)

. 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….

pgc01 said:
……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 · Oct 7, 2014

You would have to array enter that into all the relevant cells in column M at once.

VBA Evaluate Range and VLOOKUP

DocAElstein

Banned user

XOR LX

Well-known Member

Excel Facts

DocAElstein

Banned user

RoryA

MrExcel MVP, Moderator

DocAElstein

Banned user

RoryA

MrExcel MVP, Moderator

DocAElstein

Banned user

DocAElstein

Banned user

RoryA

MrExcel MVP, Moderator

DocAElstein

Banned user

RoryA

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

VBA Evaluate Range and VLOOKUP

Banned user

Well-known Member

Excel Facts

Banned user

MrExcel MVP, Moderator

Banned user

MrExcel MVP, Moderator

Banned user

Banned user

MrExcel MVP, Moderator

Banned user

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock