Vlookup for partial match

**scusack1** · 07-26-2018, 01:13 PM

I have two order number columns where the data is not a perfect match:

Example

Order 1 - Order 2
563926146_707 563926146
563926716_707 563926716
563927094_707 563927094
564008159_707 564008159
310516499 19310516499

How can I run a formula or vlookup to see what in order 2 column matches a partial in Order 1 or vice versa?

**Sam Capricci** · 07-26-2018, 01:16 PM

Look into the Fuzzy Lookup free add in from Microsoft.
https://www.microsoft.com/en-us/down....aspx?id=15011
it works well for inexact matches.

**José Augusto** · 07-26-2018, 01:20 PM

Or try this formula

Formula:

Please Login or Register  to view this content.

where A3:B7 have your data

**63falcondude** · 07-26-2018, 01:27 PM

Here's another alternative with order 1 data in A2:A6 and order 2 data in column C:

D2 (Order 1 match) =IFERROR(INDEX(A$2:A$6,MATCH("*"&C2&"*",A$2:A$6,0)),LOOKUP(1E+100,SEARCH(A$2:A$6,C2),A$2:A$6))

**Kamal Asharaf** · 07-26-2018, 01:34 PM

Dear User,

Please check if this solve the query

Order 1 Order 2 Find Order 2 in Order 1 Find Order 1 in Order 2
563926146_707 563926146 =IF(SEARCH(B3,A3)>0,"Order 2 Found in Order 1","Not found") =IFERROR(IF(SEARCHB(A3,B3)>0,"Order 1 Found in Order 2","Not found"),"Not found")
563926716_707 563926716 =IF(SEARCH(B4,A4)>0,"Order 2 Found in Order 1","Not found") =IFERROR(IF(SEARCHB(A4,B4)>0,"Order 1 Found in Order 2","Not found"),"Not found")
563927094_707 563927094 =IF(SEARCH(B5,A5)>0,"Order 2 Found in Order 1","Not found") =IFERROR(IF(SEARCHB(A5,B5)>0,"Order 1 Found in Order 2","Not found"),"Not found")
564008159_707 564008159 =IF(SEARCH(B6,A6)>0,"Order 2 Found in Order 1","Not found") =IFERROR(IF(SEARCHB(A6,B6)>0,"Order 1 Found in Order 2","Not found"),"Not found")
310516499 19310516499 =IFERROR(IF(SEARCH(B7,A7)>0,"Order 2 Found in Order 1","Not found"),"Not found") =IF(SEARCHB(A7,B7)>0,"Order 1 Found in Order 2","Not found")