Excel formula that combines MATCH INDEX and OFFSET

Question

I'm experiencing problems using a feature of Excel.

I need the value of a cell on sheet A that is x-columns to the right of cell F2. The value of cell A1 determines the variable number X. The current value is 5.

=(OFFSET(sheetA!F2,0,sheetA!A1))

This formula works. However, I want to include this function in a MATCH and INDEX function that is located on another sheet (B).

I know that I can use the following formula to get the value of $F$2

INDEX(sheetA!F:F,MATCH(sheetB!C4,sheetA!A:A,0))

Combining them results in the following formula:

=INDEX((OFFSET(sheetA!F2,0,sheetA!A1)),MATCH(sheetB!C4,sheetA!A:A,0))

This formula generates a #REF!-value.

If I evaluate the formula, I see the following steps:

=INDEX((OFFSET(sheetA!$F$2,0,5)),MATCH(sheetB!C4,sheetA!A:A,0))
=INDEX((sheetA!$K$2),MATCH(sheetB!C4,sheetA!A:A,0))
=INDEX((sheetA!$K$2),MATCH("BTC",sheetA!A:A,0))
=#REF!

I want to match the index. How can I do this?

Answer 1

Try this:

=INDEX((OFFSET(SheetA!F:F,0,SheetA!A1)),MATCH(SheetB!C4,SheetA!A:A,0))

Syntax of INDEX is:

INDEX(array, row_num, [column_num])