How to retrieve multiple possible matches in the same range

[muneshnaagar]

Hello guys,

I am new to the community, so please forgive me for any mistakes regarding my first post.

Now, I have a range of some store names with their respective addresses. I need to find all possible matches against every store to remove any sort of duplication due to typo or abbreviations used. I tried myself but could not find more than 1 match.

Came across a thread in the forum which is exactly what I am looking for but I am unable to completely use it. I will share it below.

[TABLE="width: 704"]
<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl66, width: 64"]A[/TD]
[TD="class: xl66, width: 64"]B[/TD]
[TD="class: xl66, width: 64"]C[/TD]
[TD="class: xl66, width: 64"]D[/TD]
[TD="class: xl66, width: 64"]E[/TD]
[TD="class: xl66, width: 64"]F[/TD]
[TD="class: xl66, width: 64"]G[/TD]
[TD="class: xl66, width: 64"]H[/TD]
[TD="class: xl66, width: 64"]I[/TD]
[TD="class: xl67, width: 64"]J[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]1[/TD]
[TD="class: xl63, width: 64"]Names[/TD]
[TD="class: xl63, width: 64"]Values[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl63, width: 64"]Lookups[/TD]
[TD="class: xl63, width: 64"]Matches[/TD]
[TD="class: xl63, width: 64"]Value 1[/TD]
[TD="class: xl63, width: 64"]Value 2[/TD]
[TD="class: xl63, width: 64"]Value 3[/TD]
[TD="class: xl63, width: 64"]Value 4[/TD]
[TD="class: xl69, width: 64"]Value 5[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]2[/TD]
[TD="class: xl63, width: 64"]CCvhtt[/TD]
[TD="class: xl64, width: 64"]7[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl63, width: 64"]AA[/TD]
[TD="class: xl64, width: 64"]5[/TD]
[TD="class: xl63, width: 64"]AAabjk[/TD]
[TD="class: xl63, width: 64"]AApakq[/TD]
[TD="class: xl63, width: 64"]AAsqtc[/TD]
[TD="class: xl63, width: 64"]AAhwpq[/TD]
[TD="class: xl69, width: 64"]AAwxlw[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]3[/TD]
[TD="class: xl63, width: 64"]BBmcyj[/TD]
[TD="class: xl64, width: 64"]33[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl63, width: 64"]AB[/TD]
[TD="class: xl64, width: 64"]4[/TD]
[TD="class: xl63, width: 64"]ABfehz[/TD]
[TD="class: xl63, width: 64"]ABoaxm[/TD]
[TD="class: xl63, width: 64"]ABlfaa[/TD]
[TD="class: xl63, width: 64"]ABszgv[/TD]
[TD="class: xl69, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]4[/TD]
[TD="class: xl63, width: 64"]ABszgv[/TD]
[TD="class: xl64, width: 64"]4[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl63, width: 64"]AC[/TD]
[TD="class: xl64, width: 64"]3[/TD]
[TD="class: xl63, width: 64"]ACdjjz[/TD]
[TD="class: xl63, width: 64"]ACriek[/TD]
[TD="class: xl63, width: 64"]ACuxrq[/TD]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl69, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]5[/TD]
[TD="class: xl63, width: 64"]BAvmfi[/TD]
[TD="class: xl64, width: 64"]41[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl63, width: 64"]BA[/TD]
[TD="class: xl64, width: 64"]5[/TD]
[TD="class: xl63, width: 64"]BAxcej[/TD]
[TD="class: xl63, width: 64"]BAqvkv[/TD]
[TD="class: xl63, width: 64"]BAjtit[/TD]
[TD="class: xl63, width: 64"]BAgren[/TD]
[TD="class: xl69, width: 64"]BAvmfi[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]6[/TD]
[TD="class: xl63, width: 64"]ABlfaa[/TD]
[TD="class: xl64, width: 64"]58[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl63, width: 64"]BB[/TD]
[TD="class: xl64, width: 64"]3[/TD]
[TD="class: xl63, width: 64"]BBptmf[/TD]
[TD="class: xl63, width: 64"]BBnasw[/TD]
[TD="class: xl63, width: 64"]BBmcyj[/TD]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl69, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]7[/TD]
[TD="class: xl63, width: 64"]ABoaxm[/TD]
[TD="class: xl64, width: 64"]2[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl63, width: 64"]BC[/TD]
[TD="class: xl64, width: 64"]0[/TD]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl69, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]8[/TD]
[TD="class: xl63, width: 64"]ACuxrq[/TD]
[TD="class: xl64, width: 64"]68[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl63, width: 64"]CA[/TD]
[TD="class: xl64, width: 64"]1[/TD]
[TD="class: xl63, width: 64"]CArchd[/TD]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl69, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]9[/TD]
[TD="class: xl63, width: 64"]ABfehz[/TD]
[TD="class: xl64, width: 64"]15[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl63, width: 64"]CB[/TD]
[TD="class: xl64, width: 64"]2[/TD]
[TD="class: xl63, width: 64"]CBwhpu[/TD]
[TD="class: xl63, width: 64"]CBkunk[/TD]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl69, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]10[/TD]
[TD="class: xl63, width: 64"]BAgren[/TD]
[TD="class: xl64, width: 64"]1[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl63, width: 64"]CC[/TD]
[TD="class: xl64, width: 64"]4[/TD]
[TD="class: xl63, width: 64"]CCfdwa[/TD]
[TD="class: xl63, width: 64"]CCsflq[/TD]
[TD="class: xl63, width: 64"]CCuqgm[/TD]
[TD="class: xl63, width: 64"]CCvhtt[/TD]
[TD="class: xl69, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]11[/TD]
[TD="class: xl63, width: 64"]CCuqgm[/TD]
[TD="class: xl64, width: 64"]53[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl70, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]12[/TD]
[TD="class: xl63, width: 64"]BAjtit[/TD]
[TD="class: xl64, width: 64"]49[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl70, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]13[/TD]
[TD="class: xl63, width: 64"]CArchd[/TD]
[TD="class: xl64, width: 64"]4[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl70, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]14[/TD]
[TD="class: xl63, width: 64"]CCsflq[/TD]
[TD="class: xl64, width: 64"]15[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl70, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]15[/TD]
[TD="class: xl63, width: 64"]ACriek[/TD]
[TD="class: xl64, width: 64"]68[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl70, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]16[/TD]
[TD="class: xl63, width: 64"]BAqvkv[/TD]
[TD="class: xl64, width: 64"]29[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl70, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]17[/TD]
[TD="class: xl63, width: 64"]ACdjjz[/TD]
[TD="class: xl64, width: 64"]22[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl70, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]18[/TD]
[TD="class: xl63, width: 64"]AAwxlw[/TD]
[TD="class: xl64, width: 64"]1[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl70, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]19[/TD]
[TD="class: xl63, width: 64"]BAxcej[/TD]
[TD="class: xl64, width: 64"]23[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl70, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]20[/TD]
[TD="class: xl63, width: 64"]CBkunk[/TD]
[TD="class: xl64, width: 64"]74[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl70, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]21[/TD]
[TD="class: xl63, width: 64"]AAhwpq[/TD]
[TD="class: xl64, width: 64"]35[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl70, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]22[/TD]
[TD="class: xl63, width: 64"]AAsqtc[/TD]
[TD="class: xl64, width: 64"]14[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl70, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]23[/TD]
[TD="class: xl63, width: 64"]CBwhpu[/TD]
[TD="class: xl64, width: 64"]66[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl70, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]24[/TD]
[TD="class: xl63, width: 64"]AApakq[/TD]
[TD="class: xl64, width: 64"]49[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl70, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]25[/TD]
[TD="class: xl63, width: 64"]CCfdwa[/TD]
[TD="class: xl64, width: 64"]48[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl70, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]26[/TD]
[TD="class: xl63, width: 64"]BBnasw[/TD]
[TD="class: xl64, width: 64"]2[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl70, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]27[/TD]
[TD="class: xl63, width: 64"]BBptmf[/TD]
[TD="class: xl64, width: 64"]67[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl70, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl71, width: 64"]28[/TD]
[TD="class: xl72, width: 64"]AAabjk[/TD]
[TD="class: xl73, width: 64"]20[/TD]
[TD="class: xl73, width: 64"] [/TD]
[TD="class: xl73, width: 64"] [/TD]
[TD="class: xl73, width: 64"] [/TD]
[TD="class: xl73, width: 64"] [/TD]
[TD="class: xl73, width: 64"] [/TD]
[TD="class: xl73, width: 64"] [/TD]
[TD="class: xl73, width: 64"] [/TD]
[TD="class: xl74, width: 64"] [/TD]
[/TR]
</tbody>[/TABLE]


Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH="width: 10px, align: center"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] , align: center"]E2[/TH]
[TD]{=SUM(IF(NOT(ISERROR(FIND(D2,$A$2:$A$28))),1))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] , align: center"]F2[/TH]
[TD]{=IF(COLUMN(A1)<=$E2,INDEX($A:$A,LARGE(IF(NOT(ISERROR(FIND($D2,$A$2:$A$28))),ROW($A$2:$A$28)),COLUMN(A1))),"")}[/TD]
[/TR]
</tbody>[/TABLE]


Now, I am able to get result in Value1(1st possible match) but not for next matched like Value2, Value3 (next possible matches for a lookup value) etc. Need help on this as to how to replicate formula in F2 to G2, H2, I2 etc.

Any help will be highly appreciated. Please elaborate as much as possible.

Regards
Munesh Naagar

 

[Eric W]

If you can get the first value, all you should need to do is copy the formula from F2 to G2, and so on.

 

[muneshnaagar]

Thanks Eric. Got the solution.