+ Reply to Thread
Results 1 to 2 of 2

Find a value in an array and return multiple values in an adjacent array

  1. #1
    Registered User
    Join Date
    02-02-2012
    Location
    Spokane
    MS-Off Ver
    Excel 2003
    Posts
    4

    Find a value in an array and return multiple values in an adjacent array

    Book1_tonbra.xlsxExcel 2010

    I am trying to build a spreadsheet that can look up a value in an array and return multiple values from a corresponding adjacent array. For example, I want to search A2:A250 for a specific date (such as 1/1/2012). Every time the look up function finds the specific date, I want it to return the corresponding value in the "B" column. For example, you might have 5 instances of "1/1/2012" and at each instance, the corresponding temperature from parts arond the world are listed in the "B" column. You would then have something that looks like: 1/1/2012 32-35-41-33-34

    I found a thread that had something like this with an example spreadsheet I downloaded. Works great!- the problem is I can seem to copy or re-write the code to search the size of an array that I want. The example only searches from cell A2:A9. Every time I copy or re-write to expand the search range, I get nothing. My edited code is IDENTICAL to the code I copied in how it is structured. There is ONE exception that I cant figure out. On the cells that I copied, there is a {} at the start and end of the code. When these figures are removed, it no longer works. When I edit the code to expand the size and I add the {} to the start and end, nothing happens.

    What the HECK do I need to do to get this formula to work?

    {=IF(COUNTIF($A$2:$A$10,$D2)<COLUMN(A$1),"",INDEX($B$2:$B$10,IF(COUNTIF($A$2:$A$10,$D2)>=COLUMNS($E1:E$1),SMALL(IF($D2=$A$2:$A$10,ROW($B$2:$B$10)-1,""),COLUMNS($E1:E$1 )),""),1))}

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Find a value in an array and return multiple values in an adjacent array

    Hi

    The {} relate to the formula being array entered. Edit the formula, then hold down the ctrl and shift keys together, then press the enter key. You will find that the resulting formula will have the {}

    Actually, your formula hasn't been updated for the revised positions. The formula for E13 should be =IF(COUNTIF($A$13:$A$20,$D13)<COLUMN(A$12),"",INDEX($B$13:$B$20,IF(COUNTIF($A$13:$A$20,$D13)>=COLUMNS($E12:E$12),SMALL(IF($D13=$A$13:$A$20,ROW($B$13:$B$20)-12,""),COLUMNS($E12:E$12)),""),1))


    HTH

    rylo
    Last edited by rylo; 02-02-2012 at 08:53 PM.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1