Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup one value in list and return multiple results
Hi,
Here is an illustrative example of my problem: I have a list of numbers and letters. How can I lookup the value " 1 " and return the multiple results of z, a, g ? I want to type only one formula in one cell, although I don't mind the result being spread out over multiple cells. Which formula can be used? Thanks for your thoughts on this. 1 z 2 b 1 a 3 e 4 f 1 g |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup one value in list and return multiple results
With your posted sample in Cells A1:B6
Try this: C1: (the Col_A value to find....eg 1) Enter this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, instead of just ENTER....in cell: Note: Formula is in sections for readability: D1: =IF(ROWS($1:1)COUNTIF($A$1:$A$10,$C$1),"", INDEX($B$1:$B$10,SMALL(IF(($A$1:$A$10=$C$1),ROW($A $1:$A$10)),ROWS($1:1)))) Copy D1 and paste into D2:D5 Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "ORLANDO V" wrote in message ... Hi, Here is an illustrative example of my problem: I have a list of numbers and letters. How can I lookup the value " 1 " and return the multiple results of z, a, g ? I want to type only one formula in one cell, although I don't mind the result being spread out over multiple cells. Which formula can be used? Thanks for your thoughts on this. 1 z 2 b 1 a 3 e 4 f 1 g |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup one value in list and return multiple results
So, are you saying that you *don't* want to enter a formula in a cell and
then copy it down the column? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "ORLANDO V" wrote in message ... Hi, Here is an illustrative example of my problem: I have a list of numbers and letters. How can I lookup the value " 1 " and return the multiple results of z, a, g ? I want to type only one formula in one cell, although I don't mind the result being spread out over multiple cells. Which formula can be used? Thanks for your thoughts on this. 1 z 2 b 1 a 3 e 4 f 1 g |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
V lookup with 2 criteria to return results for multiple columns | Excel Worksheet Functions | |||
how to return multiple results in vlookup? | Excel Worksheet Functions | |||
Lookup a value in a list & return multiple corresponding values | Excel Worksheet Functions | |||
Return Multiple Results with Lookup | Excel Worksheet Functions | |||
Lookup values in a list and return multiple rows of data | Excel Worksheet Functions |