I have tried the method written by Ashish Mathur but it is not working I need
assistance with returning multiple values
--
JW

Are you talking about this:

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

I can't even see the entire formula!

When you say it's "not working", what does that mean?

You need to provide some details. Post the formula you are using.

This is usually fairly easy but I would write the formula differently. I use
these types of formulas every day.

Biff

"Jerry" wrote in message
...
I have tried the method written by Ashish Mathur but it is not working I
need
assistance with returning multiple values
--
JW

Oh, now I see the entire formula:

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2))

Where most people have problems with these types of formulas is in this
portion:

ROW($A$1:$A$7)

ROW() is simply a means of generating an array of numbers from 1 to n that
correspond to the total number of elements in the Index function. In the
formula, the Indexed range, B1:B7, holds a total of 7 elements. So,
ROW(A1:A7) generates an array from 1:7.

Here's where people get this messed up:

Assume the Indexed range is B11:B17. That still contains a total of 7
elements so we still need the ROW function to generate an array from 1 to 7.
A lot of people would use this: ROW(B11:B17). However, that would generate
an array from 11:17 and since the Indexed array is from 1:7 the formula
crashes.

The best way to prevent this problem is to subtract the offset of the range
then add 1:

ROW(B11:B17)-ROW(B11)+1

Now that generates the array we need: 1:7

I would write that formula as:

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

Biff

"Biff" wrote in message
...
Are you talking about this:

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

I can't even see the entire formula!

When you say it's "not working", what does that mean?

You need to provide some details. Post the formula you are using.

This is usually fairly easy but I would write the formula differently. I
use these types of formulas every day.

Biff

"Jerry" wrote in message
...
I have tried the method written by Ashish Mathur but it is not working I
need
assistance with returning multiple values
--
JW

Biff,

I thank you for your response however I am getting a !VALUE#. I can forward
the workbook to you so that you can see what I want to do and possibly come
up witht he formula.
--
JW


"Biff" wrote:

Oh, now I see the entire formula:

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2))

Where most people have problems with these types of formulas is in this
portion:

ROW($A$1:$A$7)

ROW() is simply a means of generating an array of numbers from 1 to n that
correspond to the total number of elements in the Index function. In the
formula, the Indexed range, B1:B7, holds a total of 7 elements. So,
ROW(A1:A7) generates an array from 1:7.

Here's where people get this messed up:

Assume the Indexed range is B11:B17. That still contains a total of 7
elements so we still need the ROW function to generate an array from 1 to 7.
A lot of people would use this: ROW(B11:B17). However, that would generate
an array from 11:17 and since the Indexed array is from 1:7 the formula
crashes.

The best way to prevent this problem is to subtract the offset of the range
then add 1:

ROW(B11:B17)-ROW(B11)+1

Now that generates the array we need: 1:7

I would write that formula as:

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

Biff

"Biff" wrote in message
...
Are you talking about this:

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

I can't even see the entire formula!

When you say it's "not working", what does that mean?

You need to provide some details. Post the formula you are using.

This is usually fairly easy but I would write the formula differently. I
use these types of formulas every day.

Biff

"Jerry" wrote in message
...
I have tried the method written by Ashish Mathur but it is not working I
need
assistance with returning multiple values
--
JW

Hi!

Did you enter the formula as an array?

How about posting the details?

Biff

"Jerry" wrote in message
...
Biff,

I thank you for your response however I am getting a !VALUE#. I can
forward
the workbook to you so that you can see what I want to do and possibly
come
up witht he formula.
--
JW


"Biff" wrote:

Oh, now I see the entire formula:

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2))

Where most people have problems with these types of formulas is in this
portion:

ROW($A$1:$A$7)

ROW() is simply a means of generating an array of numbers from 1 to n
that
correspond to the total number of elements in the Index function. In the
formula, the Indexed range, B1:B7, holds a total of 7 elements. So,
ROW(A1:A7) generates an array from 1:7.

Here's where people get this messed up:

Assume the Indexed range is B11:B17. That still contains a total of 7
elements so we still need the ROW function to generate an array from 1 to
7.
A lot of people would use this: ROW(B11:B17). However, that would
generate
an array from 11:17 and since the Indexed array is from 1:7 the formula
crashes.

The best way to prevent this problem is to subtract the offset of the
range
then add 1:

ROW(B11:B17)-ROW(B11)+1

Now that generates the array we need: 1:7

I would write that formula as:

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

Biff

"Biff" wrote in message
...
Are you talking about this:

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

I can't even see the entire formula!

When you say it's "not working", what does that mean?

You need to provide some details. Post the formula you are using.

This is usually fairly easy but I would write the formula differently.
I
use these types of formulas every day.

Biff

"Jerry" wrote in message
...
I have tried the method written by Ashish Mathur but it is not working
I
need
assistance with returning multiple values
--
JW