Summary Tab from Inconsistent Data Structure and Location

Hello!

I am trying to create a summary tab in excel that will look up data in other tabs based on specified criteria, but the structure of those other tabs and location of data is not always consistent. I’ve used a combination of Vlookup, Index, and Match to achieve the desired result, but I was wondering if there was a more streamlined way to do it, or if anyone else had any other ideas. I know it would make the most sense to structure each tab in the same way, but that is not an option in this circumstance.



My current solution finds the location of the Manufacturer name row, matches the brand, and then pulls the price on to the summary tab. I feel like there is a simpler way to identify the row with Manufacturer name. Its currently “MATCH("Manufacturer Name ",INDIRECT("'"&$A7&"'!A:A"),0)&":"&MATCH("Manufacturer Name ",INDIRECT("'"&$A7&"'!A:A"),0)” Its basically the same formula twice to return a row reference of #:#.



I have attached a sample file for your reference. The formula in question is in yellow. Thank you in advance for any ideas or suggestions.

I might need more information on what to do with "data" on 2nd and 3rd sheet
like in 2nd sheet, rows below toyota, honda, lexus has 2 cells, the left one refers to where and the right one refers to where
same as the 3rd sheet

or maybe you can post the example instead of "data" in the cells