Search prices in workbook searching by brand, year, hours and model of equipment

Good morning,
I have a workbook with difference worksheets for each brand and inside everyone of them I have some tables with model year, model hours, and prices by model and year (see file attached).
I have been rocking my brain for the past 2 days trying to come up with a solution to find prices as per Test worksheet, I've been trying to use the INDIRECT formula to bring sheet names but I'm unable to get it to work.
The only caviat is that it has to be done with formulas, without using VBA or macros. Is it possible? bear in mind that the original workbook has like 50 brands, a colleague suggested using one big table but the prices get updated every month or so, so I would have to still find a way to update all the prices in all the worksheets.
Thank you very much in advanced.

Can you explain a little bit more what exactly you're asking for?

Hello,

In the Test sheet, I want to insert the Model for example: Brand1Model4, the year, the hours and get the price. In the Test sheet I have also inserted various examples, I would only need to get the price (cell C6). The output message would be a nice addition. In short, and sorry for not explaining myself better before, I would like to insert data in cells C2, C3 and C4 and get the output price in cell C6.

Yes, the problem is that I have like 50 brands (the brands 1 to 4 are just an example) and each of them get updated by different departments (I'm thinking manually) and get all reported in this big workbook.
How would you present the table that you're speaking of?

Just columns as I said above year, hours, brandmodel and price

you also need to confirm your version of excel as newer versions of excel offer other wsy to solutions (such as powerquery and filtering) I am no powerquery expert, but some on this forum are

You also need to explain your error messages Is it between the value in your table and the value below. OR just above the highest value

Are you matching year hours and model and returning the value.
If this value is above the value in your table "hour exceed value in table check the price in year before
what if there is no year before and do all hours decrease
the table would be like the below with all brands and models in the same table, some of the blank rows might be able to be deletes

Year hours brand model price
2022 1000 Brand1Model1 910,000
2021 2000 Brand1Model1 812,000
2020 3000 Brand1Model1 728,000
2019 4000 Brand1Model1 658,000
2018 5000 Brand1Model1 588,000
2017 6000 Brand1Model1 518,000
2016 7000 Brand1Model1 420,000
2015 8000 Brand1Model1 350,000
2014 9000 Brand1Model1 294,000
2013 10000 Brand1Model1 266,000
2012 11000 Brand1Model1 238,000
2011 12000 Brand1Model1 210,000
2010 13000 Brand1Model1 196,000
2009 14000 Brand1Model1 182,000
2008 15000 Brand1Model1 168,000
2007 16000 Brand1Model1
2006 17000 Brand1Model1
2005 18000 Brand1Model1
2004 19000 Brand1Model1
2003 20000 Brand1Model1
2002 21000 Brand1Model1
2001 22000 Brand1Model1
2000 23000 Brand1Model1
2022 1000 Brand1Model2
2021 2000 Brand1Model2
2020 3000 Brand1Model2
2019 4000 Brand1Model2
2018 5000 Brand1Model2
2017 6000 Brand1Model2
2016 7000 Brand1Model2
2015 8000 Brand1Model2
2014 9000 Brand1Model2
2013 10000 Brand1Model2
2012 11000 Brand1Model2
2011 12000 Brand1Model2
2010 13000 Brand1Model2
2009 14000 Brand1Model2 172,900
2008 15000 Brand1Model2 159,600
2007 16000 Brand1Model2 146,300
2006 17000 Brand1Model2 133,000
2005 18000 Brand1Model2 119,700
2004 19000 Brand1Model2 106,400
2003 20000 Brand1Model2 93,100
2002 21000 Brand1Model2 79,800
2001 22000 Brand1Model2 66,500

Literally one table columns of Year, hours BrandModel and price

Brand model could be 2 columns if needed. Some cleaver people may be able to rearrange the data into a table like this with power query

Also are you still on office 2010 as in 365 filtering is an option

why is 3800 happy in column 1 will it always be 1 year before