Search for a Name or Phone number among multiple sheets

[Ramadan]

I have a workbook with 9 sheets subject to be increased in the future, All the sheets have the same table design, format and the same starting row number
in cell "B2" in each sheet there is a code as a name for the contents like "NH01,NH02,NH03, etc...

What I need is to be make a formula or something in any sheet of them that make me able to search in theses sheets for any name or phone number (inserted as text) between columns ("D:M") and if there is a match to let me know the sheet code in "B2" to know the sheet name that has the match and the row number in the table or even the row number in the excel sheet
for your kind information there might be more than one result - is that possible ?

Thank you in advance for your help

 

[Ramadan]

Here is a minisheet

copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2		NH04
3			0	Hanady Hoot
4				TRUE
5			Westridge
6
7		S	Unit No.	Owner Name	Phone No.	Auth. Date	Authorized Personnel
8							Name	Phone No.	Name	Phone No.	Name	Phone No.	Name	Phone No.
9		Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13
10		1	RT08 - 75	Mona Rashid	01005076444	21.03.24	Monazid Aly	01004003553	Gamila Sodre	01009962552	Amira shkory	01062162153	محمد وحيد مأمون	01000904840
11		2	RT08 - 16	Sawsan Edress		12.11.21	Faten Kola		Nadeen Nor
12		3	RT08 - 95	Mamdouh taher		28.11.21
13		4	LS WF 25R	Nabila Samy		14.05.23
14		5	RW08 - 113	Nadia Aziz		08.05.22	Salma Hatem		Jessica Hadde
15		6	RW09 - 119	Entsar Rod		02.01.22
16		7	RW09A - 78	Mai Helmy	01001722886	37/1	Ahmed Bassam	01222195601	Radwa Samy	0100380572
17		8	W12 - 22	Lolo Abode		03.09.23
18		9	W15 - 22	Emy Samy		05.01.22	Marina Medhat		Dalia Hussein
19		10	W18 - 33	Sherin Wasif		03.09.23
20		11	W21 - 23	Korashi taha
West

Cell Formulas
Range	Formula
C3	C3	=COUNTIF(West[[Column6]:[Column12]],"*"&D3&"*")
D4	D4	=IFERROR(VLOOKUP($D$4,Carnell[#All],'Carnell'!$B$2),"No Match")=IFERROR(VLOOKUP($D$4,Ivory[#All],Ivory!$B$2),"No Match")
B10:B20	B10	=IF([@Column2]<>"",ROW()-ROW($B$9),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B10:T71	Expression	=SUMPRODUCT(('Stop Work'!$D$8:$D500=$C10)*('Stop Work'!$K$8:$K500="Open")*('Stop Work'!$C$8:$C500=$B$2)*($T10=""))	text	NO
C10:C365	Expression	=AND(ISBLANK($P10),$R10<>"",$T10="")	text	NO
C10:C365	Expression	=$T10<>""	text	NO
C10:C365	Expression	=AND($D$3<>"",ISNUMBER(SEARCH($D$3,$G10&$I10&$K10&$M10)))	text	NO
C10:C365	Expression	=(AND($P10<>"",$E10<>"",$R10="",$T10=""))	text	NO
C10:C365	Expression	=(AND($P10<>"",$E10="",$R10="",$T10=""))	text	NO
C10:C365	Expression	=AND(ISBLANK($P10),ISBLANK($R10),ISBLANK($T10))	text	NO
C10:C365	Expression	=(AND($P10<>"",$R10<>"",($T10="")))	text	NO
B10:T365	Expression	=AND(ISBLANK($P10),$R10<>"",$T10="")	text	NO
B10:T365	Expression	=$T10<>""	text	NO
B10:T365	Expression	=AND($D$3<>"",ISNUMBER(SEARCH($D$3,$G10&$I10&$K10&$M10)))	text	NO
B10:T365	Expression	=(AND($P10<>"",$E10<>"",$R10="",$T10=""))	text	NO
B10:T365	Expression	=(AND($P10<>"",$E10="",$R10="",$T10=""))	text	NO
B10:T365	Expression	=AND(ISBLANK($P10),ISBLANK($R10),ISBLANK($T10))	text	NO
B10:T365	Expression	=(AND($P10<>"",$R10<>"",($T10="")))	text	NO
H12	Expression	=SUMPRODUCT(('Stop Work'!$D$8:$D500=$C10)*('Stop Work'!$K$8:$K500="Open")*('Stop Work'!$C$8:$C500=$B$2)*($T10=""))	text	NO
B184:T359,B183:J183,M183:T183,B11:T182	Expression	=AND(ISBLANK($P11),$R11<>"",$T11="")	text	NO
B184:T359,B183:J183,M183:T183,B11:T182	Expression	=$T11<>""	text	NO
B184:T359,B183:J183,M183:T183,B11:T182	Expression	=AND($D$3<>"",ISNUMBER(SEARCH($D$3,$G11&$I11&$K11&$M11)))	text	NO
B184:T359,B183:J183,M183:T183,B11:T182	Expression	=(AND($P11<>"",$E11<>"",$R11="",$T11=""))	text	NO
B184:T359,B183:J183,M183:T183,B11:T182	Expression	=(AND($P11<>"",$E11="",$R11="",$T11=""))	text	NO
B184:T359,B183:J183,M183:T183,B11:T182	Expression	=AND(ISBLANK($P11),ISBLANK($R11),ISBLANK($T11))	text	NO
B184:T359,B183:J183,M183:T183,B11:T182	Expression	=(AND($P11<>"",$R11<>"",($T11="")))	text	NO
C2	Expression	=D2=""	text	NO
C3	Expression	=D3=""	text	NO

 

[alansidman]

Using Power Query, you can review my solution at the attached. Not exactly what you were looking for but you may find this workable.

PQ Lookups.xlsx | Powered by Box

app.box.com