CODEWITHSUNDEEP
excel
HJA24
HJA24

Reputation: 412

Excel formula that combines MATCH, INDEX and OFFSET

I am having trouble with an Excel-function.

On sheet A I want to get the value of a cell that is located x-columns to the right of cell F2. X is a variable number and is determined by the value of cell A1. Currently, the value is 5.

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

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

I know that I can use the following formula to get 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!

Why do I want to use MATCH and INDEX? Because while the values on sheet A are "fixed", the values of sheetB!C4 are floating/variable. Therefore, I need to locate the correct row first. The correct column can be done with the offset-part.

Thank you for your help.

Upvotes: 1

Views: 22238

Answers (1)

Mrig
Mrig

Reputation: 11702

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])

where, array is range of cells. When you use =INDEX((OFFSET(sheetA!F2,0,sheetA!A1)),MATCH(sheetB!C4,sheetA!A:A,0)), (OFFSET(sheetA!F2,0,sheetA!A1)) returns sheetA!$K$2 which is a cell not a range.

Upvotes: 2

Related Questions