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

Wildcard Partial match Vlookup question

0

Need to do a partial match on set of characters emdedded in parentheses and match to a table and return the property type. The set of characters could be 2 or 3 characters. Having trouble trying to place the wild card.  See example spread sheet attached.  

Example:                                                              Match                 Return

(S10) 2345 Connor Dr                                           (S10)                 4635/5620 Sale/Land

Thank You!

Cathy

excel
MS Office Version: 365
posted by: CCCS (rep: 2) on Mon Sep 21, 2020 at 4:16 pm
Answer
Discuss

Answers

0
Selected Answer

Hi Cathy! Glad you saw my comment and also uploaded a sample file, makes life much easier :)

This is the formula that you want:

=VLOOKUP(LEFT(A2,FIND(")",A2,1)),Type!$A$2:$B$24,2,FALSE)

Put it into cell B2 on the Address worksheet and copy it down and it should work.

LEFT(A2,FIND(")",A2,1)) -> this is the part that gets the (L4) type lookup value out on its own for use in the vlookup. You don't need to use any wildcard characters because this pulls in the entire lookup value and you only need the wildcard if the table from which you want to return the value doesn't have a column that exactly matches your lookup value, which is no longer an issue.

If you want to learn more about partial matches anyway, you can look at the tutorial here: Vlookup Partial Match in Excel

posted by: don (rep: 1989) on Mon Sep 21, 2020 at 5:04 pm
Discuss

Discussion

In place of LEFT(A2,FIND(")",A2,1)) I was about to suggest TRIM(LEFT(A2,FIND(")", A2))) in case there could be a stray leading period (depending upon how the "(S10)" got into the string in A2) or even MID(LEFT(A2,FIND(")", A2)),FIND("(",A2),10) which would pick out the bracketed number from anywhere in the A2 string.
Variatus (rep: 4889) Sep 21, '20 at 8:52 pm
Hello,

The formula worked for the sample data, however it's not working for the real data. The only difference is that the the FIND and RETURN are in a different column. The FIND starts at G4 and the Return comes column F.starting at line 2
Here is my formula: =VLOOKUP(LEFT(G4,FIND(")",G4,1)),Type F!$A$2:$F$24,2,FALSE)

Perplexed  :(
Cathy
CCCS (rep: 2) Sep 23, '20 at 12:00 pm
Read up on the VLOOKUP() function. Your formula instructs to look at the range Type F!$A$2:$F$24. It should find LEFT(G4,FIND(")",G4,1)) in the first column. That's because VLOOKUP always looks in the first column. Then it should return the value from the second column (in the row of the match). That's because ",2," in your formula specifies the 2nd column.
In the range you specify, A$2:$F$24, column F is the 6th column. That's fine if the 'Property Address' is in column A. If it's in column G VLOOKUP can't be used because the return must come from a column to the right of the lookup column. Now Excel has XLOOKUP() for that isn't possible. You might need a formula like, =XLOOKUP(LEFT(G4,FIND(")",G4,1)),Type F!$G$2:$G$24,Type F!$F$2:$F$24)
Variatus (rep: 4889) Sep 23, '20 at 8:13 pm
Add to Discussion


Answer the Question

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