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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
RoryA said:
Like so:

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


	

		clip_image001.gif
Click to expand...
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.
 
Upvote 0
I basically just took the formula examples on XOR LX's site that was linked to earlier, and tweaked them to make Evaluate work. :)
 
Upvote 0
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. :)
Click to expand...


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
 
Upvote 0
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.
 
Upvote 0
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.
Click to expand...

. 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>
 
Upvote 0
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>
 
Upvote 0
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:
Upvote 0
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
ABC
1Produnt
2Name
3Chocolate-europe aroma44
4Chocolate-Cookies00
5SCHMELZKSE/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////#########
6Limette-Ksekuchen1616
7Erdbeere-Quark88
8Erdbeere-Mix00
9Jamaica Sun66
10Waldbeeren00
11
12=SVERWEIS(A10;$A$16:$C$33;3;FALSCH)
13=VLOOKUP(A10,$A$16:$C$33,3,FALSE)
14LOOKUP Table
15Product Name
16Haselnu-Walnu-aromatisiert
17Tiramisu2
18Chocolate-colonial blend
19Chocolate-europe aroma4
20Chocolate-Cookies
21Jamaica Sun6
22Himbeere-Joghurt
23Erdbeere-Quark8
24Erdbeere-Mix
25SCHMELZKSE/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
26Waldbeeren
27Kirsche12
28Kirsche-grner Apfel
29Kirsche-Ananas14
30Stracciatella
31Limette-Ksekuchen16
32grner Apfel-Quark
33Blutorange-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
IJKLM
1DomenicAlanRory1Rory1Rory2Rory2
2IndexFormulaRange Evaluate INDEX with MATCHSpesadsheet FormulaRange Evaluate INDEX with MATCHSpesadsheet Formula
34444#WERT!
400#WERT!
5#WERT!#WERT!#WERT!10#WERT!
616161616#WERT!
78888#WERT!
800#WERT!
96666#WERT!
1000#WERT!
Sheet4
Cell Formulas
RangeFormula
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, …..
Click to expand...
. (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
 
Upvote 0
You would have to array enter that into all the relevant cells in column M at once.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
How to vlookup the characters between a delimiter
Replies
3
Views
175
Fluff
Fluff
E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
269
offthelip
O
Z
  • Solved
VBA: Convert Cell Contents into Hyperlink on Cell Change
Replies
5
Views
494
zero269
Z
P
  • Solved
How to vlookup a general format cell and convert it to a date
Replies
4
Views
244
Fluff
Fluff
J
  • Solved
VLookup Struggles
Replies
7
Views
284
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,551
Messages
6,120,161
Members
448,948
Latest member
spamiki

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