+ Reply to Thread
Results 1 to 2 of 2

Trouble identifying nth instances using *match/index function* with *offset function*

  1. #1
    Registered User
    Join Date
    06-09-2015
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    7

    Trouble identifying nth instances using *match/index function* with *offset function*

    =OFFSET(INDEX(sheet2E$5:$MI$5,SMALL(IF(sheet2E5:MH5="yes",COLUMN(E5:I5)-COLUMN(sheet2E5)+1),1)),-2,0,1,1)

    I used the above formula to find a month with a "yes" in order to indicate a barbie doll was sold. I need to offset ONLY when a yes is present, but i am currently struggling to come up with a formula that works properly passed the first instance of "yes". My goal is to solely offset from "yes" cells, and to completely ignore months without orders. The top chart is on one page and the bottom two charts are linking to the top chart, but they are on a different page. So for each instance, the quantity, buy price, quantity, sell price and total should be displayed under the instance 1 header. Months with no orders should not appear so that each instance only contains an order. (Example: if there is an order in June (M5) but not July (N5), but there is one in August (Q5), instance 1 will display the June order stats while instance 2 will display the August order stats). Thanks in advance!
    Attached Files Attached Files

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Trouble identifying nth instances using *match/index function* with *offset function*

    I didn't download your file.

    Don't use OFFSET. Index the range that you want the results to come from.

    Also, your ranges are not the same:

    INDEX(sheet2E$5:$MI$5
    IF(sheet2E5:MH5="yes"
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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. Index,match function trouble with formula
    By tuckerbunch in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-01-2013, 06:25 PM
  2. INDEX(...MATCH(IF...))) Function trouble
    By reaper_2 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-25-2011, 02:19 AM
  3. [SOLVED] Complicated Index Match Offset function
    By Bob in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 09:05 PM
  4. Complicated Index Match Offset function
    By Bob in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  5. [SOLVED] Complicated Index Match Offset function
    By Bob in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 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