Find data in whole sheet and return the value under specific header

Question

I'm not really good with excel but I've already searched and tried lots of formulas but still can't get this done. Problem is I need a cell in Sheet1...

...to return the value of the data in Sheet2 based on the column header name. Since the values might change its column number i need to search for the header name itself to return the value. As shown on the screenshot below. the lookup value will be the number and country and return the value under price that corresponds to the row.

Answer 1

Use a MATCH function across the top row to return the column number.

=index(A:Z, aggregate(15, 6, row(1:999)/(A1:A999=9)*(B1:B999="US"), 1), match("price", 1:1, 0))

You may have to lock some row and column range references down as absolute with $ if you plan to copy or fill the formula to other locations.

The AGGREGATE¹ function provides the two column match for 9 and US.

¹ _{The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.}