Lookup Formula

[Kaye]

I have created a Vlookup formula that looks in cell A1 (account #) on Tab A
and then goes to Tab B to find that account #. Tab B will have about 3000
lines of data and could include that account # as many as 30 times. When it
finds each line with that account number I need it listed on Tab A. I can
get it to copy the first time it finds that account # but how can I get it to
list all the others on lines 2-31? Is there a way that all 30 lines can be
listed on Tab A? Thanks

 

[Bob Phillips]

=IF(ISERROR(SMALL(IF(Sheet2!$A$1:$A$20=$A$1,ROW($A$1:$A$20),""),ROW($A1))),"",
INDEX(Sheet2!$B$1:$B$20,SMALL(IF(Sheet2!$A$1:$A$20=$A$1,ROW($A$1:$A$20),""),ROW($A1))))

This is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

Copy the formula down as far as you need.

 

[Bob Umlas, Excel MVP]

One way: Suppose you're looking for account # in cell A1 (of Tab A). Enter
this formula in cell B2 (B1 must be empty):
=MATCH($A$1,OFFSET(Sheet2!$A$1,B1,0,1000,1),0)+B1 and fill down. When there
are no more, you'll see #N/A. If that's OK, you're done. If you don't want to
see the #N/A's then select all of col B, use Format/Conditional Formatting,
change "cell Value Is" to "Formula Is", enter =ISNA(B1), then click Format,
select the Font tab, choose a white font.

HTH