how to return mulitple corresponding values

if there are 2 similar dates with different contact person

Ok, it gets complicated!

Here's a small sample file that demonstrates this.

xNikko.xls 17kb

http://cjoint.com/?efhZw8gEFM

--
Biff
Microsoft Excel MVP


"nikko" wrote in message
...
Thanks ... it works perfectly.

Now with the dates returned in ascending order, i'm trying to return the
related activity comments and contact person. however if there are 2
similar
dates with different contact person, the formula does not seen to work ..

desired results
Date Contact Person
25-Mar Peter
25-Mar Alan

formula is returning
Date Contact Person
25-Mar Peter
25-Mar Peter

Formula - (INDEX('Data3-Opp Activities
Info'!$A$1:$J$10000,SMALL(IF('Data3-Opp Activities
Info'!$C$1:$C$10000='Opportunity (3)'!$J$2&H25,ROW('Data3-Opp Activities
Info'!$C$1:$C$10000)),ROW(1:1)),7)))
--
nikko


"T. Valko" wrote:

This will extract the dates in ascending order, oldest to newest, that
meet
the condition.

I'm leaving out the sheet name so be sure to add it to your formula.

Let's assume you want the results starting in cell X1.

Array entered** :

=SMALL(IF(A$1:A$13=J$2,H$1:H$13),ROWS(X$1:X1))

Copy down until you get #NUM! errors meaning all applicable data has been
extracted.

If you want an error trap:

=IF(ROWS(X$1:X1)=COUNTIF(A$1:A$13,J$2),SMALL(IF(A $1:A$13=J$2,H$1:H$13),ROWS(X$1:X1)),"")

Copy down until you get blanks meaning all applicable data has been
extracted.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"nikko" wrote in message
...
the returned values are dates format... & i jus want to return the
dates
from
oldest to newest...

--
nikko


"T. Valko" wrote:

Explain what you're wanting to do.

I know what the formula does but what do you mean by: sorted order
i.e.the
most recent activity showing up first?

That sounds to me like you're looking for dates?


--
Biff
Microsoft Excel MVP


"nikko" wrote in message
...
Sorry .. didnt get wad you mean...
--
nikko


"T. Valko" wrote:

Refresh my memory.

--
Biff
Microsoft Excel MVP


"nikko" wrote in message
...
Hi Biff

Using below formula and its returning the values not in sequence

=INDEX('Data3-Opp Activities Info'!H$1:H$13,SMALL(IF('Data3-Opp
Activities
Info'!A$1:A$13=$J$2,ROW($1:$13)),ROW(1:1)))

is there a way to return the corresponding values in sorted order
i.e.
w
the
most recent activity showing up first?

Thanks you!

--
nikko


"Biff" wrote:

in the formula, it looks like were mutliplying the rng2
valule by the corresponding value in rng3, however
the rng2 value is not a number. it is in most cases
text. not sure if that is the problem.

No, it's not a problem. It's not actually multiplying numbers.
It's
multiplying logical tests that return either TRUE or FALSE. For
example:

('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000)

This will return an array like this:

(TRUE)*(TRUE)
(TRUE)*(FALSE)
(FALSE)*(TRUE)
(FALSE)*(FALSE)

These logicals are multiplied together and result in an array of
1's
or
0's
where 1 evalautes to the value_if_true and 0 evaluates to the
value_if_false. The value_if_true is a number from 1 to 500
that's
derived
from this expression:

ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500))

When the value_if_true condition is met then the corresponding
numbers
from
that expression are then passed to the Small function.

..here is my exact formula (Array entered)
=INDEX('PO Detail'!D$1$500,SMALL(IF(('PO
Detail'!B$1:B$500=$B$3)*('PO
Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO
Detail'!B$1:B$500)))),ROW('PO
Detail'!B18)))

If you're not using dynamic named ranges then I would "dummy
down"
this
portion:

ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500))

To:

ROW($1:$500)

You don't actually need the sheet name or the cell references
but
you'll
see
where some people use them just for a better understanding.

Biff

"gfactor" wrote in message
...
biff,

thanks for your help...getting an error. here is what you
recommended:

=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

..here is my exact formula (Array entered)

=INDEX('PO Detail'!D$1$500,SMALL(IF(('PO
Detail'!B$1:B$500=$B$3)*('PO
Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO
Detail'!B$1:B$500)))),ROW('PO
Detail'!B18)))

its still bringing back values from range 2 if range 3 is 0.
in
the
formula, it looks like were mutliplying the rng2 valule by the
corresponding
value in rng3, however the rng2 value is not a number. it is
in
most
cases
text. not sure if that is the problem. maybe we can just
check
to
see
if
rng3 is 0? i tried this, but syntax wasn't right. any
thoughts?

thanks in advance,

g


"Biff" wrote:

i only want the result delivered if the value in (rng3) is
"0"

Try this: (array entered)

=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in
message
...
biff -

you nailed it kid. got the result i was looking for in
terms
of
being
able
to have a variable length array. here's my next question:

lets assume my formula reads as follows:

=INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

...and my data looks like this:

a(rng2) b(rng1) c (rng4)
1 y1 p1 5
2 x1 p2 1
3 y1 p3 0
4 z1 p4 3
5 q1 p5 4

if my $z$1 value is "y1"...the formula returns 2 data sets:

y1 p1
y1 p3

the thing is, i only want the result delivered if the value
in
(rng3)
is
"0"

can you help me?

thanks in advance.

g



"Biff" wrote:

You can use dynamic ranges that automatically adjust as
the
size
of
your
data set changes.

See instructions he

http://contextures.com/xlNames01.html#Dynamic

Then the formula would be something like this: (array
entered)

=INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in
message
...
Biff,

I'm using your formula:

=INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1)))

My question is this: Can the ranges $B$1:$B$190 and
A$1:$A$190
be
named
ranges with variable amounts of data? I export data out
of
our
accounting
and want to use the formula, but the number of data
points
changes.
It
seems
that the function only works if all the cells between
$b$1
and
$b$190
have
data. Am i missing something?

thanks in advance,

greg

"Biff" wrote:

Hi!

this formula works if the sheet is sorted by the
value
i'm
looking
up
and

Thanks ... works perfectly!!!
--
nikko


"T. Valko" wrote:

if there are 2 similar dates with different contact person

Ok, it gets complicated!

Here's a small sample file that demonstrates this.

xNikko.xls 17kb

http://cjoint.com/?efhZw8gEFM

--
Biff
Microsoft Excel MVP


"nikko" wrote in message
...
Thanks ... it works perfectly.

Now with the dates returned in ascending order, i'm trying to return the
related activity comments and contact person. however if there are 2
similar
dates with different contact person, the formula does not seen to work ..

desired results
Date Contact Person
25-Mar Peter
25-Mar Alan

formula is returning
Date Contact Person
25-Mar Peter
25-Mar Peter

Formula - (INDEX('Data3-Opp Activities
Info'!$A$1:$J$10000,SMALL(IF('Data3-Opp Activities
Info'!$C$1:$C$10000='Opportunity (3)'!$J$2&H25,ROW('Data3-Opp Activities
Info'!$C$1:$C$10000)),ROW(1:1)),7)))
--
nikko


"T. Valko" wrote:

This will extract the dates in ascending order, oldest to newest, that
meet
the condition.

I'm leaving out the sheet name so be sure to add it to your formula.

Let's assume you want the results starting in cell X1.

Array entered** :

=SMALL(IF(A$1:A$13=J$2,H$1:H$13),ROWS(X$1:X1))

Copy down until you get #NUM! errors meaning all applicable data has been
extracted.

If you want an error trap:

=IF(ROWS(X$1:X1)=COUNTIF(A$1:A$13,J$2),SMALL(IF(A $1:A$13=J$2,H$1:H$13),ROWS(X$1:X1)),"")

Copy down until you get blanks meaning all applicable data has been
extracted.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"nikko" wrote in message
...
the returned values are dates format... & i jus want to return the
dates
from
oldest to newest...

--
nikko


"T. Valko" wrote:

Explain what you're wanting to do.

I know what the formula does but what do you mean by: sorted order
i.e.the
most recent activity showing up first?

That sounds to me like you're looking for dates?


--
Biff
Microsoft Excel MVP


"nikko" wrote in message
...
Sorry .. didnt get wad you mean...
--
nikko


"T. Valko" wrote:

Refresh my memory.

--
Biff
Microsoft Excel MVP


"nikko" wrote in message
...
Hi Biff

Using below formula and its returning the values not in sequence

=INDEX('Data3-Opp Activities Info'!H$1:H$13,SMALL(IF('Data3-Opp
Activities
Info'!A$1:A$13=$J$2,ROW($1:$13)),ROW(1:1)))

is there a way to return the corresponding values in sorted order
i.e.
w
the
most recent activity showing up first?

Thanks you!

--
nikko


"Biff" wrote:

in the formula, it looks like were mutliplying the rng2
valule by the corresponding value in rng3, however
the rng2 value is not a number. it is in most cases
text. not sure if that is the problem.

No, it's not a problem. It's not actually multiplying numbers.
It's
multiplying logical tests that return either TRUE or FALSE. For
example:

('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000)

This will return an array like this:

(TRUE)*(TRUE)
(TRUE)*(FALSE)
(FALSE)*(TRUE)
(FALSE)*(FALSE)

These logicals are multiplied together and result in an array of
1's
or
0's
where 1 evalautes to the value_if_true and 0 evaluates to the
value_if_false. The value_if_true is a number from 1 to 500
that's
derived
from this expression:

ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500))

When the value_if_true condition is met then the corresponding
numbers
from
that expression are then passed to the Small function.

..here is my exact formula (Array entered)
=INDEX('PO Detail'!D$1$500,SMALL(IF(('PO
Detail'!B$1:B$500=$B$3)*('PO
Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO
Detail'!B$1:B$500)))),ROW('PO
Detail'!B18)))

If you're not using dynamic named ranges then I would "dummy
down"
this
portion:

ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500))

To:

ROW($1:$500)

You don't actually need the sheet name or the cell references
but
you'll
see
where some people use them just for a better understanding.

Biff

"gfactor" wrote in message
...
biff,

thanks for your help...getting an error. here is what you
recommended:

=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

..here is my exact formula (Array entered)

=INDEX('PO Detail'!D$1$500,SMALL(IF(('PO
Detail'!B$1:B$500=$B$3)*('PO
Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO
Detail'!B$1:B$500)))),ROW('PO
Detail'!B18)))

its still bringing back values from range 2 if range 3 is 0.
in
the
formula, it looks like were mutliplying the rng2 valule by the
corresponding
value in rng3, however the rng2 value is not a number. it is
in
most
cases
text. not sure if that is the problem. maybe we can just
check
to
see
if
rng3 is 0? i tried this, but syntax wasn't right. any
thoughts?

thanks in advance,

g


"Biff" wrote:

i only want the result delivered if the value in (rng3) is
"0"

Try this: (array entered)

=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in
message
...
biff -

you nailed it kid. got the result i was looking for in
terms
of
being
able
to have a variable length array. here's my next question:

lets assume my formula reads as follows:

=INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

...and my data looks like this:

a(rng2) b(rng1) c (rng4)
1 y1 p1 5
2 x1 p2 1
3 y1 p3 0
4 z1 p4 3
5 q1 p5 4

if my $z$1 value is "y1"...the formula returns 2 data sets:

y1 p1
y1 p3

the thing is, i only want the result delivered if the value
in
(rng3)
is
"0"

can you help me?

thanks in advance.

g



"Biff" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"nikko" wrote in message
...
Thanks ... works perfectly!!!
--
nikko


"T. Valko" wrote:

if there are 2 similar dates with different contact person

Ok, it gets complicated!

Here's a small sample file that demonstrates this.

xNikko.xls 17kb

http://cjoint.com/?efhZw8gEFM

--
Biff
Microsoft Excel MVP


"nikko" wrote in message
...
Thanks ... it works perfectly.

Now with the dates returned in ascending order, i'm trying to return
the
related activity comments and contact person. however if there are 2
similar
dates with different contact person, the formula does not seen to work
..

desired results
Date Contact Person
25-Mar Peter
25-Mar Alan

formula is returning
Date Contact Person
25-Mar Peter
25-Mar Peter

Formula - (INDEX('Data3-Opp Activities
Info'!$A$1:$J$10000,SMALL(IF('Data3-Opp Activities
Info'!$C$1:$C$10000='Opportunity (3)'!$J$2&H25,ROW('Data3-Opp
Activities
Info'!$C$1:$C$10000)),ROW(1:1)),7)))
--
nikko


"T. Valko" wrote:

This will extract the dates in ascending order, oldest to newest, that
meet
the condition.

I'm leaving out the sheet name so be sure to add it to your formula.

Let's assume you want the results starting in cell X1.

Array entered** :

=SMALL(IF(A$1:A$13=J$2,H$1:H$13),ROWS(X$1:X1))

Copy down until you get #NUM! errors meaning all applicable data has
been
extracted.

If you want an error trap:

=IF(ROWS(X$1:X1)=COUNTIF(A$1:A$13,J$2),SMALL(IF(A $1:A$13=J$2,H$1:H$13),ROWS(X$1:X1)),"")

Copy down until you get blanks meaning all applicable data has been
extracted.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"nikko" wrote in message
...
the returned values are dates format... & i jus want to return the
dates
from
oldest to newest...

--
nikko


"T. Valko" wrote:

Explain what you're wanting to do.

I know what the formula does but what do you mean by: sorted order
i.e.the
most recent activity showing up first?

That sounds to me like you're looking for dates?


--
Biff
Microsoft Excel MVP


"nikko" wrote in message
...
Sorry .. didnt get wad you mean...
--
nikko


"T. Valko" wrote:

Refresh my memory.

--
Biff
Microsoft Excel MVP


"nikko" wrote in message
...
Hi Biff

Using below formula and its returning the values not in
sequence

=INDEX('Data3-Opp Activities
Info'!H$1:H$13,SMALL(IF('Data3-Opp
Activities
Info'!A$1:A$13=$J$2,ROW($1:$13)),ROW(1:1)))

is there a way to return the corresponding values in sorted
order
i.e.
w
the
most recent activity showing up first?

Thanks you!

--
nikko


"Biff" wrote:

in the formula, it looks like were mutliplying the rng2
valule by the corresponding value in rng3, however
the rng2 value is not a number. it is in most cases
text. not sure if that is the problem.

No, it's not a problem. It's not actually multiplying
numbers.
It's
multiplying logical tests that return either TRUE or FALSE.
For
example:

('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000)

This will return an array like this:

(TRUE)*(TRUE)
(TRUE)*(FALSE)
(FALSE)*(TRUE)
(FALSE)*(FALSE)

These logicals are multiplied together and result in an array
of
1's
or
0's
where 1 evalautes to the value_if_true and 0 evaluates to the
value_if_false. The value_if_true is a number from 1 to 500
that's
derived
from this expression:

ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500))

When the value_if_true condition is met then the
corresponding
numbers
from
that expression are then passed to the Small function.

..here is my exact formula (Array entered)
=INDEX('PO Detail'!D$1$500,SMALL(IF(('PO
Detail'!B$1:B$500=$B$3)*('PO
Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO
Detail'!B$1:B$500)))),ROW('PO
Detail'!B18)))

If you're not using dynamic named ranges then I would "dummy
down"
this
portion:

ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500))

To:

ROW($1:$500)

You don't actually need the sheet name or the cell references
but
you'll
see
where some people use them just for a better understanding.

Biff

"gfactor" wrote in
message
...
biff,

thanks for your help...getting an error. here is what you
recommended:

=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

..here is my exact formula (Array entered)

=INDEX('PO Detail'!D$1$500,SMALL(IF(('PO
Detail'!B$1:B$500=$B$3)*('PO
Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO
Detail'!B$1:B$500)))),ROW('PO
Detail'!B18)))

its still bringing back values from range 2 if range 3 is
0.
in
the
formula, it looks like were mutliplying the rng2 valule by
the
corresponding
value in rng3, however the rng2 value is not a number. it
is
in
most
cases
text. not sure if that is the problem. maybe we can just
check
to
see
if
rng3 is 0? i tried this, but syntax wasn't right. any
thoughts?

thanks in advance,

g


"Biff" wrote:

i only want the result delivered if the value in (rng3)
is
"0"

Try this: (array entered)

=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in
message
...
biff -

you nailed it kid. got the result i was looking for in
terms
of
being
able
to have a variable length array. here's my next
question:

lets assume my formula reads as follows:

=INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

...and my data looks like this:

a(rng2) b(rng1) c (rng4)
1 y1 p1 5
2 x1 p2 1
3 y1 p3 0
4 z1 p4 3
5 q1 p5 4

if my $z$1 value is "y1"...the formula returns 2 data
sets:

y1 p1
y1 p3

the thing is, i only want the result delivered if the
value
in
(rng3)
is
"0"

can you help me?

thanks in advance.

g



"Biff" wrote:

Hi Biff,

I've read quite a few of the posts on returning multiple corresponding
values but everything is basically over my head. I don't have any training or
exp in this stuff. I've tried to replicate the example at:

http://office.microsoft.com/en-us/ex...0551033#Remove

I was able to return only one value for ashish 234. I couldn't get the
formula to return 534 or 834. Here's the formula I have.

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A10,ROW($A$1:$ A$7)),ROW(1:1)),2)

Even if I get this right, I'm not sure if it is what I need. I have a data
set where one value can have multiple corresponding values. It's not a 1:1
relationship. I want all values returned but only the distinct values. Here's
an example of the data.

Acct Line
1010 12
1010 12
1010 12
1010 12
1010 12
1010 5
1010 7
1010 5
1020 22
1020 13
1030 10

This is what I want the result to look like.

1010 12
5
7
1020 22
13
1030 10

Can you help, please?



"Biff" wrote:

Hi!

this formula works if the sheet is sorted by the value i'm looking up and
if
there are no duplicates in the field I want returned.

The sheet does not need to be sorted and it doesn't matter if there are dupe
return values.

Post the *EXACT* formula that you tried.

I then want to include this
in a weekly dashboard for the 50+ managers i'm monitoring.
have you got anything else?

Pivot table or filter

Biff

"MetricsShiva" wrote in message
news
this formula works if the sheet is sorted by the value i'm looking up and
if
there are no duplicates in the field I want returned. Otherwise i get
either
incorrect values returned or errors..

basically, i have a sheet listing jobs scheduled by managers. I want to
be
able to look up the manager's name and return a list of all the job's
scheduled and the dates they were scheduled on. I then want to include
this
in a weekly dashboard for the 50+ managers i'm monitoring.

Thanks for the response, but have you got anything else?



"Biff" wrote:

Hi!

The basic formula is something like this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1)))

Then copy down.

Where column A contains the lookup_value and column B contains the values
to
be returned.

Need more specific details to offer a more robust suggestion.

Biff

"MetricsShiva" wrote in message
...
i want to look up a name that occurs several times in one column of a
spreadsheet and return corresponding values from each row the name
occurs
on.
Vlookup returns only one value. How can I get multiple values?

This is what I want the result to look like.
1010 12
5
7
1020 22
13
1030 10

I can get the result to look like this:

1010...5...7...12
1020...13...22
1030...10

Do you want to go with that?

--
Biff
Microsoft Excel MVP


"jbf" wrote in message
...
Hi Biff,

I've read quite a few of the posts on returning multiple corresponding
values but everything is basically over my head. I don't have any training
or
exp in this stuff. I've tried to replicate the example at:

http://office.microsoft.com/en-us/ex...0551033#Remove

I was able to return only one value for ashish 234. I couldn't get the
formula to return 534 or 834. Here's the formula I have.

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A10,ROW($A$1:$ A$7)),ROW(1:1)),2)

Even if I get this right, I'm not sure if it is what I need. I have a data
set where one value can have multiple corresponding values. It's not a 1:1
relationship. I want all values returned but only the distinct values.
Here's
an example of the data.

Acct Line
1010 12
1010 12
1010 12
1010 12
1010 12
1010 5
1010 7
1010 5
1020 22
1020 13
1030 10

This is what I want the result to look like.

1010 12
5
7
1020 22
13
1030 10

Can you help, please?



"Biff" wrote:

Hi!

this formula works if the sheet is sorted by the value i'm looking up
and
if
there are no duplicates in the field I want returned.

The sheet does not need to be sorted and it doesn't matter if there are
dupe
return values.

Post the *EXACT* formula that you tried.

I then want to include this
in a weekly dashboard for the 50+ managers i'm monitoring.
have you got anything else?

Pivot table or filter

Biff

"MetricsShiva" wrote in message
news
this formula works if the sheet is sorted by the value i'm looking up
and
if
there are no duplicates in the field I want returned. Otherwise i get
either
incorrect values returned or errors..

basically, i have a sheet listing jobs scheduled by managers. I want
to
be
able to look up the manager's name and return a list of all the job's
scheduled and the dates they were scheduled on. I then want to include
this
in a weekly dashboard for the 50+ managers i'm monitoring.

Thanks for the response, but have you got anything else?



"Biff" wrote:

Hi!

The basic formula is something like this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1)))

Then copy down.

Where column A contains the lookup_value and column B contains the
values
to
be returned.

Need more specific details to offer a more robust suggestion.

Biff

"MetricsShiva" wrote in
message
...
i want to look up a name that occurs several times in one column of a
spreadsheet and return corresponding values from each row the name
occurs
on.
Vlookup returns only one value. How can I get multiple values?

Yes...that would be great.

"T. Valko" wrote:

This is what I want the result to look like.
1010 12
5
7
1020 22
13
1030 10

I can get the result to look like this:

1010...5...7...12
1020...13...22
1030...10

Do you want to go with that?

--
Biff
Microsoft Excel MVP


"jbf" wrote in message
...
Hi Biff,

I've read quite a few of the posts on returning multiple corresponding
values but everything is basically over my head. I don't have any training
or
exp in this stuff. I've tried to replicate the example at:

http://office.microsoft.com/en-us/ex...0551033#Remove

I was able to return only one value for ashish 234. I couldn't get the
formula to return 534 or 834. Here's the formula I have.

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A10,ROW($A$1:$ A$7)),ROW(1:1)),2)

Even if I get this right, I'm not sure if it is what I need. I have a data
set where one value can have multiple corresponding values. It's not a 1:1
relationship. I want all values returned but only the distinct values.
Here's
an example of the data.

Acct Line
1010 12
1010 12
1010 12
1010 12
1010 12
1010 5
1010 7
1010 5
1020 22
1020 13
1030 10

This is what I want the result to look like.

1010 12
5
7
1020 22
13
1030 10

Can you help, please?



"Biff" wrote:

Hi!

this formula works if the sheet is sorted by the value i'm looking up
and
if
there are no duplicates in the field I want returned.

The sheet does not need to be sorted and it doesn't matter if there are
dupe
return values.

Post the *EXACT* formula that you tried.

I then want to include this
in a weekly dashboard for the 50+ managers i'm monitoring.
have you got anything else?

Pivot table or filter

Biff

"MetricsShiva" wrote in message
news this formula works if the sheet is sorted by the value i'm looking up
and
if
there are no duplicates in the field I want returned. Otherwise i get
either
incorrect values returned or errors..

basically, i have a sheet listing jobs scheduled by managers. I want
to
be
able to look up the manager's name and return a list of all the job's
scheduled and the dates they were scheduled on. I then want to include
this
in a weekly dashboard for the 50+ managers i'm monitoring.

Thanks for the response, but have you got anything else?



"Biff" wrote:

Hi!

The basic formula is something like this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1)))

Then copy down.

Where column A contains the lookup_value and column B contains the
values
to
be returned.

Need more specific details to offer a more robust suggestion.

Biff

"MetricsShiva" wrote in
message
...
i want to look up a name that occurs several times in one column of a
spreadsheet and return corresponding values from each row the name
occurs
on.
Vlookup returns only one value. How can I get multiple values?

OK....

Assume your data is in the range A2:B12.

Create these named ranges:
InsertNameDefine
Name: Acct
Refers to: =$A$2:$A$12

Name: Line
Refers to: =$B$2:$B$12

List the unique Acct numbers in a range of cells. Assume they're in the
range D24. You can do this several ways: type them in manually, use a
formula, or use the advanced filter to extract the uniques and copy to a new
location.

Enter this array formula** in E2:

=MIN(IF(Acct=D2,Line))

Copy down to D4

Enter this array formula** in F2:

=IF(COLUMNS($E2:F2)=COUNT(1/FREQUENCY(IF(Acct=$D2,Line),Line)),MIN(IF((Acct=$D 2)*(LineE2),Line)),"")

Copy down to F4.

Select the range F2:F4 and copy across until you get a solid column full of
blanks.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

This portion of the formula can be calculation intensive if you have a large
amount of data:

COUNT(1/FREQUENCY(IF(Acct=$D2,Line),Line))

You can place this as a separate formula in a helper cell and then refer to
that cell instead of using it in every formula. That formula returns the
count of unique line numbers per acct. This is used as an error trap so that
when the formula is copied across and as it "runs out of data" it will
return a blank rather than an error.

--
Biff
Microsoft Excel MVP


"jbf" wrote in message
...
Yes...that would be great.

"T. Valko" wrote:

This is what I want the result to look like.
1010 12
5
7
1020 22
13
1030 10

I can get the result to look like this:

1010...5...7...12
1020...13...22
1030...10

Do you want to go with that?

--
Biff
Microsoft Excel MVP


"jbf" wrote in message
...
Hi Biff,

I've read quite a few of the posts on returning multiple corresponding
values but everything is basically over my head. I don't have any
training
or
exp in this stuff. I've tried to replicate the example at:

http://office.microsoft.com/en-us/ex...0551033#Remove

I was able to return only one value for ashish 234. I couldn't get the
formula to return 534 or 834. Here's the formula I have.

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A10,ROW($A$1:$ A$7)),ROW(1:1)),2)

Even if I get this right, I'm not sure if it is what I need. I have a
data
set where one value can have multiple corresponding values. It's not a
1:1
relationship. I want all values returned but only the distinct values.
Here's
an example of the data.

Acct Line
1010 12
1010 12
1010 12
1010 12
1010 12
1010 5
1010 7
1010 5
1020 22
1020 13
1030 10

This is what I want the result to look like.

1010 12
5
7
1020 22
13
1030 10

Can you help, please?



"Biff" wrote:

Hi!

this formula works if the sheet is sorted by the value i'm looking
up
and
if
there are no duplicates in the field I want returned.

The sheet does not need to be sorted and it doesn't matter if there
are
dupe
return values.

Post the *EXACT* formula that you tried.

I then want to include this
in a weekly dashboard for the 50+ managers i'm monitoring.
have you got anything else?

Pivot table or filter

Biff

"MetricsShiva" wrote in
message
news this formula works if the sheet is sorted by the value i'm looking
up
and
if
there are no duplicates in the field I want returned. Otherwise i
get
either
incorrect values returned or errors..

basically, i have a sheet listing jobs scheduled by managers. I
want
to
be
able to look up the manager's name and return a list of all the
job's
scheduled and the dates they were scheduled on. I then want to
include
this
in a weekly dashboard for the 50+ managers i'm monitoring.

Thanks for the response, but have you got anything else?



"Biff" wrote:

Hi!

The basic formula is something like this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1)))

Then copy down.

Where column A contains the lookup_value and column B contains the
values
to
be returned.

Need more specific details to offer a more robust suggestion.

Biff

"MetricsShiva" wrote in
message
...
i want to look up a name that occurs several times in one column
of a
spreadsheet and return corresponding values from each row the
name
occurs
on.
Vlookup returns only one value. How can I get multiple values?

Hello,

Another approach:
Select a range of 6 rows and 2 columns and array-enter:
=Pfreq(A1:A11,B1:B11)

Pfreq you can find he
http://www.sulprobil.com/html/pfreq.html

Regards,
Bernd

Hi Biff,

I am following this thread and use your example. My formula is:
{=INDEX(Customer,SMALL(IF(Revenue*$C$3Criteria,RO W(INDIRECT("1:"&ROWS(Customer)))),ROW(A1)))}

I got what I want, but now I face a new problem. I want those values whose
status is not 'under negotiation'. I tried
{=INDEX(Customer,SMALL(IF((Status"under
negotiation")*(Revenue*$C$3Criteria),ROW(INDIRECT ("1:"&ROWS(Customer)))),ROW(A1)))}

But was return #N/A. Is there anything I can do to rectify this?

Thanks

"Biff" wrote:

You can use dynamic ranges that automatically adjust as the size of your
data set changes.

See instructions he

http://contextures.com/xlNames01.html#Dynamic

Then the formula would be something like this: (array entered)

=INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
Biff,

I'm using your formula:

=INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1)))

My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be named
ranges with variable amounts of data? I export data out of our accounting
and want to use the formula, but the number of data points changes. It
seems
that the function only works if all the cells between $b$1 and $b$190 have
data. Am i missing something?

thanks in advance,

greg

"Biff" wrote:

Hi!

this formula works if the sheet is sorted by the value i'm looking up
and
if
there are no duplicates in the field I want returned.

The sheet does not need to be sorted and it doesn't matter if there are
dupe
return values.

Post the *EXACT* formula that you tried.

I then want to include this
in a weekly dashboard for the 50+ managers i'm monitoring.
have you got anything else?

Pivot table or filter

Biff

"MetricsShiva" wrote in message
news this formula works if the sheet is sorted by the value i'm looking up
and
if
there are no duplicates in the field I want returned. Otherwise i get
either
incorrect values returned or errors..

basically, i have a sheet listing jobs scheduled by managers. I want
to
be
able to look up the manager's name and return a list of all the job's
scheduled and the dates they were scheduled on. I then want to include
this
in a weekly dashboard for the 50+ managers i'm monitoring.

Thanks for the response, but have you got anything else?



"Biff" wrote:

Hi!

The basic formula is something like this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1)))

Then copy down.

Where column A contains the lookup_value and column B contains the
values
to
be returned.

Need more specific details to offer a more robust suggestion.

Biff

"MetricsShiva" wrote in
message
...
i want to look up a name that occurs several times in one column of a
spreadsheet and return corresponding values from each row the name
occurs
on.
Vlookup returns only one value. How can I get multiple values?

I managed to solve the issue. Thanks

"" wrote:

Hi Biff,

I am following this thread and use your example. My formula is:
{=INDEX(Customer,SMALL(IF(Revenue*$C$3Criteria,RO W(INDIRECT("1:"&ROWS(Customer)))),ROW(A1)))}

I got what I want, but now I face a new problem. I want those values whose
status is not 'under negotiation'. I tried
{=INDEX(Customer,SMALL(IF((Status"under
negotiation")*(Revenue*$C$3Criteria),ROW(INDIRECT ("1:"&ROWS(Customer)))),ROW(A1)))}

But was return #N/A. Is there anything I can do to rectify this?

Thanks

"Biff" wrote:

You can use dynamic ranges that automatically adjust as the size of your
data set changes.

See instructions he

http://contextures.com/xlNames01.html#Dynamic

Then the formula would be something like this: (array entered)

=INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
Biff,

I'm using your formula:

=INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1)))

My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be named
ranges with variable amounts of data? I export data out of our accounting
and want to use the formula, but the number of data points changes. It
seems
that the function only works if all the cells between $b$1 and $b$190 have
data. Am i missing something?

thanks in advance,

greg

"Biff" wrote:

Hi!

this formula works if the sheet is sorted by the value i'm looking up
and
if
there are no duplicates in the field I want returned.

The sheet does not need to be sorted and it doesn't matter if there are
dupe
return values.

Post the *EXACT* formula that you tried.

I then want to include this
in a weekly dashboard for the 50+ managers i'm monitoring.
have you got anything else?

Pivot table or filter

Biff

"MetricsShiva" wrote in message
news this formula works if the sheet is sorted by the value i'm looking up
and
if
there are no duplicates in the field I want returned. Otherwise i get
either
incorrect values returned or errors..

basically, i have a sheet listing jobs scheduled by managers. I want
to
be
able to look up the manager's name and return a list of all the job's
scheduled and the dates they were scheduled on. I then want to include
this
in a weekly dashboard for the 50+ managers i'm monitoring.

Thanks for the response, but have you got anything else?



"Biff" wrote:

Hi!

The basic formula is something like this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1)))

Then copy down.

Where column A contains the lookup_value and column B contains the
values
to
be returned.

Need more specific details to offer a more robust suggestion.

Biff

"MetricsShiva" wrote in
message
...
i want to look up a name that occurs several times in one column of a
spreadsheet and return corresponding values from each row the name
occurs
on.
Vlookup returns only one value. How can I get multiple values?