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 ?
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 ?
VBA Code:
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) |