help with INDEX function

J

John Case

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\Breakroom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$2,'\\Santbdc\Breakroom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$3,'\\Santbdc\Breakroom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$4),ROW('\\Santbdc\Breakroom\Productivity
Reports\[last day.xls]Output'!$A$3:$A$1000)),ROW(1:1))-2,1)
 
T

T. Valko

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:A1))-2,1)

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

John Case

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 said:
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:A1))-2,1)

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

--
Biff
Microsoft Excel MVP


John Case said:
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\Breakroom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$2,'\\Santbdc\Breakroom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$3,'\\Santbdc\Breakroom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$4),ROW('\\Santbdc\Breakroom\Productivity
Reports\[last day.xls]Output'!$A$3:$A$1000)),ROW(1:1))-2,1)
Click to expand...
Click to expand...
 
T

T. Valko

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

I'll keep an eye on this thread.


--
Biff
Microsoft Excel MVP


John Case said:
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 said:
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:A1))-2,1)

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

--
Biff
Microsoft Excel MVP


John Case said:
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\Breakroom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$2,'\\Santbdc\Breakroom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$3,'\\Santbdc\Breakroom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$4),ROW('\\Santbdc\Breakroom\Productivity
Reports\[last day.xls]Output'!$A$3:$A$1000)),ROW(1:1))-2,1)
Click to expand...
Click to expand...
Click to expand...
 
J

John Case

This does what I want it to, THANKS!!

T. Valko said:
I will try the formula you gave me tomorrow, it looks like
it is going to work. I will let you know.
Click to expand...

I'll keep an eye on this thread.


--
Biff
Microsoft Excel MVP


John Case said:
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 said:
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:A1))-2,1)

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

--
Biff
Microsoft Excel MVP


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\Breakroom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$2,'\\Santbdc\Breakroom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$3,'\\Santbdc\Breakroom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$4),ROW('\\Santbdc\Breakroom\Productivity
Reports\[last day.xls]Output'!$A$3:$A$1000)),ROW(1:1))-2,1)
Click to expand...
Click to expand...
Click to expand...
 
T

T. Valko

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


John Case said:
This does what I want it to, THANKS!!

T. Valko said:
I will try the formula you gave me tomorrow, it looks like
it is going to work. I will let you know.
Click to expand...

I'll keep an eye on this thread.


--
Biff
Microsoft Excel MVP


John Case said:
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.


:

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:A1))-2,1)

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

--
Biff
Microsoft Excel MVP


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\Breakroom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$2,'\\Santbdc\Breakroom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$3,'\\Santbdc\Breakroom\Productivity
Reports\[last
day.xls]Output'!$A$3:$A$1000=$T$4),ROW('\\Santbdc\Breakroom\Productivity
Reports\[last day.xls]Output'!$A$3:$A$1000)),ROW(1:1))-2,1)
Click to expand...
Click to expand...
Click to expand...
 
Top