+ Reply to Thread
Results 1 to 3 of 3

Offset or Index or both

  1. #1
    Registered User
    Join Date
    03-07-2012
    Location
    Londinium
    MS-Off Ver
    Excel 2007
    Posts
    13

    Offset or Index or both

    Good evening Forum Members,

    It has been a long Friday and I am still not any closer to my solution so I am hoping someone here can help me.

    I have read various post on here and on blogs about the Index and Offset functions. I understand the offset function very well. And I think I have just about got my head around the Index function. But somehow when I use them together I cannot seem to get the result I am looking for.

    Here is my example sheet
    Offset or index or both.xlsx

    Ideally I want C2 to be my input cell and then based on the value in C2 the "magic" of the formula returns all of the corresponding colours for the code in C2 into cells C3:C6 from the table below.

    I have left the values in C3:C6 as a way of example of the expected result.

    Look forward to your repsonses.

    Regards
    richgoof

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Offset or Index or both

    Maybe try add a helper column.

    =IF(COUNTIF($C$10:$C$22,$I$3)>=H4,OFFSET($C$10,MATCH($I$3,$C$10:$C$22,0)+(ROW(A4)-5),3),"")
    Attached Files Attached Files

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Offset or Index or both

    In C3 enter this array formula and copy down

    =IFERROR(INDEX($F$10:$F$22,SMALL(IF($C$10:$C$22=$C$2,ROW($C$10:$C$22)-ROW($C$10)+1),ROWS(C$10:C10))),"")

    ***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

    Or you can also use this regular formula

    =IF(ROWS(C$3:C3)>COUNTIF(C$10:C$22,C$2),"",OFFSET(F10,MATCH(C$2,C$10:C$22,0)-1,0))
    Last edited by AlKey; 02-06-2015 at 10:13 PM. Reason: Added regular formula
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

+ 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. Offset/ Index Not sure
    By lourensb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-02-2014, 08:48 AM
  2. Index match offset to get subsequent index values in a column
    By Andrew_Step in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2013, 02:55 PM
  3. offset, index
    By step_one in forum Excel General
    Replies: 2
    Last Post: 07-21-2011, 02:10 PM
  4. [SOLVED] Should it be Index or Offset ?
    By colwyn in forum Excel General
    Replies: 2
    Last Post: 10-01-2008, 10:14 AM
  5. Using INDEX instead of OFFSET
    By JK1234 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-30-2008, 11:07 AM

Tags for this Thread

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