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]

Try this...

=TEXTJOIN(", ",,XLOOKUP(--TRIM(TEXTSPLIT(D2,",")),--TRIM($A$2:$A$5),$B$2:$B$5,""))

For experimentation sake... try copying and pasting column A to a blank column using paste special: values, add, and then paste them back in column a

 

[Theemeadelis]

Try this...

=TEXTJOIN(", ",,XLOOKUP(--TRIM(TEXTSPLIT(D2,",")),--TRIM($A$2:$A$5),$B$2:$B$5,""))

For experimentation sake... try copying and pasting column A to a blank column using paste special: values, add, and then paste them back in column a

That's worked perfectly this time, thank you for your perseverance and help :D

 

[MountainFog]

Happy to help. Fluff's formula should have worked. If my formula worked, it is because you have column A values stored as text.