Lookup value, display multiple values in new worksheet

[Neios]

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...

 

[Don Guillett]

=vlookup("bob",sheet2!a2:d33,2,0)

 

[Neios]

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...

 

[T. Valko]

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,SMALL(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.

 

[Herbert Seidenberg]

Excel 2007
Four ways to filter a Table to another sheet:
http://www.mediafire.com/file/ftfjmwzmxtm/01_17_09.xlsm