Thank you for the below, but I'm struggling with it. I copied the data as
shown at the MS site and entered your corrected formula FAR below but keep
getting a #VALUE! ERROR.

What am I missing?


"T. Valko" wrote:

When entered as an array formula in d1:d7, it always returns 1.

That formula was not written as a range array. It's supposed to be entered
in a single cell then copied down.

To make it robust against row insertions above the range:

=INDEX(B$1:B$7,SMALL(IF(A$1:A$7=A$10,ROW(B$1:B$7)-MIN(ROW(B$1:B$7))+1),ROWS(B$10:B10)))

To include an efficient error trap:

=IF(ROWS(B$10:B10)<=COUNTIF(A$1:A$7,A$10),INDEX(B$ 1:B$7,SMALL(IF(A$1:A$7=A$10,ROW(B$1:B$7)-MIN(ROW(B$1:B$7))+1),ROWS(A$10:A10))),"")

If you're using Excel 2007 you could use IFERROR but for this particular
formula IFERROR *isn't* more efficient than
ROWS(B$10:B10)<=COUNTIF(A$1:A$7,A$10) when an error is anticipated.

However, when there is no error condition then IFERROR *is* slightly more
efficient.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
The formula at the MS site is wrong It is:
=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1) ) When entered as an
array formula in d1:d7, it always returns 1. The formula should be
=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:7) ) However, that formula
ceases to work properly if a row is inserted before row 1. The formula
should actually be:
=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(indi rect("1:7"))) and the
final formula also does not work if rows are inserted before row 1.

The final formula is which does not work without the first correction is:
=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1 :$A$7)),ROW(1:1)),2)

To work properly the final formula should be:
=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1 :$A$7)-ROW($A$1:$A$1)+1),ROW(INDIRECT("1:7"))),2)

That formula returns the 3 values for Ashish - 234, 534 and 834 in D1, D2,
D3 and #NUM errors in D4, D5, D6, D7 and allows for insertion of rows
before row 1. The original formula with the correction for ROW(1:1) which
is ROW(1:7) returns identical results if there are no rows inserted before
row 1.


Tyro


"Niek Otten" wrote in message
...
Look he

http://office.microsoft.com/en-us/ex...260381033.aspx

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Charlie7805" wrote in message
...
|I have tabs in a workbook for every year, 2003 to 2007. Each sheet is a
list
| of employees who have had an <event during the year. Some have 2 or
more
| <events in a single year.
|
| I want to add a new sheet with a master list of all employees and
insert a
| lookup formula alongside each one to search each sheet returning every
| <event and the date associated with it.
|
| A vlookup will only return one event per employee, per sheet. It will
not
| find multiple <events???
|
| Need some help please.
|
| Thanks.