INDEX / MATCH / OFFSET / AVERAGE Formula

[sprs248]

Help me Guys...
[TABLE="width: 640"]
<colgroup><col span="10" style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]DIV[/TD]
[TD="align: center"]DOI[/TD]
[TD="align: center"]55555[/TD]
[TD="align: center"]33333[/TD]
[TD="align: center"]11111[/TD]
[TD="align: center"]44444[/TD]
[TD="align: center"]22222[/TD]
[TD="align: center"]66666[/TD]
[TD="align: center"]77777[/TD]
[TD="align: center"]888888[/TD]
[/TR]
[TR]
[TD="align: center"]104[/TD]
[TD="align: center"]11111[/TD]
[TD="align: center"]10028[/TD]
[TD="align: center"]10128[/TD]
[TD="align: center"]10240[/TD]
[TD="align: center"]10358[/TD]
[TD="align: center"]10520[/TD]
[TD="align: center"]10748[/TD]
[TD="align: center"]10867[/TD]
[TD="align: center"]11583[/TD]
[/TR]
[TR]
[TD="align: center"]104[/TD]
[TD="align: center"]22222[/TD]
[TD="align: center"]6948[/TD]
[TD="align: center"]7024[/TD]
[TD="align: center"]7099[/TD]
[TD="align: center"]7174[/TD]
[TD="align: center"]7245[/TD]
[TD="align: center"]7364[/TD]
[TD="align: center"]7440[/TD]
[TD="align: center"]8562[/TD]
[/TR]
[TR]
[TD="align: center"]104[/TD]
[TD="align: center"]33333[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1051[/TD]
[TD="align: center"]1200[/TD]
[TD="align: center"]1280[/TD]
[TD="align: center"]13523[/TD]
[/TR]
[TR]
[TD="align: center"]104[/TD]
[TD="align: center"]44444[/TD]
[TD="align: center"]1317[/TD]
[TD="align: center"]1325[/TD]
[TD="align: center"]1337[/TD]
[TD="align: center"]1354[/TD]
[TD="align: center"]1371[/TD]
[TD="align: center"]1396[/TD]
[TD="align: center"]1404[/TD]
[TD="align: center"]1524[/TD]
[/TR]
</tbody>[/TABLE]
Example: Left Red marked "22222" needs to be searched in top Row and matched with "22222" and get results as "7245", which is the intersection point of simultaneous Row & Column. Then I need the Average of three more cells to the right from the same row starting from 7364, 7440, 8562.

 

[steve the fish]

Hi. This would work:

=AVERAGE(INDEX(C3:J3,MATCH($B3,$C$1:$J$1,0)+1):INDEX(C3:J3,MATCH($B3,$C$1:$J$1,0)+3))

but i dont know if you already know which row the lookup cell (the left red 22222) is in?

 

[sprs248]

Hi. This would work:

=AVERAGE(INDEX(C3:J3,MATCH($B3,$C$1:$J$1,0)+1):INDEX(C3:J3,MATCH($B3,$C$1:$J$1,0)+3))

but i dont know if you already know which row the lookup cell (the left red 22222) is in?

Thanks Bro.. Really it worked...
If i want the Average of three more cells to the Left from the same row starting from 7174 7099 7024, what will be the formula!!

 

[Peter_SSs]

Welcome to the MrExcel board!

If you don't have a lot of these to do on the sheet, you can also use OFFSET as follows

3 to the right:
=AVERAGE(OFFSET($B3,0,MATCH($B3,B$1:J$1,0),1,3))

3 to the left
=AVERAGE(OFFSET($A3,0,MATCH($B3,C$1:J$1,0),1,-3))

.. but note that OFFSET is a volatile function so can slow your sheet if a great number of them are used.

 

[sprs248]

Welcome to the MrExcel board!

If you don't have a lot of these to do on the sheet, you can also use OFFSET as follows

3 to the right:
=AVERAGE(OFFSET($B3,0,MATCH($B3,B$1:J$1,0),1,3))

3 to the left
=AVERAGE(OFFSET($A3,0,MATCH($B3,C$1:J$1,0),1,-3))

.. but note that OFFSET is a volatile function so can slow your sheet if a great number of them are used.

Thanks.. But i have 1.60 Lacs ROWS in working sheet. Kindly help..

 

[sprs248]

Hi. This would work:

=AVERAGE(INDEX(C3:J3,MATCH($B3,$C$1:$J$1,0)+1):INDEX(C3:J3,MATCH($B3,$C$1:$J$1,0)+3))

but i dont know if you already know which row the lookup cell (the left red 22222) is in?

If i want to choose from right , but after 2 cells ..
[TABLE="class: cms_table, width: 640"]
<tbody>[TR]
[TD="align: center"]DIV[/TD]
[TD="align: center"]DOI[/TD]
[TD="align: center"]55555[/TD]
[TD="align: center"]33333[/TD]
[TD="align: center"]11111[/TD]
[TD="align: center"]44444[/TD]
[TD="align: center"]22222[/TD]
[TD="align: center"]66666[/TD]
[TD="align: center"]77777[/TD]
[TD="align: center"]888888[/TD]
[/TR]
[TR]
[TD="align: center"]104[/TD]
[TD="align: center"]11111[/TD]
[TD="align: center"]10028[/TD]
[TD="align: center"]10128[/TD]
[TD="align: center"]10240[/TD]
[TD="align: center"]10358[/TD]
[TD="align: center"]10520[/TD]
[TD="align: center"]10748[/TD]
[TD="align: center"]10867[/TD]
[TD="align: center"]11583[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

If you look at the formula it has two match formulas in it. The +1 and the +3 are being used to manipulate where the average range starts and ends. So plus one means it starts one cell to the right of the 22222 and finishes three cells to the right of the 22222. You can use that to change where you are looking. If you want two cells to the right then use +2 in the first match formula.

 

[sprs248]

If you look at the formula it has two match formulas in it. The +1 and the +3 are being used to manipulate where the average range starts and ends. So plus one means it starts one cell to the right of the 22222 and finishes three cells to the right of the 22222. You can use that to change where you are looking. If you want two cells to the right then use +2 in the first match formula.

Thanks ... It worked.

 

[ThuThuatExcel]

Hi. This would work:

=AVERAGE(INDEX(C3:J3,MATCH($B3,$C$1:$J$1,0)+1):INDEX(C3:J3,MATCH($B3,$C$1:$J$1,0)+3))

but i dont know if you already know which row the lookup cell (the left red 22222) is in?

Hi Pro
Can explain why add 1 and 3 in the formula, thanks.

 

[Peter_SSs]

Hi Pro
Can explain why add 1 and 3 in the formula, thanks.

Welcome to the MrExcel board!

Because the OP wanted to start the average 1 column to the right of where the 22222 was found in the heading row and end the average 3 columns to the right of where the 22222 was found in the heading row.