Similar Values in two columns

[yatindatta]

Hi Forum Members,

Seeking your help for an issue I am facing.

I have two columns having names of companies. I have to find the common names in both the columns and get in column C

Problem is name format is not same in both columns. For example if the company name is written as ABC International in column A it is written as ABC Intl or ABC in Column B.

Thank you

[FlameRetired]

Please try uploading a workbook sample. It will be easier to identify workable patterns if any exist.

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

be sure to desensitize the data

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[yatindatta]

Attaching Sample data

[FlameRetired]

If you wish to return the column A data that "matches" the column B data array enter this in cell C1 as in the attached and fill down.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

If you wish to do it the other way around as in the attached array enter this in G1 and fill down.

Please Login or Register  to view this content.

	A	B	C	D	E	F	G
1	ABC International	Walmart Inc.	Walmart		ABC International	Walmart Inc.	ABC Intl
2	KFC	Swiss Intl Airlines	Swiss Air		KFC	Swiss Intl Airlines	KFC International
3	Walmart	Pizza Hut Inc	Pizza Hut		Walmart	Pizza Hut Inc	Walmart Inc.
4	Mc Donalds	Mc D	Mc Donalds		Mc Donalds	Mc D	Mc D
5	Swiss Air	KFC International	KFC		Swiss Air	KFC International	Swiss Intl Airlines
6	Pizza Hut	Adobe International	Adobe Intl		Pizza Hut	Adobe International	Pizza Hut Inc
7	Adobe Intl	ABC Intl	ABC International		Adobe Intl	ABC Intl	Adobe International

[yatindatta]

Thank You Falemretired.

But I tried the same with actual data. I have 248 in column A and 400 in column B. So I modified your formula to and dragged down for all 248 entries.

=INDEX($A$1:$A$248,INDEX(MATCH(LEFT($B$1:$B$400&" ",4),LEFT($A$1:$A$248&" ",4),0),ROWS(C$1:C1)))

None of it worked. All am I getting is #Value

Thanks

[kersplash]

Did you use Ctrl+Shift+Enter when entering the first formula as per Post #4?

[azumi]

Try this, using array formulas



Azumi

[yatindatta]

Thank you. Worked