Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Partial VLOOKUP with asterisk in middle of data array

0

I'm trying to do a partial vlookup across data array that sometimes has an asterisk and sometimes doesn't.

Ex: I'm trying to match up 'ABC Moving' in sheet A with 'ABC*Moving' in sheet B.

The formula I'm using is =VLOOKUP("*"&lookupvalue&"*", etc) where it looks anywhere in the text.

If I'm doing the vlookup from sheet B to A, it finds the match; however, if I'm doing it from A to B, it won't find the match. What formula could I use to vlookup in both directions?

excelvlookup
posted by: bethtx23 on Wed Jun 30, 2021 at 9:52 am
Answer
Discuss

Answers

0

Hi Beth and welcome to the Forum

Without seeing your file (you can attach it to your question in future), my guess is that looking from sheet B to A, the asterisk in ABC*Moving is treated as a wildcard so it finds ABC Moving (but would also find ABCDEFGHILKL Moving e.g.).

If the presence or otherwise of an asterisk in your lookup value is the only problem, you can use that effect to your advantage by replacing any space in the lookup value with an asterisk using the SUBSTITUTE function

In the formula below, the added function is in bold :

=VLOOKUP("*"& SUBSTITUTE("<< your lookup value>>"," ","*"), << your sheet/ array>>,<< table column>> FALSE)
Seems cumbersome but should work.

If you have Excel365, you might also try this tutorial  Excel 365 Wildcard Vlookup to Return All Partial Matches (but I haven't!)

posted by: John_Ru (rep: 6142) on Wed Jun 30, 2021 at 12:41 pm
Discuss

Discussion

Corrected my answer above (was working for memory but didn't need the wrap-around ISERROR addition I had first used)
John_Ru (rep: 6142) Jun 30, '21 at 1:15 pm
Thank you so much!  I've attached a file with two tabs.  Your formula worked great in the first tab where the asterisk was the issue.  I was trying to use it on the second tab where the middle initial is interfering with a match.  I think I need a different formula for that though.
bethtx23 Jul 1, '21 at 11:04 am
Beth
It looks like you're trying to match payments (so you need to be carefully that one from John Smith is not confused with one from John F Smith). Remember that the astersisk represents something or nothing so the second example in sheet 2,  John(something)F(something)Smith is searched for and doesn't exist in A:B. VLOOKUP will only return the first match it make so if you search for John*Smith, it may return a value for John F Smith if that occurs first in the array (and you're using an Exact search) which the FALSE in my formula makes it)
John_Ru (rep: 6142) Jul 1, '21 at 11:47 am
That makes sense.  Really appreciate your help!
bethtx23 Jul 1, '21 at 12:17 pm
Okay Beth. Don't forget that if my Answer solved your issue, you should mark it as Selected (this guides other users looking for similar solutions)
John_Ru (rep: 6142) Jul 1, '21 at 12:21 pm
Yes, I will thank you!  One more question though on this please. 

Let's say I wanted to clean up the names to be in the same format before I perform the vlookup. Some names contain the middle initial and some don't.  I want the cells to only be first name last name.

Ex: One cell says John Smith and the other cell says John F Smith.  

Is there a single formula I could use to return the first last (meaning extract the middle initial), if applicable?  So, I need John Smith to return John Smith and John F Smith to return John Smith.
bethtx23 Jul 1, '21 at 12:41 pm
Beth, if you have a name in cell A1,try this:
=IFERROR(TRIM(LEFT(A1,FIND(" ",A1,1))) & " " & TRIM(MID(A1,FIND(" ",A1,FIND(" ",A1,1)+1)+1,LEN(A1)-FIND(" ",A1,1)+1)),A1)
It should clean out a middle initial (plus TRIM will remove any spaces at the start and end). Once you've done that, copy then paste values to "fix" the names.

Please note that this isn't a discussion forum (it's Q& A) so I shouldn't 
be answering "in the weeds" of a Discussion
John_Ru (rep: 6142) Jul 1, '21 at 1:19 pm
Did that work for you?
John_Ru (rep: 6142) Jul 2, '21 at 5:25 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login