HLOOKUP in Excel Formula

Question

This is my excel file:

In this Excel document, cell J2 needs to contain a formula that does the following: In the filled matrix, it would search for the letter C (column), and then it would give me the value. For instance, if J2 had the number 43,24 as its first value, I would drag the formula all the way to the end (the 14th row, where the matrix's filled values are fixed $$).

The Final output should be like this:

My problem is that I can't fix the filled matrix ($$) in my HLOOKUP/VLOOKUP.

Can someone please help me with this?

Answer 1

If you want to switch up the C and F, you need one INDEX with a MATCH for the row and a MATCH for the column, like this: 

=INDEX($B$2:$G$14,MATCH($I2,$A$2:$A$14,0),MATCH(J$1,$B$1:$G$1,0))

If you don't need to do a match on the date, then simply remove that part of the formula:

=INDEX($B$2:$G$14,,MATCH(J$1,$B$1:$G$1,0))

Or if you want to use HLOOKUP, then like this:

=HLOOKUP(J$1,$B$1:$G$14,ROW(),TRUE)