| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search Column Data and Return Multiple Values across Row
Hi All,
I have two columns of data. Column "A" houses Numeric Labels and Column "B" houses Numeric Values. Data: Numeric Labels A20:A105 Numeric Values B20:B105 I would like to return across a Single Row all Numeric Lables that have a corresponding Numeric Value in Column "B" that is =2 (greater than or equal to 2). Thanks, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200609/1 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search Column Data and Return Multiple Values across Row
Assuming that the results are to be returned in Row 20, starting at D20,
try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER... D20, copied across: =IF(COLUMNS($D20:D20)<=COUNTIF($B$20:$B$105,"=2") ,INDEX($A$20:$A$105,SMA LL(IF($B$20:$B$105=2,ROW($B$20:$B$105)-ROW($B$20)+1),COLUMNS($D20:D20))) ,"") Hope this helps! In article <671292b883da7@uwe, "Sam via OfficeKB.com" <u4102@uwe wrote: Hi All, I have two columns of data. Column "A" houses Numeric Labels and Column "B" houses Numeric Values. Data: Numeric Labels A20:A105 Numeric Values B20:B105 I would like to return across a Single Row all Numeric Lables that have a corresponding Numeric Value in Column "B" that is =2 (greater than or equal to 2). Thanks, Sam |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search Column Data and Return Multiple Values across Row
Hi Domenic,
Thank you very much. Formula works Great! Cheers, Sam Domenic wrote: Assuming that the results are to be returned in Row 20, starting at D20, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER... D20, copied across: =IF(COLUMNS($D20:D20)<=COUNTIF($B$20:$B$105,"=2" ),INDEX($A$20:$A$105,SMA LL(IF($B$20:$B$105=2,ROW($B$20:$B$105)-ROW($B$20)+1),COLUMNS($D20:D20))) ,"") Hope this helps! -- Message posted via http://www.officekb.com |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search Column Data and Return Multiple Values across Row
Just an FYI at this point - you also could have used the autofilter and
filtered on column B (custom) for values = 2. Then select the values in column A and copy, select your destination, then click Edit/Paste Special - Transpose "Sam via OfficeKB.com" wrote: Hi All, I have two columns of data. Column "A" houses Numeric Labels and Column "B" houses Numeric Values. Data: Numeric Labels A20:A105 Numeric Values B20:B105 I would like to return across a Single Row all Numeric Lables that have a corresponding Numeric Value in Column "B" that is =2 (greater than or equal to 2). Thanks, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200609/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
| Search multiple values & return single value - seperate worksheets | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Linear Mode
