Search through multiple tabs and pull results

Question

Test Sheet for my use case: https://docs.google.com/spreadsheets/d/1_gcyygG8IhVc-4mXf8n09IFG5ZIYuK_UxWxPLQ5av-k/edit?usp=sharing

I've had some input from another user on the site helping me search through one tab of data in my sheet and pull results. Now I'm wondering if its possible to do that but also look through multiple tabs.

In the "Partial Answer" tab, it shows how it would work with a single tab ('Car Parts') but it broke when U transferred it over to this new sheet. Besides the point, I would like the "Search Tab" tab be able to look for a keyword in 'A2' and return results in 'B2:B' from all my tabs (Hoods, Bumpers, and Doors) as well as link to the row the data was pulled from in the query in B2:

=iferror(query('Car Parts'!A2:A,"select A where upper(A) contains '"&UPPER(A2)&"' ",0), "not found")

Answer 1

Instead of a single range

'Car Parts'!A2:A

use an array of ranges (break lines included for readability, but you could write this in a single line)

{
'Hoods'!A2:A;
'Bumbpers'!A2:A;
'Doors'!A2:A
}

and

instead of A in the select statement use Col1