Using Sheets to search through the entire sheet and pull up results in a column

I have a bunch of sheets I use for personal work. They have a bunch of different car parts under different tabs in each sheet.

I created a master sheet that importrange’s from all of them and shows links to them in a master tab to jump to each tab separately. (doors, hoods, lightbulbs, door trims, roof racks, box of crown vic parts; its all over the place)

Is there a way for a user to search some text in a cell and have the column next to it populate it with results with matching words and ultimately, link to the tab and row that the item exists on?

ex: I have a sheet called "Search" and I type in A2 "crown vic". Then it will populate B2:B100 with any items found in the entire sheet with the words "crown vic" in it, and C2:C100 will have a link to the tabbed sheet that it is in.

Link to a test page to get my idea across:

https://docs.google.com/spreadsheets/d/1WrImPYHhhMOOZbf-AE2sNs82xL-u8wWOW4IFel6RGcY/edit#gid=999756632

I believe it would be better for me to use Javascript and HTML to create a web database for all this info instead of using sheets since its limited in some ways I want to use it. Ultimately I want it to be easier to find all the data by bringing up things with search.

120 thoughts on “Using Sheets to search through the entire sheet and pull up results in a column”