Count*Find - Need to find 2 different words matching the same cell in other sheet

[Leo92]

Hello ,maybe the title is not well described.

In the first sheet, in the column A, I have a list of companies + services hired in the same cell.

In the second sheet, I have in the column A, each company, and in the row 1 , all the services.

What I need to do in sheet 2,exactly from cell b2, fill each company with a tick or a cross if they have the service or not. I knew there was a way with count*find , but cant manage to do it.

helppppp
thank you !

 

[RobP]

Hi,

when you say in "Row 1 all the services" - do you mean all the services are in 1 cell, or do you mean the services are across the columns (B1 has a service type, C1, has a different service type ?, C3 etc..?

Unfortunately your copied sheet helps us not at all here ? If you can clarify what your second sheet looks like that would help probably a lot please ?

thanks
Rob

 

[Fluff]

Maybe

=IF(SUM((ISNUMBER(FIND($A2,Sheet1!$A$2:$A$100)))*(ISNUMBER(FIND(B$1,Sheet1!$A$2:$A$100)))),"X","")

 

[Leo92]

Hi,

when you say in "Row 1 all the services" - do you mean all the services are in 1 cell, or do you mean the services are across the columns (B1 has a service type, C1, has a different service type ?, C3 etc..?

Unfortunately your copied sheet helps us not at all here ? If you can clarify what your second sheet looks like that would help probably a lot please ?

thanks

Hi RobP!

Thanks for replying. I just uploaded 2 images to describe my issue. The image will the "ok" and "x" was filled manually, i have tons of data, so i need the formula. I hope this clarify and many thanks for reading!

 

[RobP]

Hi,

apologies for my delay - being out for a few days - but try this and see how you get on :

Book1
	A	B	C	D	E
1	Client	Blue Shoes	red Shoes	red hat	blue hat
2	Nike	OK	X	OK	X
3	Adidas	X	X	X	OK
4	Reebok	X	OK	X	OK
5
Sheet1

Cell Formulas
Range	Formula
B2:E4	B2	=IF(ISNA(MATCH(SUBSTITUTE($A2&B$1," ",""),SUBSTITUTE(Sheet2!$A$1:$A$10," ",""),0)),"X","OK")

of course edit the range references to suit your own data (I have range A1:a10 on second sheet, yours will be longer etc..