Use “Find and Replace” to Search within Google Sheets
An easy way to search inside all sheets of a Google Sheets spreadsheet at once is to use the “Find and Replace” function. With this, you can search for a keyword in the current sheet, in a specific sheet, or all sheets of your spreadsheet.

On “Find and Replace” window, you have to click on “Find” box at the top and type your search text. After this click on “Search” drop-down menu and select “All sheets” to search in all sheets. Then, click “Find” at the bottom to start the search.

If Google Sheets finds a match in a sheet, it will open that sheet and highlight border of that field that contain your text match.

If there is no match found then it will display an error message.

To close this error message windows you need to click “Done”.
Use Google Drive to Search within Google Sheets
All your Google sheets are saved in Google Drive,this allows searching inside your spreadsheets.The only issue with this method is that it only shows entire spreadsheet not sheet that matches your search.
To start with this load the Google Drive site in a browser. On the main Google Drive page, click the “Search in Drive” box at the top. Type your search query, press Space, and then type this and hit Enter.

Google Drive will look for your typed query in your Google Sheets spreadsheets.
On the search results screen, it will list the spreadsheets that match your search. Double-click any spreadsheet here to open it in the Sheets editor.

And that’s how you search in all sheets without opening each one in Google Sheets.