Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Please - Lookup in an array that has duplicte values
I have an array with the lead column having duplicate ID #'s, but unique
related data in the array. I need to lookup all related data to the ID. In the following example, I need to lookup ID #2 and find both Blue and Green. Is this possible? ID Color 1 Red 2 Blue 2 Green 3 Black -- Bryan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Please - Lookup in an array that has duplicte values
With the query ID in Cell C1 enter the below array formula in cell C2 and
copy down as required Col A Col B Col C ID Color 2 1 Red =formula 2 Blue 2 Green 3 Black =IF(COUNTIF($A$1:$A$100,C1)<ROW(A1),"",INDEX(B$1:B $100, SMALL(IF($A$1:$A$100=C1,ROW($A$1:$A$100)),ROW(A1)) )) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "CBI FlexCard" wrote: I have an array with the lead column having duplicate ID #'s, but unique related data in the array. I need to lookup all related data to the ID. In the following example, I need to lookup ID #2 and find both Blue and Green. Is this possible? ID Color 1 Red 2 Blue 2 Green 3 Black -- Bryan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Please - Lookup in an array that has duplicte values
Try this...
Defined names: ID refers to: =$A$2:$A$5 Color refers to: =$B:$B D2 = lookup ID = 2 Enter this formula in E2. This will return the count of lookup ID's. =COUNTIF(ID,D2) Enter this array formula** in D3 and copy down until you get blanks: =IF(ROWS(D$3:D3)E$2,"",INDEX(Color,SMALL(IF(ID=D$ 2,ROW(ID)),ROWS(D$3:D3)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "CBI FlexCard" wrote in message ... I have an array with the lead column having duplicate ID #'s, but unique related data in the array. I need to lookup all related data to the ID. In the following example, I need to lookup ID #2 and find both Blue and Green. Is this possible? ID Color 1 Red 2 Blue 2 Green 3 Black -- Bryan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use array for lookup value, to return array of lookups | Excel Discussion (Misc queries) | |||
Table Lookup formula where 2 known values are inside array | New Users to Excel | |||
Array: Counting multiple values within array | Excel Worksheet Functions | |||
lookup a list of values for an array formula | Excel Worksheet Functions | |||
find maximum of two values in an array with same lookup value | Excel Discussion (Misc queries) |