CODEWITHSUNDEEP
stringexcelvlookup
user3095929
user3095929

Reputation: 11

Excel vlokup with partial string

I have 2 excel sheets, sheet A

Column A | Column B
12ABC      Value1
14AZC      Value2
44AXC      Value3
73XBC      Value4

and second sheet

Column A | Column B
BC         Value5
14AZC      Value6
44A        Value7

I need to make vlookup on Sheet1 with data from Sheet2.

The problem is that I need to use only partial string from Sheet2, as there may be more than 1 possible way to match both values. In result, I would need result:

Column A | Column B | Column C
12ABC      Value1     Value 5
73XBC      Value4     Value 5

I tried with, but I need it the other way around

=IF(NOT(ISBLANK(A2)); VLOOKUP(A2 & "*"; Sheet2!$A$1:$B$40;2;FALSE))

Upvotes: 0

Views: 113

Answers (1)

user3514930
user3514930

Reputation: 1717

If you want to use VBA it's very simple:

Public Function FindOcc(Base As Range, Serc As Range) As String
    For Each x In Serc
        If InStr(Base.Value, x.Value) > 0 Then
            FindOcc = x.Offset(0, 1).Value
            Exit Function
        End If
    Next
End Function

Otherwise you can use the a Hidden Column for each data in the sheet2. Following the scheme:

enter image description here

Use the formulas:

E3 -> =F3&G3&H3
F3 -> =IFERROR(IF(FIND($A$10;A3);$B$10;"");"")
G3 -> =IFERROR(IF(FIND($A$11;A3);$B$11;"");"")
H3 -> =IFERROR(IF(FIND($A$12;A3);$B$12;"");"")
...

and autocomplete...
In the column E you have the result...

Upvotes: 1

Related Questions