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]

The tables rows serial number is in "B"

 

[alansidman]

It is possible. But, in order to give any type of assistance it will be necessary to know where the Name and Phone Number are located. ie. The column (field) name. Additioanly, maybe 8-10 rows of sample data to understand what your sheets look like. Please use XL2BB for your example.

 

[Ramadan]

It is possible. But, in order to give any type of assistance it will be necessary to know where the Name and Phone Number are located. ie. The column (field) name. Additioanly, maybe 8-10 rows of sample data to understand what your sheets look like. Please use XL2BB for your example.

@alansidman

Thank you Screenshot Attached

 

[alansidman]

Firstly, I cannot manipulate data in a picture. XL2BB allows us to work your data without having to create a spreadsheet and reenter the data.
Secondly, if I were to do this, i would restructure the range/table so that it was normalized. With normalized data you can do analysis without having complex issues. If you don't know about normalized tables then read the following.

Database normalization description - Microsoft 365 Apps

Describe the method to normalize the database and gives several alternatives to normalize forms. You need to master the database principles to understand them or you can follow the steps listed in the article.

learn.microsoft.com

Once your data is normalized then you can do lookups with ease. To get your current data normalized, you can use Power Query to unpivot the data and then you can do your lookups easily.

 

[alansidman]

Here is another explanation of Data Normalization.

Normalization in DBMS - 1NF, 2NF, 3NF, BCNF, 4NF and 5NF | Studytonight

Learn Normalization in DBMS and how to implement 1NF in DBMS, 2NF in DBMS, BCNF in DBMS, 4NF in DBMS, and 5NF in DBMS with examples.

www.studytonight.com

 

[Ramadan]

Here is another explanation of Data Normalization.

Normalization in DBMS - 1NF, 2NF, 3NF, BCNF, 4NF and 5NF | Studytonight

Learn Normalization in DBMS and how to implement 1NF in DBMS, 2NF in DBMS, BCNF in DBMS, 4NF in DBMS, and 5NF in DBMS with examples.

www.studytonight.com

Thanks anyway