Looking for help to identify matches across multiple columns

[minimacro]

Hi,
Im a newbie with excel, but very keen to learn more so please bear with me.



I have an excel workbook with around 4000 rows of data and two sheets (simplified version uploaded). I have 1 main sheet (Sheet1 - Names) in which I want to cross reference delimited company names with (Sheet2 - Companies).



I need to search a string in 3 columns of key words then in the 4th column return the result (search return either name ID or "no match")
i.e.

Sheet 1 - Names

Name1	Name2	Name3
TEST	CASE	HERE
THE	WHITE	COMPANY
RALPH	LAUREN	LTD

Search row 2 TEST CASE HERE In Sheet 2 - companies, and return the company ID 1 to show a match.

Company_ID	COMPANY1	COMPANY2	COMPANY3
1	TEST	CASE	HERE
2	THE	WHITE	COMPANY
3	RALPH	LAUREN	LTD
4	GENERAL	ELECTRIC	LTD

In column E of the Names Sheet I have set up a Index Match = =IFERROR(INDEX($A:$A,MATCH(B2&C2&D2,Companies!$A:$A=B2&Companies!$B:$B=C2&C ompanies!$C:$C=D2,0),1),"no match")


However there is a problem and the search does not return the ID I'm expecting, instead it is saying there is no match - what am I doing wrong?


I also tried to turn this into a VBA - to again no success. I'm really struggling and would be very grateful for some direction.




Thank you,


MiniMacro

companies_v3.0.xlsx

[Leith Ross]

Hello minimacro,

Copy this formula to "D2" on "Names" and drag it down.

=IF(COUNTIF($A2:$C2, "<>"),IF(SUMPRODUCT(--(Names!$A2=Companies!$B2),--(Names!$B2=Companies!$C2),--(Names!$C2=Companies!$D2)),Companies!$A2,"No Match"),"")

[minimacro]

Thanks so much for your prompt reply!! Sorry I didn't mention - the names in the Company sheet are in a random order so
sheet 1 (Names) items appear as:

Name1	Name2	Name3
TEST	CASE	HERE
THE	WHITE	COMPANY
RALPH	LAUREN	LTD
WALMART	PHARMACY	LTD

however in sheet two (companies):

COMPANY1	COMPANY2	COMPANY3
RALPH	LAUREN	LTD
TEST	CASE	HERE
UBER	LTD
THE	WHITE	COMPANY

Is there a way to search the columns?

[Leith Ross]

Hello Minimacro.

Here is the revised formula. This will work with the tables being mismatched.

 =IF(COUNTA(Names!$A2:$C2)=0,"",IFERROR(INDEX(Companies!$A$2:$A$16,MATCH(Names!$A2&Names!$B2&Names!$C2,Companies!$B$2:$B$16&Companies!$C$2:$C$16&Companies!$D$2:$D$16,0)),"No Match"))

This is an Array Formula. You must enter the formula using the keys Control+Shift+Enter and then drag it down. Currently, the last row is set to row 16 on the "Companies" worksheet. You can increase the the last row as need. Be sure to prefix it with a dollar sign $.

[minimacro]

Hello Minimacro.

Here is the revised formula. This will work with the tables being mismatched.

 =IF(COUNTA(Names!$A2:$C2)=0,"",IFERROR(INDEX(Companies!$A$2:$A$16,MATCH(Names!$A2&Names!$B2&Names!$C2,Companies!$B$2:$B$16&Companies!$C$2:$C$16&Companies!$D$2:$D$16,0)),"No Match"))

This is an Array Formula. You must enter the formula using the keys Control+Shift+Enter and then drag it down. Currently, the last row is set to row 16 on the "Companies" worksheet. You can increase the the last row as need. Be sure to prefix it with a dollar sign $.

Morning Leith,

Sorry for the late reply, I was using a Mac and I couldn't get it to work but had great success with this once I used it on Windows Microsoft Office!

Thanks very much for all your help.

[Leith Ross]

Hello Minimacro,

I am surprised this didn't work on the Mac. As long as you were able to use it successfully, that is what matters. You're welcome.