Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup value, display multiple values in new worksheet
I want to look up a value from a column in one worksheet and display the
value from the next column, but in another worksheet. I can do if it is in the same worksheet, but the formula doesn't work if I use cell ranges from another sheet. ex: Name Type Bob A Joe B Cindy A Now in a column on a new worksheet I want to pull all names that fall under type A... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup value, display multiple values in new worksheet
=vlookup("bob",sheet2!a2:d33,2,0) -- Don Guillett Microsoft MVP Excel SalesAid Software "Neios" wrote in message ... I want to look up a value from a column in one worksheet and display the value from the next column, but in another worksheet. I can do if it is in the same worksheet, but the formula doesn't work if I use cell ranges from another sheet. ex: Name Type Bob A Joe B Cindy A Now in a column on a new worksheet I want to pull all names that fall under type A... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup value, display multiple values in new worksheet
I don't know the names ahead of time, only the categories...what I want is a
list of names that are of a specific type... "Don Guillett" wrote: =vlookup("bob",sheet2!a2:d33,2,0) -- Don Guillett Microsoft MVP Excel SalesAid Software "Neios" wrote in message ... I want to look up a value from a column in one worksheet and display the value from the next column, but in another worksheet. I can do if it is in the same worksheet, but the formula doesn't work if I use cell ranges from another sheet. ex: Name Type Bob A Joe B Cindy A Now in a column on a new worksheet I want to pull all names that fall under type A... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup value, display multiple values in new worksheet
This will work but may be slow if you have 1000's of rows of data.
Assume this data is on Sheet1 in the range A2:B4 - Name Type Bob A Joe B Cindy A In the formula: Name refers to Sheet1!A$2:A$4 Type refers to Sheet1!B$2:B$4 On Sheet2: A1 = a type to lookup = A Enter this array formula** in B1: =IF(ROWS(B$1:B1)<=COUNTIF(Type,A$1),INDEX(Name,SMA LL(IF(Type=A$1,ROW(Type)),ROWS(B$1:B1))-MIN(ROW(Type))+1),"") ** 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. Copy down to a number of cells that is at least the same as the max count for any type. For example, in your sample data type A appears the most times, 2. So you have to copy the formula down to at least 2 cells. -- Biff Microsoft Excel MVP "Neios" wrote in message ... I don't know the names ahead of time, only the categories...what I want is a list of names that are of a specific type... "Don Guillett" wrote: =vlookup("bob",sheet2!a2:d33,2,0) -- Don Guillett Microsoft MVP Excel SalesAid Software "Neios" wrote in message ... I want to look up a value from a column in one worksheet and display the value from the next column, but in another worksheet. I can do if it is in the same worksheet, but the formula doesn't work if I use cell ranges from another sheet. ex: Name Type Bob A Joe B Cindy A Now in a column on a new worksheet I want to pull all names that fall under type A... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup value, display multiple values in new worksheet
Excel 2007
Four ways to filter a Table to another sheet: http://www.mediafire.com/file/ftfjmwzmxtm/01_17_09.xlsm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup values and display them | Excel Discussion (Misc queries) | |||
Lookup letters and display highest values in a pivot table | Excel Discussion (Misc queries) | |||
Lookup and Display Using Multiple Criteria | Excel Worksheet Functions | |||
Lookup baseball scores in 1 worksheet and have them display in ano | Excel Worksheet Functions | |||
Lookup values in a column and display them in order with no gaps | Excel Worksheet Functions |