I'm trying to make a worksheet that allows employees to type in their first &
last name and display corresponding data from other workbooks. I have an
INDEX function that does this well, but what I want is for it to display
results that match all of their logins. I tried to use the OR function but I
can't make it work. See example below:

This function does what I want it to do, for one of their logins:

=INDEX('\\Santbdc\Breakroom\Productivity Reports\[last
day.xls]Output'!$A$3:$S$1000,SMALL(IF('\\Santbdc\Breakroom \Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$2,ROW('\\Santbdc\Breakroom \Productivity
Reports\[last day.xls]Output'!$A$3:$A$1000)),ROW(1:1))-2,1)

The problem is that instead of only matching the item in cell T2, I want it
to return all the results that match cells T2, T3, and T4

How can this be done?

P.S. I tried using the OR function like this, but it returns values that
don't match:

=INDEX('\\Santbdc\Breakroom\Productivity Reports\[last
day.xls]Output'!$A$3:$S$1000,SMALL(IF(OR('\\Santbdc\Breakr oom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$2,'\\Santbdc\Breakroom\Pro ductivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$3,'\\Santbdc\Breakroom\Pro ductivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$4),ROW('\\Santbdc\Breakroo m\Productivity
Reports\[last day.xls]Output'!$A$3:$A$1000)),ROW(1:1))-2,1)

Try it like this (I left out the long path to make it easier to read):

=INDEX($A$3:$S$1000,SMALL(IF(ISNUMBER(MATCH($A$3:$ A$1000,$T$2:$T$4,0)),ROW($A$3:$A$1000)),ROWS(A$1:A 1))-2,1)

Why are you indexing A:S? Are you copying this formula across the row?

--
Biff
Microsoft Excel MVP


"John Case" wrote in message
...
I'm trying to make a worksheet that allows employees to type in their
first &
last name and display corresponding data from other workbooks. I have an
INDEX function that does this well, but what I want is for it to display
results that match all of their logins. I tried to use the OR function
but I
can't make it work. See example below:

This function does what I want it to do, for one of their logins:

=INDEX('\\Santbdc\Breakroom\Productivity Reports\[last
day.xls]Output'!$A$3:$S$1000,SMALL(IF('\\Santbdc\Breakroom \Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$2,ROW('\\Santbdc\Breakroom \Productivity
Reports\[last day.xls]Output'!$A$3:$A$1000)),ROW(1:1))-2,1)

The problem is that instead of only matching the item in cell T2, I want
it
to return all the results that match cells T2, T3, and T4

How can this be done?

P.S. I tried using the OR function like this, but it returns values that
don't match:

=INDEX('\\Santbdc\Breakroom\Productivity Reports\[last
day.xls]Output'!$A$3:$S$1000,SMALL(IF(OR('\\Santbdc\Breakr oom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$2,'\\Santbdc\Breakroom\Pro ductivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$3,'\\Santbdc\Breakroom\Pro ductivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$4),ROW('\\Santbdc\Breakroo m\Productivity
Reports\[last day.xls]Output'!$A$3:$A$1000)),ROW(1:1))-2,1)

Yes the values returned show them comprehensive stats. The report shows
their name, market, completes, time on phone, etc. all the way to column S.

I will try the formula you gave me tomorrow, it looks like it is going to
work. I will let you know.

THANKS! This is going to make it much easier for employees to look up their
stats. You're not helping me, you're helping my 500 employees. I am sure
they will be very appreciative of the resulting workbook, and I would be
AMAZED if they realised how much help you guys on these forums are.


"T. Valko" wrote:

Try it like this (I left out the long path to make it easier to read):

=INDEX($A$3:$S$1000,SMALL(IF(ISNUMBER(MATCH($A$3:$ A$1000,$T$2:$T$4,0)),ROW($A$3:$A$1000)),ROWS(A$1:A 1))-2,1)

Why are you indexing A:S? Are you copying this formula across the row?

--
Biff
Microsoft Excel MVP


"John Case" wrote in message
...
I'm trying to make a worksheet that allows employees to type in their
first &
last name and display corresponding data from other workbooks. I have an
INDEX function that does this well, but what I want is for it to display
results that match all of their logins. I tried to use the OR function
but I
can't make it work. See example below:

This function does what I want it to do, for one of their logins:

=INDEX('\\Santbdc\Breakroom\Productivity Reports\[last
day.xls]Output'!$A$3:$S$1000,SMALL(IF('\\Santbdc\Breakroom \Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$2,ROW('\\Santbdc\Breakroom \Productivity
Reports\[last day.xls]Output'!$A$3:$A$1000)),ROW(1:1))-2,1)

The problem is that instead of only matching the item in cell T2, I want
it
to return all the results that match cells T2, T3, and T4

How can this be done?

P.S. I tried using the OR function like this, but it returns values that
don't match:

=INDEX('\\Santbdc\Breakroom\Productivity Reports\[last
day.xls]Output'!$A$3:$S$1000,SMALL(IF(OR('\\Santbdc\Breakr oom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$2,'\\Santbdc\Breakroom\Pro ductivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$3,'\\Santbdc\Breakroom\Pro ductivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$4),ROW('\\Santbdc\Breakroo m\Productivity
Reports\[last day.xls]Output'!$A$3:$A$1000)),ROW(1:1))-2,1)

I will try the formula you gave me tomorrow, it looks like
it is going to work. I will let you know.

I'll keep an eye on this thread.


--
Biff
Microsoft Excel MVP


"John Case" wrote in message
...
Yes the values returned show them comprehensive stats. The report shows
their name, market, completes, time on phone, etc. all the way to column
S.

I will try the formula you gave me tomorrow, it looks like it is going to
work. I will let you know.

THANKS! This is going to make it much easier for employees to look up
their
stats. You're not helping me, you're helping my 500 employees. I am sure
they will be very appreciative of the resulting workbook, and I would be
AMAZED if they realised how much help you guys on these forums are.


"T. Valko" wrote:

Try it like this (I left out the long path to make it easier to read):

=INDEX($A$3:$S$1000,SMALL(IF(ISNUMBER(MATCH($A$3:$ A$1000,$T$2:$T$4,0)),ROW($A$3:$A$1000)),ROWS(A$1:A 1))-2,1)

Why are you indexing A:S? Are you copying this formula across the row?

--
Biff
Microsoft Excel MVP


"John Case" wrote in message
...
I'm trying to make a worksheet that allows employees to type in their
first &
last name and display corresponding data from other workbooks. I have
an
INDEX function that does this well, but what I want is for it to
display
results that match all of their logins. I tried to use the OR function
but I
can't make it work. See example below:

This function does what I want it to do, for one of their logins:

=INDEX('\\Santbdc\Breakroom\Productivity Reports\[last
day.xls]Output'!$A$3:$S$1000,SMALL(IF('\\Santbdc\Breakroom \Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$2,ROW('\\Santbdc\Breakroom \Productivity
Reports\[last day.xls]Output'!$A$3:$A$1000)),ROW(1:1))-2,1)

The problem is that instead of only matching the item in cell T2, I
want
it
to return all the results that match cells T2, T3, and T4

How can this be done?

P.S. I tried using the OR function like this, but it returns values
that
don't match:

=INDEX('\\Santbdc\Breakroom\Productivity Reports\[last
day.xls]Output'!$A$3:$S$1000,SMALL(IF(OR('\\Santbdc\Breakr oom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$2,'\\Santbdc\Breakroom\Pro ductivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$3,'\\Santbdc\Breakroom\Pro ductivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$4),ROW('\\Santbdc\Breakroo m\Productivity
Reports\[last day.xls]Output'!$A$3:$A$1000)),ROW(1:1))-2,1)

This does what I want it to, THANKS!!

"T. Valko" wrote:

I will try the formula you gave me tomorrow, it looks like
it is going to work. I will let you know.

I'll keep an eye on this thread.


--
Biff
Microsoft Excel MVP


"John Case" wrote in message
...
Yes the values returned show them comprehensive stats. The report shows
their name, market, completes, time on phone, etc. all the way to column
S.

I will try the formula you gave me tomorrow, it looks like it is going to
work. I will let you know.

THANKS! This is going to make it much easier for employees to look up
their
stats. You're not helping me, you're helping my 500 employees. I am sure
they will be very appreciative of the resulting workbook, and I would be
AMAZED if they realised how much help you guys on these forums are.


"T. Valko" wrote:

Try it like this (I left out the long path to make it easier to read):

=INDEX($A$3:$S$1000,SMALL(IF(ISNUMBER(MATCH($A$3:$ A$1000,$T$2:$T$4,0)),ROW($A$3:$A$1000)),ROWS(A$1:A 1))-2,1)

Why are you indexing A:S? Are you copying this formula across the row?

--
Biff
Microsoft Excel MVP


"John Case" wrote in message
...
I'm trying to make a worksheet that allows employees to type in their
first &
last name and display corresponding data from other workbooks. I have
an
INDEX function that does this well, but what I want is for it to
display
results that match all of their logins. I tried to use the OR function
but I
can't make it work. See example below:

This function does what I want it to do, for one of their logins:

=INDEX('\\Santbdc\Breakroom\Productivity Reports\[last
day.xls]Output'!$A$3:$S$1000,SMALL(IF('\\Santbdc\Breakroom \Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$2,ROW('\\Santbdc\Breakroom \Productivity
Reports\[last day.xls]Output'!$A$3:$A$1000)),ROW(1:1))-2,1)

The problem is that instead of only matching the item in cell T2, I
want
it
to return all the results that match cells T2, T3, and T4

How can this be done?

P.S. I tried using the OR function like this, but it returns values
that
don't match:

=INDEX('\\Santbdc\Breakroom\Productivity Reports\[last
day.xls]Output'!$A$3:$S$1000,SMALL(IF(OR('\\Santbdc\Breakr oom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$2,'\\Santbdc\Breakroom\Pro ductivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$3,'\\Santbdc\Breakroom\Pro ductivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$4),ROW('\\Santbdc\Breakroo m\Productivity
Reports\[last day.xls]Output'!$A$3:$A$1000)),ROW(1:1))-2,1)

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"John Case" wrote in message
...
This does what I want it to, THANKS!!

"T. Valko" wrote:

I will try the formula you gave me tomorrow, it looks like
it is going to work. I will let you know.

I'll keep an eye on this thread.


--
Biff
Microsoft Excel MVP


"John Case" wrote in message
...
Yes the values returned show them comprehensive stats. The report
shows
their name, market, completes, time on phone, etc. all the way to
column
S.

I will try the formula you gave me tomorrow, it looks like it is going
to
work. I will let you know.

THANKS! This is going to make it much easier for employees to look up
their
stats. You're not helping me, you're helping my 500 employees. I am
sure
they will be very appreciative of the resulting workbook, and I would
be
AMAZED if they realised how much help you guys on these forums are.


"T. Valko" wrote:

Try it like this (I left out the long path to make it easier to read):

=INDEX($A$3:$S$1000,SMALL(IF(ISNUMBER(MATCH($A$3:$ A$1000,$T$2:$T$4,0)),ROW($A$3:$A$1000)),ROWS(A$1:A 1))-2,1)

Why are you indexing A:S? Are you copying this formula across the row?

--
Biff
Microsoft Excel MVP


"John Case" wrote in message
...
I'm trying to make a worksheet that allows employees to type in
their
first &
last name and display corresponding data from other workbooks. I
have
an
INDEX function that does this well, but what I want is for it to
display
results that match all of their logins. I tried to use the OR
function
but I
can't make it work. See example below:

This function does what I want it to do, for one of their logins:

=INDEX('\\Santbdc\Breakroom\Productivity Reports\[last
day.xls]Output'!$A$3:$S$1000,SMALL(IF('\\Santbdc\Breakroom \Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$2,ROW('\\Santbdc\Breakroom \Productivity
Reports\[last day.xls]Output'!$A$3:$A$1000)),ROW(1:1))-2,1)

The problem is that instead of only matching the item in cell T2, I
want
it
to return all the results that match cells T2, T3, and T4

How can this be done?

P.S. I tried using the OR function like this, but it returns values
that
don't match:

=INDEX('\\Santbdc\Breakroom\Productivity Reports\[last
day.xls]Output'!$A$3:$S$1000,SMALL(IF(OR('\\Santbdc\Breakr oom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$2,'\\Santbdc\Breakroom\Pro ductivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$3,'\\Santbdc\Breakroom\Pro ductivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$4),ROW('\\Santbdc\Breakroo m\Productivity
Reports\[last day.xls]Output'!$A$3:$A$1000)),ROW(1:1))-2,1)