+ Reply to Thread
Results 1 to 8 of 8

Index / Match to find next instance of a value

  1. 10-10-2012, 07:47 AM #1
    boylers75
    boylers75 is offline
    Registered User
    Join Date
    10-02-2012
    Location
    Galway, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    34

    Index / Match to find next instance of a value

    Hi,
    I have a sheet which uses a search function. When a value is entered in to the "Search" sheet in C4, the formula in C9 is:
    Please Login or Register  to view this content.
    which works fine.
    the thing is, there could be more than one instance of the value in C4 and I want to get the next instance and put this formula into C11 and again into C13 for the next instance.
    Can anyone help please?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-10-2012, 08:52 AM #2
    Jakobshavn
    Jakobshavn is offline
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Index / Match to find next instance of a value

    MATCH/INDEX like VLOOKUP works great if you only need is to retrieve the first instance of the match. To retrieve all the matches we can use a sequential match table (beginning each match search in the cell after the previous match is found)

    See the attached for a very simple example.
    Attached Files Attached Files
    Gary's Student
    Reply With QuoteRegister To Reply
  3. 10-10-2012, 08:55 AM #3
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Index / Match to find next instance of a value

    hi boylers75. would you mind not merging the cells in the 3 yellow cells? i have attached a solution of mine using array formulas. and they dont work well with merged cells. to obtain the effect like merged cells, select the cells you want to merge, right-click-> format cells -> Alignment -> Horizontal: Center Across Selection -> OK

    the array formulas i used must be confirmed with CTRL + SHIFT + ENTER if you ever need to edit anything.
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."

    Tips & Tutorials I Compiled | How to Get Quick & Good Answers
    Reply With QuoteRegister To Reply
  4. 10-10-2012, 08:56 AM #4
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Index / Match to find next instance of a value

    You can use this ARRAY formula for this, but you can not use an Array formula in merged cells.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  5. 10-10-2012, 09:04 AM #5
    greenyman
    greenyman is offline
    Registered User
    Join Date
    04-14-2011
    Location
    australia
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Index / Match to find next instance of a value

    try the below array formula:

    A9 =IFERROR(INDEX(STS!A:A,SMALL(IF(STS!C:C=$C$4,ROW(INDIRECT("1:"&ROWS(STS!A:A)))),1)),"") ---- CRTL+SHIFT+ENTER
    A11 =IFERROR(INDEX(STS!A:A,SMALL(IF(STS!C:C=$C$4,ROW(INDIRECT("1:"&ROWS(STS!A:A)))),2)),"") ---- CRTL+SHIFT+ENTER
    A13 =IFERROR(INDEX(STS!A:A,SMALL(IF(STS!C:C=$C$4,ROW(INDIRECT("1:"&ROWS(STS!A:A)))),3)),"") ---- CRTL+SHIFT+ENTER
    Reply With QuoteRegister To Reply
  6. 10-10-2012, 09:06 AM #6
    greenyman
    greenyman is offline
    Registered User
    Join Date
    04-14-2011
    Location
    australia
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Index / Match to find next instance of a value

    try the below array formula:

    A9 =IFERROR(INDEX(STS!A:A,SMALL(IF(STS!C:C=$C$4,ROW(INDIRECT("1:"&ROWS(STS!A:A)))),1)),"") ---- CRTL+SHIFT+ENTER
    A11 =IFERROR(INDEX(STS!A:A,SMALL(IF(STS!C:C=$C$4,ROW(INDIRECT("1:"&ROWS(STS!A:A)))),2)),"") ---- CRTL+SHIFT+ENTER
    A13 =IFERROR(INDEX(STS!A:A,SMALL(IF(STS!C:C=$C$4,ROW(INDIRECT("1:"&ROWS(STS!A:A)))),3)),"") ---- CRTL+SHIFT+ENTER
    Reply With QuoteRegister To Reply
  7. 10-10-2012, 09:06 AM #7
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Index / Match to find next instance of a value

    You can't use that original formula to do that, but since you're using VBA anyway, you can use a Do Loop to find all the instances of that code and insert them into the results.

    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  8. 10-10-2012, 10:06 AM #8
    boylers75
    boylers75 is offline
    Registered User
    Join Date
    10-02-2012
    Location
    Galway, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    34

    Re: Index / Match to find next instance of a value

    Thanks for all the help and suggestions.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1