V-Lookup in a cell with more than one value present

[Theemeadelis]

I am in need of a formula which will search for a specific value in a column of cells that may contain more than one value in each cell.

e.g.

A	B	C	D	E	F
1	ROLE	RESULT		ROLE	RESULT
2	897	BONUS		563, 897	BONUS
3				167	#N/A
4				897	BONUS
5				637, 598, 897	BONUS
6				324, 874	#N/A
7				162	#N/A
8				897	BONUS
9				354	#N/A

I would be entering the formula in column F for it to check for every occurrence of the value "897" (B2), and for each occurrence of the B2 value "897" in column E, it would return the value from cell C2 "Bonus" in column E.

Is this possible with VLook-Up, or a different type of look-up formula?

 

[MountainFog]

How would this work if there was a role and result in B3:C3 that had a role of 563? Trying to figure out what you are looking for

 

[Theemeadelis]

How would this work if there was a role and result in B3:C3 that had a role of 563? Trying to figure out what you are looking for

A	B	C	D	E	F
1	ROLE	RESULT		ROLE	RESULT
2	897	BONUS		563, 897	BONUS, TRIPLE
3	563	TRIPLE		167	#N/A
4				897	BONUS
5				637, 598, 897	BONUS
6				324, 874	#N/A
7				162	#N/A
8				897	BONUS
9				354	#N/A

If there was a value in B3 of 563 with a corresponding result of TRIPLE in C3, ideally it would return the value as now showing in F2 - or something similar.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1	ROLE	RESULT		ROLE	RESULT
2	897	BONUS		563, 897	TRIPLE, BONUS
3	563	TRIPLE		167
4				897	BONUS
5				637, 598, 897	BONUS
6				324, 874
7				162
8				897	BONUS
9				354
Data

Cell Formulas
Range	Formula
E2:E9	E2	=TEXTJOIN(", ",,XLOOKUP(--TRIM(TEXTSPLIT(D2,",")),$A$2:$A$5,$B$2:$B$5,""))

 

[MountainFog]

@Fluff why do you put in the -- in front of trim? Does that convert it to a number value?

 

[Fluff]

Yes it does.

 

[Theemeadelis]

How about

Fluff.xlsm
	A	B	C	D	E
1	ROLE	RESULT		ROLE	RESULT
2	897	BONUS		563, 897	TRIPLE, BONUS
3	563	TRIPLE		167
4				897	BONUS
5				637, 598, 897	BONUS
6				324, 874
7				162
8				897	BONUS
9				354
Data

Cell Formulas
Range	Formula
E2:E9	E2	=TEXTJOIN(", ",,XLOOKUP(--TRIM(TEXTSPLIT(D2,",")),$A$2:$A$5,$B$2:$B$5,""))

I'm struggling to get this to work - with it returning a Value error

 

[MountainFog]

How are the values in A2:A3 formatted? are they text values or number values?

 

[Fluff]

What happens if you remove the double unary (ie --) ?

 

[Theemeadelis]

What happens if you remove the double unary (ie --) ?

If I remove the double unary, the formula returns a blank result:

As for formatting, column A and column D cells are formatted as Numbers: