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 in Others by Kithuzzz
• 20,660 points
56 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 by narikkadan
• 37,660 points

Related Questions In Others

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 in Others by narikkadan
• 37,660 points
84 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 in Others by narikkadan
• 37,660 points
59 views
0 votes
1 answer
0 votes
1 answer

Excel Formula to convert from cm to inches and feet & round off to two digits

Use the TEXT function to format, and ...READ MORE

answered Oct 15 in Others by narikkadan
• 37,660 points
45 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 in Others by Edureka
• 13,640 points
142 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 in Others by Edureka
• 13,640 points
242 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 in Others by Edureka
• 13,640 points
109 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18 in Others by gaurav
• 22,040 points
71 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 in Others by narikkadan
• 37,660 points
73 views
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 in Others by narikkadan
• 37,660 points
64 views
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