0

Please refer to earlier posted question at Unable to use SUBSTITUTE(ADDRESS(MATCH))) combination as a range in a LOOKUP function in Excel

I was recommended this Excel formula to identify a value using reverse look-up by Scott Craner and it works:

=LOOKUP(2,1/((A:A=2018)*(B:B=31)),INDEX(A:AAA,0,MATCH("Employee2",1:1,0)))

In this case, the answer is numeric 15.

I wanted to convert the same to a VBA code statement using:

Dim i as Integer

i = Application.WorksheetFunction.Lookup(2, 1 / ((Range("A:A") = 2018) * (Range("B:B") = 31)), 
Application.WorksheetFunction.Index(Range("A:AAA"), 0, 
Application.WorksheetFunction.Match("Employee2", Range("1:1"), 0)))

It gives "Run-time error '13': Type mismatch".

Can someone please help me to figure out what I'm not doing right - in terms of converting the Excel formula to VBA?

Thanks for your help in advance.

PaichengWu
  • 2,649
  • 1
  • 14
  • 28
S. Shaw
  • 11
  • 5
  • 1
    Use Evaluate: `i = Evaluate("LOOKUP(2,1/((A:A=2018)*(B:B=31)),INDEX(A:AAA,0,MATCH(""Employee2"",1:1,0)))")` – tigeravatar Aug 08 '18 at 15:18
  • Thanks, tigeravatar. Your suggestion of EVALUATE function works well for the formula conversion. It has helped me a lot!! – S. Shaw Aug 08 '18 at 15:32
  • Can I use a string variable (say "strEmp" in place of ""Employee2"" in this formula? – S. Shaw Aug 08 '18 at 15:39
  • 1
    Yes, if your variable is `strEmp` then the line becomes: `i = Evaluate("LOOKUP(2,1/((A:A=2018)*(B:B=31)),INDEX(A:AAA,0,MATCH(" & strEmp & ",1:1,0)))")` – tigeravatar Aug 08 '18 at 15:51
  • Thanks for your answer. However I got a Type mismatch error. But I found this to work for me: i = Evaluate("LOOKUP(2,1/((A:A=2018)*(B:B=31)),INDEX(A:AAA,0,MATCH(""" & strEmp & """,1:1,0)))") – S. Shaw Aug 08 '18 at 18:24
  • Ah, good call, still need to surround the variable with quotes so the formula treats it as a string properly. – tigeravatar Aug 08 '18 at 18:25

0 Answers0