Excel formula that combines MATCH INDEX and OFFSET

0 votes

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?

Nov 7, 2022 in Others by Kithuzzz
• 28,700 points
170 views

1 answer to this question.

0 votes

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])
answered Nov 7, 2022 by narikkadan
• 53,160 points

Related Questions In Others

0 votes
1 answer

Excel formula to check date within this week and last week

Assuming the first date in A2 try this formula ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
• 53,160 points
175 views
0 votes
1 answer

Excel Formula Query on Summing Annual Leave Days and Half Days

Try Something like =SUM(COUNTIF($A$1:$A$8,"A"),COUNTIF($A$1:$A$8,"HA")/2) - total count ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 53,160 points
213 views
0 votes
1 answer

INDEX formula in Excel, Top 10, repeats previous value

Try this formula in cell W4: =IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX($I$2:$I$26,MATCH(V4,$R$2:$R$26,0))) The calculation ...READ MORE

answered Oct 8, 2022 in Others by narikkadan
• 53,160 points
157 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
213 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
385 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,630 points
174 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 22,940 points
160 views
0 votes
1 answer

Excel Formula multiple Index Match and Average the result

If the conditions are separate and unrelated, ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 53,160 points
456 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP