+ Reply to Thread
Results 1 to 16 of 16

Obtain multiple results for a given value from a table - (was Offset Formula?)

  1. #1
    Banned User!
    Join Date
    10-12-2018
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    358

    Obtain multiple results for a given value from a table - (was Offset Formula?)

    Hello,

    I have this formula which gives the matching result from column J on Sheet 2.

    =IFERROR(INDEX(Sheet2!$J$3:$J$12,MATCH($A$3,Sheet2!$D$3:$D$12,0)),"")

    This formula is in Sheet 1 cell C6.

    What would the formulas be for C7, C8, and C9 please? I would like the next data from column J on Sheet 2 matching what is put in $A$3.

    Thank you,
    XJ
    Last edited by xjohnson; 01-17-2019 at 06:38 AM. Reason: Change of title

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,101

    Re: Offset Formula?

    Please note that your thread title is meant to summarise what you are trying to do, not what you think the solution might be (which may or may not be apt). Please update your thread title accordingly. Thank you.
    Ali


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


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,858

    Re: Offset Formula?

    Try this:

    =IFERROR(INDEX(Sheet2!$J$3:$J$14,MATCH($A$3,Sheet2!$D$3:$D$14,0)+ROWS(C$6:C6)-1),"")

    Non-volatile, so it doesn't recalculate all the time!!
    Glenn



  4. #4
    Banned User!
    Join Date
    10-12-2018
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    358

    Re: Offset Formula?

    Thank you Glenn

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,101

    Re: Offset Formula?

    As the thread is marked as solved, I will not delete the solution. However, please note that thread titles need to be more explicit of your problem in future, and if you are asked to change a thread title, you should do so. You must NOT ignore moderation requests.

  6. #6
    Banned User!
    Join Date
    10-12-2018
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    358

    Re: Offset Formula?

    sorry for violating rules

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,101

    Re: Obtain multiple results for a given value from a table - (was Offset Formula?)

    Thanks - please remember this in future.

  8. #8
    Banned User!
    Join Date
    10-12-2018
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    358

    Re: Obtain multiple results for a given value from a table - (was Offset Formula?)

    Don't worry I am leaving

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,858

    Re: Offset Formula?

    Quote Originally Posted by AliGW View Post
    You must NOT ignore moderation requests.

    I didn't see it!!

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,101

    Re: Obtain multiple results for a given value from a table - (was Offset Formula?)

    Don't worry I am leaving
    That's rather an extreme response to being asked to amend your thread title, especially as I also explained why it needed to be done. Your prerogative, of course, but there really is no need for you to leave.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,101

    Re: Offset Formula?

    Quote Originally Posted by Glenn Kennedy View Post
    I didn't see it!!
    Wasn't aimed at you, Glenn, and the OP has now amended the thread title.

  12. #12
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,368

    Re: Obtain multiple results for a given value from a table - (was Offset Formula?)

    WHy not save yourself a whole lot of trouble and create a proper normalised database.

    Add a Pivot Table and some Slicers and you have a much more efficient analysis system

    See attached
    Attached Files Attached Files
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  13. #13
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,368

    Re: Obtain multiple results for a given value from a table - (was Offset Formula?)

    Hi xJohnson,

    I would encourage you not to leave. No one is trying to be unhelpful. Sometimes misunderstandings occur.

  14. #14
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,368

    Re: Offset Formula?

    Actually I changed the title on behalf of the OP.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,101

    Re: Obtain multiple results for a given value from a table - (was Offset Formula?)

    Thanks for the clarification, Richard. I was working from the new rules (rule #1 and footnote A, regarding experienced members), which is why I assumed (incorrectly) that the OP had complied with my request.

  16. #16
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,368

    Re: Obtain multiple results for a given value from a table - (was Offset Formula?)

    Quote Originally Posted by AliGW View Post
    Thanks for the clarification, Richard. I was working from the new rules (rule #1 and footnote A, regarding experienced members), which is why I assumed (incorrectly) that the OP had complied with my request.
    No problem. That's why we're here

+ 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. [SOLVED] Re: Is it possible to incorporate an Offset formula with a Index Match formula?
    By billrogers184 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-31-2018, 02:17 PM
  2. [SOLVED] My offset formula doesn't like referencing a cell with a formula.
    By MrOchoa in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-21-2016, 01:35 PM
  3. Gone formula blind -cant get OFFSET formula to work
    By dazbear in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-25-2015, 09:07 AM
  4. 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
  5. 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
  6. Replies: 2
    Last Post: 10-08-2009, 09:51 PM
  7. Replies: 2
    Last Post: 01-03-2006, 05:42 PM

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