Assuming that Columns A through E contain your data, and that the first
row contains your headers/labels, try the following...

First, define the following reference...

Insert Name Define

Name: AcctNum

Refers to:

=Sheet1!$G$2:INDEX(Sheet1!$G$2:$G$65536,MATCH(REPT ("z",255),Sheet1!$G$2:$
G$65536))

This will allow you to enter one or more account numbers in Column G,
starting from G2, without having to adjust its range reference in the
formula.

Then, enter the following formulas...

H1: enter 0 (zero)

H2, copied down:

=IF((A2<"")*(ISNUMBER(MATCH(A2,AcctNum,0))),LOOKU P(9.99999999999999E+307
,$H$1:H1)+1,"")

I1:

=LOOKUP(9.99999999999999E+307,H:H)

J2, copied down:

=IF(ROW()-ROW(J$2)+1<=$I$1,MATCH(ROW()-ROW(J$2)+1,H:H,0),"")

K2, copied across and down:

=IF(N($J2),INDEX(A:A,$J2),"")

Note that Columns H, I, and J can be hidden, if you so wish.

Hope this helps!

In article ,
"Candice H." <Candice wrote:

Hi all..

I have a situation that is a bit unique and interesting. I have a list of
account numbers that have invoices linked to them. From this vast list, I
have around 10 -15 different account numbers that I would like the invoice
numbers (and associated information) pulled from the list and put into
another area (on the same worksheet). The problem is that an account number
may appear multiple times in the list (have more than 1 invoice) and when I
do Vlookup or Vlookups it's only returning the first invoice. I would like
to have it look in the big list for the account number, and return all
instances associated with that account number.

Here's an example:

A B C D E
F G
1 090WEN111 111012 $20.00 Paper 4/25/05
090PAP112
2 090PAP112 112141 $100.00 Tissue 1/25/05
090TIM412
3 090PAP112 114571 $58.23 Paper 3/1/05
4 090TIM412 142577 $45.21 SANI 5/1/05

so in this example, columns A - E have all the info that comes from a
database report. Column G has a select list of accounts that I need the data
for. Instead of me having to go through hundreds of records, I would like to
have Excel find these records and place them into a separate area on the same
worksheet.

I know that this one is a bit long and drawn out but I really would
appreciate any help someone could offer... Thanks in advance!