+ Reply to Thread
Results 1 to 10 of 10

Re: Is it possible to incorporate an Offset formula with a Index Match formula?

  1. #1
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Asheville NC
    MS-Off Ver
    Excel for Office 365 MSO
    Posts
    111

    Re: Is it possible to incorporate an Offset formula with a Index Match formula?

    I get a spread sheet from other colleagues and compare it to my data. I normally insert columns and then do a index match formula in one cell then drag down the column. I then have to manually edit the formula if I copy it to other columns are not not next to my first column. I've read about offsetting but can't seem to get the formula to work. See attachment and thanks for any advise.
    Attached Files Attached Files
    Last edited by AliGW; 10-29-2018 at 05:46 PM.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Offset

    Try this in D14:

    =INDEX(Sheet3!$D$5:$H$11,MATCH($A14,Sheet3!$C$5:$C$11,0),MATCH(C$12,Sheet3!$D$4:$H$4,0))

    You can then drag the formula down and copy/paste it into the columns to the right.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,324

    Re: Offset

    Billrogers - your thread title is not descriptive of your problem. Please change it to make it more explicit. Thanks.

    No further assistance to be offered here until the title has been changed, please.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Offset

    Thanks for the rep!

    After following the request from post #3, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  5. #5
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Asheville NC
    MS-Off Ver
    Excel for Office 365 MSO
    Posts
    111

    Re: Is it possible to incorporate an Offset formula with a Index Match formula?

    Title updated to be more descriptive.

  6. #6
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Asheville NC
    MS-Off Ver
    Excel for Office 365 MSO
    Posts
    111

    Re: Offset

    63falcondude, thanks for your index match solution. Is it possible to use an Offset formula also without using header titles?

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,324

    Re: Is it possible to incorporate an Offset formula with a Index Match formula?

    Thanks for the title update - it was in the wrong post, but I’ve added it to the opening post for you.

  8. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Is it possible to incorporate an Offset formula with a Index Match formula?

    If you insist...

    D14 =INDEX(OFFSET(Sheet3!$D$5:$D$11,0,INT((COLUMNS($A:A)-1)/3)),MATCH($A14,Sheet3!$C$5:$C$11,0))

    Just keep in mind that OFFSET is volatile and will recalculate every time anything is done in the workbook.

  9. #9
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Asheville NC
    MS-Off Ver
    Excel for Office 365 MSO
    Posts
    111

    Re: Is it possible to incorporate an Offset formula with a Index Match formula?

    Thanks again for the advice and formula.

  10. #10
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Is it possible to incorporate an Offset formula with a Index Match formula?

    You're welcome. Happy to help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 10-05-2016, 01:11 PM
  2. How to offset previously offset-ed cell ?
    By freshe in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-07-2015, 09:34 AM
  3. Select offset variable offset cells
    By papasmurfuo9 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-02-2014, 06:12 AM
  4. Replies: 3
    Last Post: 06-01-2014, 10:26 AM
  5. function similar to offset to offset the offset
    By Jerseynjphillypa in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-03-2012, 03:07 PM
  6. Reformatting - insert formula with offset, then offset to next cell
    By Armitage2k in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-06-2011, 05:41 AM
  7. Replies: 7
    Last Post: 11-04-2008, 06:41 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1