Try this...

Names used in the formulas refer to:

SaleRep: refers to =Sheet1!$A$2:$A$100
Customer: refers to =Sheet1!$B$2:$B$100

On Sheet2:

A1 = some name like Wahlberg

Enter this formula in B1. This will return the count of records related to
Wahlberg

=COUNTIF(SalesRep,A1)

Enter this array formula** in C1. This will return the customer names
related to Wahlberg.

=IF(ROWS(C$1:C1)B$1,"",INDEX(Customer,SMALL(IF(Sa lesRep=A$1,ROW(Customer)),ROWS(C$1:C1))-MIN(ROW(Customer))+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 the formula in C1 down until you get blanks meaning all recods have
been extracted.

--
Biff
Microsoft Excel MVP


"Deema" wrote in message
...
I have two spreadsheets.

First spreadsheet contains 4 columns of data (A-D). A= sales rep,
B=customer, C=outbound, D=inbound.

Second spreadsheet contains numerous pages, all are indentical except that
each page contain data for a different sales rep.

I am trying to create an IF statement that would pull data from the first
spreadsheet for a particular tech. I tried this put it's not pulling all
of
the data.

Below is basically searching column A for a sales rep by the name Wahlberg
and then if found taking the data in column B (customer) and putting it in
a
column on my second spreadsheet.
=IF(sheet1!$$A$2:$A$200="Wahlberg",sheet1!$B$2:$B$ 200,"-")