Formula: Cell Contains Every Value in Range

[ghrain22]

Hello,

I'm trying to create a formula that bounces a cell string ("Lookup") off a list of rows. If all cells within a row of "terms" is found (anywhere in the entire list of terms), then I want to return the value of "TRUE". It would also be awesome if I would be able to return a concatenation of the found row (I'm on 2016, so I have access to the concat and jointext functions).

Example:

Lookup	Found
Excel Formulas Can Be Really Easy	True	1
But I Cannot Figure This One Out	False
I Would Really Appreciate Any Help	True	3
Thank You For Your Time	False

<tbody>
</tbody>

row	Term 1	Term 2	Term 3
1	Excel	Easy
2	Thank	Time	Apple
3	Appreciate	Help	Would

<tbody>
</tbody>

Thanks!

 

[CyrusTheVirus]

Hi,

Perhaps try testing the below. Maybe this could work for you?

Enter the formula in C2 using Ctrl+Shift+Enter, not just enter.

Note: Please ignore the asterisks, they were cause by the copy/pasting of my spreadsheet into this reply.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Lookup	Found	In Row	Concatenation	*	row	Term 1	Term 2	Term 3	Helper
2	Excel Formulas Can Be Really Easy	TRUE	1	Excel Easy	*	1	Excel	Easy	*	Excel Easy
3	But I Cannot Figure This One Out	FALSE	*	*	*	2	Thank	Time	Apple	Thank Time Apple
4	I Would Really Appreciate Any Help	TRUE	3	Appreciate Help Would	*	3	Appreciate	Help	Would	Appreciate Help Would
5	Thank You For Your Time	FALSE	*	*	*	*	*	*	*	*
Sheet4