Help with a Lookup formula ?
 

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.

Help with a Lookup formula ?
 

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.

Help with a Lookup formula ?
 

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.

Help with a Lookup formula ?
 

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.

Help with a Lookup formula ?
 

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.

Help with a Lookup formula ?
 

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/translat...l%3Den%26lr%3D

It's a French site translated to English.

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


--
Biff
Microsoft Excel MVP


"Charlie7805" wrote in message
...
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.