excel partial match in any of the lookup data

[chaitanya1124]

I have data in column A and B have lookup data. If the any of the lookup data is partially matching in the result column need to put lookup data.


I tried with VLOOKUP but no luck. Please help me.


Columns:


A B(Lookup) C(Result)
FY|F|V|D|Safety|3M Toro 3M
FY|F|V|D|POP|ToroDays 3M Toro
FY|F|V|D|Lumber|GroundContact honeywell NA(Not available)
FY|F|V|D|airfilters|honeywell samsung honeywell

 

[mse330]

Welcome to the forums

Try the below vlookup formula

=VLOOKUP("*"&B2&"*",A:A,1,0)

 

[chaitanya1124]

No. Its not working. It is giving NA. Table was not populated properly, so I pasted here again.

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">A B(Lookup) C(Result)
FY|F|V|D|Safety|3M Toro 3M
FY|F|V|D|POP|ToroDays 3M Toro
FY|F|V|D|Lumber|GroundContact honeywell NA(Not available)
FY|F|V|D|airfilters|honeywell samsung honeywell</code>

 

[chaitanya1124]

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">A                                  B(Lookup)            C(Result)
FY|F|V|D|Safety|3M                 Toro                 3M
FY|F|V|D|POP|ToroDays              3M                   Toro
FY|F|V|D|Lumber|GroundContact      honeywell            NA(Not available)
FY|F|V|D|airfilters|honeywell      samsung              honeywell</code>

 

[mse330]

I am a bit confused here ... Toro is the lookup value that you've searched for in cell A2 which doesn't exist yet you have 3M in the result (column C). However, Honeywell is showing NA in column C & Samsung is not showing NA yet it doesn't exist at all in column A

 

[chaitanya1124]

Requirement is: for A2 (FY|F|V|D|Safety|3M). take the B column first value and search whether there is any match if not take second value in B column and repeat the process for all the B column values and if there is match print the B column value in Result. Does that make sense?

 

[mse330]

Requirement is: for A2 (FY|F|V|D|Safety|3M). take the B column first value and search whether there is any match if not take second value in B column and repeat the process for all the B column values and if there is match print the B column value in Result. Does that make sense?

Try the below code & let me know if this is what you're looking for

Sub NotSureWhy()

Dim LastRow As Long, text1 As String, text2 As String, x As Long, y As Long
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

For x = 2 To LastRow
    text1 = Cells(x, 1).Value
    
    For y = 2 To LastRow
        text2 = Cells(y, 2)
            If "*" & text1 & "*" Like "*" & text2 & "*" Then
                Cells(x, 3).Value = text2
                Exit For
            Else
                Cells(x, 3).Value = "N/A"
            End If
    Next y
Next x

End Sub