Need help with vlookup or index/match that finds a specific text value within a long string.

[rhaas128]

So I have a list of user ids on one worksheet. On another worksheet, I have a field called receivers that may contain a single user id or multiples that are separated by commas like so: AAA1234,BBB1234,CCC1234. I need to be able to search each cell in the receivers column and match the user id I provide on the first sheet to one of these cells, and then show data from another cell. I was attempting to use index match, but it isn't a direct match. I have setup a basic idea below with everything on one sheet.

A	B	C
UserID	Receivers	Return Value
AAA1234	DDD1234,CCC1234,EEE1234	1
BBB1234	AAA1234	2
CCC1234	BBB1234	3

<tbody>
</tbody>

So I want to search for each UserID in column A and find it in column B, then return the value from column C.

Thanks for all help in advance and sorr if this is confusing.

 

[AhoyNC]

This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B
1	Receivers	Return Value
2	DDD1234,CCC1234,EEE1234	1
3	AAA1234	2
4	BBB1234	3
5	*	*
6	*	*
7	*	*
8	UserID	Return Value
9	AAA1234	2
10	BBB1234	3
11	CCC1234	1
Sheet

 

[rhaas128]

OK, will give this a shot!

 

[AhoyNC]

Doesn't matter. Return value can be text number or combination of both.

 

[rhaas128]

Doesn't matter. Return value can be text number or combination of both.

This worked perfect. Thank you!

 

[AhoyNC]

You're welcome. Thanks for the feedback.