How to get partial match result between range of cells and a column of values

Question

I have a workbook with two sheets, "RO" and "LookUp"

In column K on Sheet "RO" there is a list of addresses and in Column E of "LookUp" there is a list of towns.

On Sheet "RO" column Q i am trying to extract the town name that matches a town on the list in the "lookUp" sheet.

I've tried variations of partial Vlookup and index and match functions but they obviously haven't worked because they are backwards (looking up the full address in the towns list rather than the other way around)

=VLOOKUP("*"&J2&"",'Look up Data'!E:E,1,0)

I tried doing an if statement using like (i knew it wouldnt work but it might give an idea of what i am trying do) see below.

Sub ROITown()

    Dim lRow As Long
    Dim iCntr As Long
    Set ws = Worksheets("RO")
    Set ls = Worksheets("LookUp")
    lRow = ws.Cells(Rows.Count, 5).End(xlUp).Row
    For iCntr = lRow To 2 Step -1
        If ws.Cells(iCntr, 10).Value Like ls.Cells(iCntr, 5).Value Then
            ws.Cells(iCntr, 17).Value = ls.Cells(iCntr, 5).Value
        End If
    Next iCntr
End Sub

these are some examples of my expected output

Column K "RO"
THE MEADOW AVENUE DEMESNE NAAS
BELFRY PARK DUNDALK CO. LOUTH
KESTREL PUB CO. LOUTH
AVONDALE CREST DUNBOYNE CO. MEATH
KNIGHTSBROOK STREET TRIM CO. MEATH
TAYLOR GREEN AVENUE BALBRIGGAN CO. DUBLIN

Column E "LookUp"
Athy
Balbriggan
Baldoyle
Citywest
Clane
Drogheda
Dun Laoghaire
Dunboyne
Dundalk
Dungarvan
Monasterevin
Mullingar
Naas
Navan
Tramore
Trim
Tuam

Expected Results in Column Q "RO"
Naas
Dundalk

Dunboyne
Trim
Balbriggan

As you can see, for the solution I need it to not be case sensitive and also where it doesnt find a matching town in the list it should just leave the cell blank.

Any advice even basic is welcome, even just point me in the right direction or let me know what I should search to find a solution.

Thanks in advance :)

CDP1802 · Accepted Answer · 2021-11-12 17:19:40Z

2

You need 2 nested loops to iterate the sheets independently.

Option Explicit

Sub ROITown()

    Dim s As String, arTown, LastRow As Long
    Dim r As Long, i As Long, n As Long
    
    With Worksheets("LookUp") ' towns
         LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
         arTown = .Range("E2:E" & LastRow)
    End With
    
    With Worksheets("RO") ' address
        LastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
        For r = 2 To LastRow
            For i = 1 To UBound(arTown)
                s = Trim(arTown(i, 1))
                If InStr(1, .Cells(r, "K"), s, vbTextCompare) Then
                    .Cells(r, "Q") = s
                    n = n + 1
                    Exit For
                End If
            Next
        Next r
    End With
    MsgBox n & " rows updated", vbInformation
    
End Sub

answered Nov 12, 2021 at 17:19

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rkayy Over a year ago

You my friend are a superstar!!! this works perfect thank you so much!!!!

Ron Rosenfeld · Accepted Answer · 2021-11-12 18:00:25Z

1

You can do this with a formula.

I Name'd the tables and am using structured references, but you can change that to regular addressing if you prefer:

This formula should work in any version at least since 2010

=IFERROR(INDEX(Lookup,AGGREGATE(15,6,1/(ISNUMBER(SEARCH(Lookup[Lookup],TRANSPOSE(LongName[Towns]))))*ROW(Lookup)-ROW(Lookup[#Headers]),ROW(INDEX($A:$A,1):INDEX($A:$A,ROWS(Lookup))))),"")

answered Nov 12, 2021 at 18:00

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

1 Comment

Rkayy Over a year ago

Thank you for your help here :), i used the VBA solution provided but this worked equally well, greatly appreciated :)

Black cat · Accepted Answer · 2025-09-04 07:45:59Z

Solutions with only formulas:

Addresses range: A59:A64 or as table the table name: Table6 and column name Search in
Towns range: G59:G75 or as table the table name: Table7 and column name Sought

To avoid the below mentioned issue use formula with REGEXEXTRACT

=BYROW(Table6[Search in],LAMBDA(x,IFERROR(REGEXEXTRACT(x,"\b("&TEXTJOIN("|",,"("&Table7[Sought]&")")&")\b",,1),"")))

If it doesn't count

With LAMBDA function:

=BYROW(Table6[Search in],LAMBDA(x,IFERROR(TOCOL(IF(SEARCH(Table7[Sought],x)>0,Table7[Sought],""),2),"")))

With ranges

All formulas need to drag down

Formula1 in B59: (All versions) =IFERROR(INDEX(Table7[Sought],MATCH(0,IFERROR(SEARCH(Table7[Sought],A59),""),-1)),"")

Formula2 in C59:
=TEXTJOIN("",TRUE,IF(IFERROR(SEARCH(G$35:G$51,A59),0)>0,G$35:G$51,""))

Formula3 in D59:
=IFERROR(TOCOL(IF(SEARCH(G$35:G$51,A59)>0,G$35:G$51,""),2),"")

Formula4 in E59: (All versions)
=IFERROR(INDEX(G$35:G$51,MATCH(0,IFERROR(SEARCH(G$35:G$51,A59),""),-1)),"")

SIDENOTE: Fake results can be returned if the address contains a word with partial match of a town.

Collectives™ on Stack Overflow

How to get partial match result between range of cells and a column of values

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related