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
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