# Thread: INDEX / MATCH / OFFSET / AVERAGE Formula Thanks: 0 Likes:  2 Post #5328542 (1)Post #5329680 (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.  Reply With Quote

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?  Reply With Quote

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!!  Reply With Quote

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.  Reply With Quote

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..  Reply With Quote

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  Reply With Quote

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.  Reply With Quote

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.   Reply With Quote

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.  Reply With Quote

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.  Reply With Quote

## User Tag List

average, marked, row, searched, top 