Look up formula help

[STEVENS3010]

Hi all,

I'm trying to determine which formula would be best to use to carry out the following task. Any support that could be offered would be greatly appreciated.

I have a username in cell A1 on sheet 1, and I am trying to create a formula, to look up the value in cell 'A1' in a range of data on sheet 2, specifically in column E. In each row in column E there could be more than one username in each cell, so I need a partial match formula, but then I also need the formula to use the matched username to then count another value in column I.

So for example, I need to look up 'username 1' in column E on sheet 2, and then count how many times the value 'red' appears in column I. Is this possible?

Thanks

 

[etaf]

how about
=COUNTIFS(F2:F4,"*"&"user2"&"*",G2:G4,"red")
but change the sheet - i simplified

Book15
	A	B	C	D	E	F	G
1	1
2						user1,user2,user3
3						user1,user2,user4	red
4						user1,user2,user5
Sheet1

Cell Formulas
Range	Formula
A1	A1	=COUNTIFS(F2:F4,"*"&"user2"&"*",G2:G4,"red")

 

[STEVENS3010]

Thanks so much.

 

[etaf]

you are welcome