PDA

View Full Version : [SOLVED] Looking for help to identify matches across multiple columns



minimacro
09-16-2017, 11:40 AM
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&Companies!$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

20368

Leith Ross
09-16-2017, 12:29 PM
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
09-16-2017, 01:33 PM
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
09-17-2017, 02:46 PM
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
09-21-2017, 02:35 AM
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
09-21-2017, 11:00 AM
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.