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
• 38,010 points
1,599 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
• 63,700 points

Related Questions In Others

0 votes
1 answer

Excel worksheet multi-criteria function (like Index-Match array) to fetch last value of an item, looking up by item name and date

Use: =SUMIFS(C:C,B:B,E12,A:A,MAXIFS(A:A,B:B,E12)) It will return the value at the ...READ MORE

answered Apr 6, 2023 in Others by narikkadan
• 63,700 points
470 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, 2022 in Others by narikkadan
• 63,700 points
1,483 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
• 63,700 points
1,466 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,670 points
1,707 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,670 points
1,557 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,670 points
757 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
• 23,260 points
2,627 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
• 63,700 points
2,745 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