VBA Evaluate & VLOOKUP

[mse330]

Hello everyone,

I have been using Evaluate() in my VBA code recently to understand how it works & enjoy the potential of this function. However, I just ran into a case where I was unable to make it work correctly using VLOOKUP. I have made a small sample & used MATCH function to ensure my syntax is correct & the MATCH function works as expected ?

After further inspection, I noticed that the VLOOKUP uses table_array whereas MATCH uses lookup_array (as you can see in the screen shot) which I am not sure if this is what is causing my code to give incorrect results

Does anyone have any explanation for this or how to make my evaluate with VLOOKUP to work correctly ?

Sub test()
With Range("C2:C4")
   .Offset(, 1) = Evaluate("if({1},vlookup(" & .Address & ",$A$2:$A$5,1,0))")
   .Offset(, 4) = Evaluate("if({1},match(" & .Address & ",$A$2:$A$5,0))")
End With
End Sub

Copy of Worksheets.xlsm
	A	B	C	D	E	F	G	H
1	Names		Names	Evaluate	VlookUp		Evaluate	Match
2	Jack		Sara	Sara	Sara		2	2
3	Sara		Jeff	Sara	#N/A		#N/A	#N/A
4	Mike		Fred	Sara	Fred		4	4
5	Fred
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=VLOOKUP(C2,$A$2:$A$5,1,0)
H2:H4	H2	=MATCH(C2,$A$2:$A$5,0)

 

[Fluff]

Try it like

Sub test()
With Range("C2:C4")
   .Offset(, 1) = Evaluate("if({1},vlookup(t(if({1}," & .Address & ")),$A$2:$A$5,1,0))")
   .Offset(, 4) = Evaluate("if({1},match(" & .Address & ",$A$2:$A$5,0))")
End With
End Sub

VBA, VLOOKUP possible in EVALUATE function? Results not as expected

Hi, I have a two column VLOOKUP table, which currently I'm passing to a dictionary object to replace output with mapped data. I'm trying to see if I can reduce the amount of code by using VLOOKUP within EVALUATE, so far have tried: Sub test() 'F5:F8 With Cells(5, 6).Resize(4)...

www.mrexcel.com

 

[mse330]

@Fluff Thank you very much ... Always very fast response & right to the point. I was searching before posting a new thread & found a 10-page thread from 2014 HERE which I lost track while I was reading

Again, much appreciated & have a nice weekend

 

[Fluff]

Not surprised you lost track. Reading any thread involving the Doc, makes me loose the will to live.

 

[Yongle]

Reading any thread involving the Doc, makes me loose the will to live.

@Fluff Some people have no stamina

 

[Fluff]

Have you read any of his posts.

 

[mse330]

Reading any thread involving the Doc, makes me loose the will to live.

LOL ?

 

[Yongle]

Have you read any of his posts.

I find them .... unique ?