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.

	A	B	C	D	E	F	G	H	I	J
1	Names	Values		Lookups	Matches	Value 1	Value 2	Value 3	Value 4	Value 5
2	CCvhtt	7		AA	5	AAabjk	AApakq	AAsqtc	AAhwpq	AAwxlw
3	BBmcyj	33		AB	4	ABfehz	ABoaxm	ABlfaa	ABszgv
4	ABszgv	4		AC	3	ACdjjz	ACriek	ACuxrq
5	BAvmfi	41		BA	5	BAxcej	BAqvkv	BAjtit	BAgren	BAvmfi
6	ABlfaa	58		BB	3	BBptmf	BBnasw	BBmcyj
7	ABoaxm	2		BC	0
8	ACuxrq	68		CA	1	CArchd
9	ABfehz	15		CB	2	CBwhpu	CBkunk
10	BAgren	1		CC	4	CCfdwa	CCsflq	CCuqgm	CCvhtt
11	CCuqgm	53
12	BAjtit	49
13	CArchd	4
14	CCsflq	15
15	ACriek	68
16	BAqvkv	29
17	ACdjjz	22
18	AAwxlw	1
19	BAxcej	23
20	CBkunk	74
21	AAhwpq	35
22	AAsqtc	14
23	CBwhpu	66
24	AApakq	49
25	CCfdwa	48
26	BBnasw	2
27	BBptmf	67
28	AAabjk	20

<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>
</tbody>

Array Formulas

Cell	Formula
E2	{=SUM(IF(NOT(ISERROR(FIND(D2,$A$2:$A$28))),1))}
F2	{=IF(COLUMN(A1)<=$E2,INDEX($A:$A,LARGE(IF(NOT(ISERROR(FIND($D2,$A$2:$A$28))),ROW($A$2:$A$28)),COLUMN(A1))),"")}

<thead>
</thead><tbody>
</tbody>

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.