CODEWITHSUNDEEP
excelexcel-formulavlookup
scripter78
scripter78

Reputation: 1177

excel Vlookup partial match list to list

Forgive the terrible Title. I have a list of values like this

SL- - - - - - - 
TP- - - - - - - 
AB- - - - - - - 
E5- - - - - - - 
AD- - - - - - - 
XP- - - - - - - 
MD- - - - - - - 
XH- - - - - - - 
MD- - - - - - - 
TP-TQ-TR- - - - - 
TQ-TN-TJ- - - - - 
TH- - - - - - - 
XH- - - - - - -

I have an additional list like 

SL
XP
XH
TN

I need to validate if any of the two character values in list one exist in list two. so to take for example the final result of the example above would look like 

SL- - - - - - - 
XP- - - - - - - 
XH- - - - - - - 
TQ-TN-TJ- - - - - 
XH- - - - - - -

Upvotes: 0

Views: 510

Answers (2)

SeanC
SeanC

Reputation: 15923

The asterisk is the wildcard character to use with Vlookup.

With your data in A1:A13, and the text to search for listed from H1, this is the formula I came up with to find the text

=VLOOKUP("*"&H1&"*",$A$1:$A$13,1,FALSE)

Note that this will only give the first one it comes across, so searching for TQ will give TP-TQ-TR- - - - - but not the line below it: TQ-TN-TJ- - - - -

Upvotes: 1

Dillon_Su
Dillon_Su

Reputation: 91

Here is the page for vlookup:

https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

It will explain the syntax of the formula and you will need to use TRUE for the 4th parameter so it will use an approximate match and not an exact match. It will also turn up a lot of #N/A values. To get rid of these I would just use a filter on that column.

Let me know if that works. If not explain a little about result you need or what you need it for and I will try to help again.

PS. Sort both columns before hand

Upvotes: 0

Related Questions