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))}
Bookmarks