Formula to seek text in adjacent range and return first matching text

[AOB]

I'm trying to come up with a formula that takes an arbitrary number of string arguments, then checks for the presence of any of those strings in a range of adjacent cells (same row) and returns the first (i.e. left-to-right) match, if it finds one, or simply nothing if there are no such matches.

So for example :

A	B	C	D	E	F	G
	Apple	Orange	Lemon	Grapefruit	Kiwi	Banana
	Watermelon	Lemon	Strawberry	Grape	Raspberry	Apple
	Cherry	Kiwi	Banana	Mango	Lime	Apple

Let's say my string arguments are "Orange", "Banana" and "Mango"

I need a formula in column A that returns the first match in columns B through G for any of those strings :

A	B	C	D	E	F	G
Orange	Apple	Orange	Lemon	Grapefruit	Kiwi	Banana
	Watermelon	Lemon	Strawberry	Grape	Raspberry	Apple
Banana	Cherry	Kiwi	Banana	Mango	Lime	Apple

Row 1 contains both "Orange" (column C) and "Banana" (column G) so the formula returns "Orange" as it is the first match against any of the argument strings when looking left-to-right
Row 2 doesn't contain any of "Orange", "Banana" or "Mango" so the formula returns nothing (blank / zero length string)
Row 3 contains both "Banana" (column D) and "Mango" (column E) so the formula returns "Banana" as it is the first match against any of the argument strings when looking left-to-right

Thought I could do this quite easily with some kind of INDEX/MATCH or FIND/SEARCH but struggling to come up with one (the array of arguments is what's hurting me there, as opposed to a single atomic lookup)

(Apologies for the laboured example by the way, this was the simplest way I could explain what it is exactly I'm trying to do!... )

Thanks in advance!

 

[FormR]

Hi, for XL365, you could try:

Book10
	A	B	C	D	E	F	G
1	Orange	Apple	Orange	Lemon	Grapefruit	Kiwi	Banana
2		Watermelon	Lemon	Strawberry	Grape	Raspberry	Apple
3	Banana	Cherry	Kiwi	Banana	Mango	Lime	Apple
Sheet1

Cell Formulas
Range	Formula
A1:A3	A1	=LET(lookfor,"Orange,Banana,Mango",lookin,B1:H1,TAKE(FILTER(lookin,ISNUMBER(MATCH(lookin,TEXTSPLIT(lookfor,","),0)),""),,1))

 

[AOB]

Hi, for XL365, you could try:

Book10
	A	B	C	D	E	F	G
1	Orange	Apple	Orange	Lemon	Grapefruit	Kiwi	Banana
2		Watermelon	Lemon	Strawberry	Grape	Raspberry	Apple
3	Banana	Cherry	Kiwi	Banana	Mango	Lime	Apple
Sheet1

Cell Formulas
Range	Formula
A1:A3	A1	=LET(lookfor,"Orange,Banana,Mango",lookin,B1:H1,TAKE(FILTER(lookin,ISNUMBER(MATCH(lookin,TEXTSPLIT(lookfor,","),0)),""),,1))

Well that's my mind blown @FormR thanks for that!

Only one problem though - unfortunately, my string arguments can contain commas, so I can't define "lookfor" as a comma-delimited list of strings...

 

[FormR]

Hi, you can choose any delimiter and specify it here.

....MATCH(lookin,TEXTSPLIT(lookfor,","),0)),"...

 

[FormR]

you can choose any delimiter and specify it

for example..

Book10
	A	B	C	D	E	F	G
1	Orange	Apple	Orange	Lemon	Grapefruit	Kiwi	Banana
2		Watermelon	Lemon	Strawberry	Grape	Raspberry	Apple
3	Banana	Cherry	Kiwi	Banana	Mango	Lime	Apple
4	com,ma	Cherry	com,ma
Sheet1

Cell Formulas
Range	Formula
A1:A4	A1	=LET(lookfor,"Orange|Banana|Mango|com,ma",lookin,B1:H1,TAKE(FILTER(lookin,ISNUMBER(MATCH(lookin,TEXTSPLIT(lookfor,"|"),0)),""),,1))

 

[AOB]

for example..

Book10
	A	B	C	D	E	F	G
1	Orange	Apple	Orange	Lemon	Grapefruit	Kiwi	Banana
2		Watermelon	Lemon	Strawberry	Grape	Raspberry	Apple
3	Banana	Cherry	Kiwi	Banana	Mango	Lime	Apple
4	com,ma	Cherry	com,ma
Sheet1

Cell Formulas
Range	Formula
A1:A4	A1	=LET(lookfor,"Orange|Banana|Mango|com,ma",lookin,B1:H1,TAKE(FILTER(lookin,ISNUMBER(MATCH(lookin,TEXTSPLIT(lookfor,"|"),0)),""),,1))

Absolutely wonderful! This is so awesome, thank you - works a charm but will play around with this for other uses too.

(Should've looked further down the formula for the TEXTSPLIT function that would have made me realise this myself, apologies!... )