Need an Excel array formula that searches multiple columns, and shows the row heading when found

[bakarken]

Hi guys - really appreciate any help you can offer (I feel close to finding it but I'm struggling, think I need an array formula that include Index, Row, Search)
Using Excel 2019

In the below example, I need the formula to be placed in cells B1:B3

The formula needs to SEARCH for all words/text strings in columns F:G
If it finds anything, it needs to show the word (heading) from column E, if it finds something next to it in F:G

500Jeff10	NEED FORMULA HERE			Accounts	Greg	Tom
BG Tom	NEED FORMULA HERE			Admin	Jeff
0000 Greg	NEED FORMULA HERE

So ideally, B1 would say Admin (because it found Jeff next to admin), B2 and B3 would say Accounts (because it found these names next to Accounts in either F or G)

I'm sure an Index/Search formula can do this - any help really appreciated!!

Bonus points if it can say 'Not found' if nothing is found in searches, or 'Multiple found' if multiple search results.

Thank you!!

 

[DanteAmor]

Try:

DANTE AMOR
	A	B	C	D	E	F	G
1	500Jeff10	Admin			Accounts	Greg	Tom
2	BG Tom	Accounts			System	Sue	Dante
3	0000 Greg	Accounts			Eng
4	form Dante xd	System			Arc
5					Admin	Jeff
AMOR

Cell Formulas
Range	Formula
B1:B4	B1	=INDEX($E$1:$E$6,MAX((ISNUMBER(SEARCH(IF($F$1:$G$6<>"",$F$1:$G$6),A1)))*(ROW($F$1:$G$6))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[DanteAmor]

Bonus points if it can say 'Not found' if nothing is found in searches

Try:

DANTE AMOR
	A	B	C	D	E	F	G
1	500Jeff10	Admin			Accounts	Greg	Tom
2	BG Tom	Accounts			System	Sue	Dante
3	0000 Greg	Accounts			Eng
4	form Dante xd	System			Arc
5	100abc200	Not found			Admin	Jeff
6
Hoja1

Cell Formulas
Range	Formula
B1:B5	B1	=IFERROR(INDEX($E$1:$E$6,MAX(IF(ISNUMBER(SEARCH(IF($F$1:$G$6<>"",$F$1:$G$6),A1)),ROW($F$1:$G$6),-1))),"Not found")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bakarken]

Try:

DANTE AMOR
	A	B	C	D	E	F	G
1	500Jeff10	Admin			Accounts	Greg	Tom
2	BG Tom	Accounts			System	Sue	Dante
3	0000 Greg	Accounts			Eng
4	form Dante xd	System			Arc
5					Admin	Jeff
AMOR

Cell Formulas
Range	Formula
B1:B4	B1	=INDEX($E$1:$E$6,MAX((ISNUMBER(SEARCH(IF($F$1:$G$6<>"",$F$1:$G$6),A1)))*(ROW($F$1:$G$6))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Dante thank you so much! This is brilliant!

One slight issue - where there are no 'matches' in the array search, it is still resulting in the uppermost cell in column E (E1 in this case, Accounts). Is there a way for it, instead of defaulting to E1, to just say 'not found' or something?

Also, if the name Jeff appears twice in columns F:G (say in both F3 and G5 for example), is there a way that it can say 'multiple found'?

 

[DanteAmor]

One slight issue - where there are no 'matches' ...
Also, if the name Jeff appears twice in columns F:G (say in both F3 and G5 for example), is there a way that it can say 'multiple found'?

Edit:

DANTE AMOR
	A	B	C	D	E	F	G
1	500Jeff10	Multiple			Accounts	Greg	Tom
2	BG Tom	Accounts			System	Sue	Dante
3	0000 Greg	Accounts			Eng
4	form Dante xd	System			Arc	Jeff
5	100abc200	Not found			Admin	Jeff
6	200 Sue Tom 300	Multiple
Hoja1

Cell Formulas
Range	Formula
B1:B6	B1	=IF(SUM(--ISNUMBER(SEARCH(IF($F$1:$G$6<>"",$F$1:$G$6),A1)))>1,"Multiple",IFERROR(INDEX($E$1:$E$6,MAX(IF(ISNUMBER(SEARCH(IF($F$1:$G$6<>"",$F$1:$G$6),A1)),ROW($F$1:$G$6),-1))),"Not found"))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bakarken]

Edit:

DANTE AMOR
	A	B	C	D	E	F	G
1	500Jeff10	Multiple			Accounts	Greg	Tom
2	BG Tom	Accounts			System	Sue	Dante
3	0000 Greg	Accounts			Eng
4	form Dante xd	System			Arc	Jeff
5	100abc200	Not found			Admin	Jeff
6	200 Sue Tom 300	Multiple
Hoja1

Cell Formulas
Range	Formula
B1:B6	B1	=IF(SUM(--ISNUMBER(SEARCH(IF($F$1:$G$6<>"",$F$1:$G$6),A1)))>1,"Multiple",IFERROR(INDEX($E$1:$E$6,MAX(IF(ISNUMBER(SEARCH(IF($F$1:$G$6<>"",$F$1:$G$6),A1)),ROW($F$1:$G$6),-1))),"Not found"))
Press CTRL+SHIFT+ENTER to enter array formulas.

Absolutely perfect, thank you so much Dante!!