Results 1 to 10 of 10

Thread: INDEX / MATCH / OFFSET / AVERAGE Formula

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    17
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,668
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default 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?
    Looking for opportunities

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    17
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDEX / MATCH / OFFSET / AVERAGE Formula

    Quote Originally Posted by steve the fish View Post
    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. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,375
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default 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.
    Last edited by Peter_SSs; Aug 20th, 2019 at 09:32 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    17
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDEX / MATCH / OFFSET / AVERAGE Formula

    Quote Originally Posted by Peter_SSs View Post
    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. #6
    New Member
    Join Date
    Aug 2019
    Posts
    17
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDEX / MATCH / OFFSET / AVERAGE Formula

    Quote Originally Posted by steve the fish View Post
    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. #7
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,668
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default 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.
    Looking for opportunities

  8. #8
    New Member
    Join Date
    Aug 2019
    Posts
    17
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDEX / MATCH / OFFSET / AVERAGE Formula

    Quote Originally Posted by steve the fish View Post
    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. #9
    New Member
    Join Date
    Aug 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDEX / MATCH / OFFSET / AVERAGE Formula

    Quote Originally Posted by steve the fish View Post
    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. #10
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,375
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: INDEX / MATCH / OFFSET / AVERAGE Formula

    Quote Originally Posted by ThuThuatExcel View Post
    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.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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