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

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

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

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

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