# Thread: INDEX / MATCH / OFFSET / AVERAGE Formula Thanks: 0 Likes:  2 Post #5329680 (1)Post #5328542 (1)

1. ## INDEX / MATCH / OFFSET / AVERAGE Formula

Help me Guys...
 DIV DOI 55555 33333 11111 44444 22222 66666 77777 888888 104 11111 10028 10128 10240 10358 10520 10748 10867 11583 104 22222 6948 7024 7099 7174 7245 7364 7440 8562 104 33333 1051 1200 1280 13523 104 44444 1317 1325 1337 1354 1371 1396 1404 1524
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.

2. ## Re: INDEX / MATCH / OFFSET / AVERAGE Formula

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?

3. ## Re: INDEX / MATCH / OFFSET / AVERAGE Formula

Originally Posted by 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?
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!!

4. ## Re: INDEX / MATCH / OFFSET / AVERAGE Formula

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.

5. ## Re: INDEX / MATCH / OFFSET / AVERAGE Formula

Originally Posted by 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.
Thanks.. But i have 1.60 Lacs ROWS in working sheet. Kindly help..

6. ## Re: INDEX / MATCH / OFFSET / AVERAGE Formula

Originally Posted by 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?
If i want to choose from right , but after 2 cells ..
 DIV DOI 55555 33333 11111 44444 22222 66666 77777 888888 104 11111 10028 10128 10240 10358 10520 10748 10867 11583

7. ## Re: INDEX / MATCH / OFFSET / AVERAGE Formula

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.

8. ## Re: INDEX / MATCH / OFFSET / AVERAGE Formula

Originally Posted by 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.
Thanks ... It worked.

9. ## Re: INDEX / MATCH / OFFSET / AVERAGE Formula

Originally Posted by 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?
Hi Pro
Can explain why add 1 and 3 in the formula, thanks.

10. ## Re: INDEX / MATCH / OFFSET / AVERAGE Formula

Originally Posted by ThuThuatExcel
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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•