We help IT Professionals succeed at work.
Get Started

Reverse Excel VLookup with Wildcard?

163 Views
Last Modified: 2018-02-15
Hello

Not sure of the terminology but I think what I want to do is reverse the logic of this vlookup , amend the wildcard slightly so that only full word matches are found and I guess to do this it has to be converted to a VBA loop and the search text split into an array.

=IF(LEN(Products[Name] )>2, VLOOKUP("*" & Products[Name] &"*",Orders[Description],1,FALSE),"N/A")

The above works on a list of product names, finding those that appear in order descriptions. What I really need to do is loop over the order descriptions and add the first match of any product name into an adjacent column so that each order description has the product name next to it. Also the above gives a few false positives where the product name is contained in a word. So I need to only match where the product name is immediately preceded and followed by a space or other special character such as hyphen,forward slash, backslash or parenthesis.

Any help appreciated.

Thanks!
Comment
Watch Question
Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
Unlock 2 Answers and 6 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE