INDEX / MATCH / OFFSET / AVERAGE Formula

sprs248

New Member
Joined
Aug 20, 2019
Messages
18
Help me Guys...
DIVDOI55555333331111144444222226666677777888888
104111111002810128102401035810520107481086711583
1042222269487024709971747245736474408562
1043333310511200128013523
1044444413171325133713541371139614041524

<colgroup><col span="10" style="text-align: center;"></colgroup><tbody>
</tbody>
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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?
 
Upvote 0
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!!
 
Upvote 0
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.
 
Last edited:
Upvote 0
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..
 
Upvote 0
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 ..
DIVDOI55555333331111144444222226666677777888888
104111111002810128102401035810520107481086711583

<tbody>
</tbody>
 
Upvote 0
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.
 
Upvote 0
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. :cool:
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top