Vlookup all or parts of the information

[vivek.budhram]

Hi guys,

I need your help with a formula for looking up addresses which may seem the same but there might be missing a few items

e.g1 I want to look up "123 ABC street" in tab1 where the address is incomplete or not the exact format

i.e "123 ABC st" or "123 ABC" or "123 abc street"

by looking up the address, I then want to extract the "REF"

e.g2
as per the attached spreadsheet,
sheet1, where address is "767 LOUIS JOHN STREET", sheet2 also has the information as "767 Louis John Street"
I want to extract the REF="6503215147080" to sheet1

Please help

Thank you in advance
Regards
Vivekhttps://www.excelforum.com/images/icons/icon6.png

[JohnTopley]

In O2

=IFERROR(INDEX(Sheet2!$A$2:$A$100,MATCH($N2,Sheet2!$P$2:$P$6,0)),"")

Assumes exact match

Matching

729 Lobelia Street vs 729B Lobelia Street v 729 Lobelia St (if the latter existed)


is much more difficult as we need to define what we search on

[bebo021999]

I try to break string into 2 sub strings: 1st 3 digits and last strings after last 2 "spaces"
i.e:
767 LOUIS JOHN STREET ==>767 **** JOHN STREET
729B LOBELIA STREET ==> 729 **** LOBELIA STREET

In N2:

Please Login or Register  to view this content.