Help with a Lookup formula ?

[Charlie7805]

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.

 

[Niek Otten]

Look here:

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[Tyro]

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(indirect("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

 

[T. Valko]

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.

 

[Charlie7805]

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]

Well, at this point there's not much I can suggest. If you want to upload a
sample file that demonstrates what you're trying to do then someone (me!)
may take a look at it and figure it out. I use this free file hosting site
when I post sample files:

http://translate.google.com/transla...m=1&ct=result&prev=/search?q=cjoint&hl=en&lr=

It's a French site translated to English.

Upload your file then post back with the link to the file. Note the size
limits!