how to return mulitple corresponding values

Download and install the free Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Then, based on the sample data you posted...

Assuming this data is in the range A2:B12. You have the unique groups listed
in the range E2:E5.

Enter this array formula** in F2 and copy down to F5:

=SUBSTITUTE(TRIM(MCONCAT(IF(A$2:A$12=E2,B$2:B$12," ")&" "))," ","^")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Melanie" wrote in message
...
Yes, I'm definitely interested. I know in reality that it can't be
unlimited,
but I need at least 10 names appended. I'll also have to figure out how to
move anything over 30 characters to the next line, but first things first.

Can this function handle multiple reoccurring group names (i.e. loop
within
a loop)?

group name
marketing mjagger
marketing rthomas
marketing xbono
accounting rcharles
accounting jbrown
hr jmayer
it jjohnson
it bdylan
it jjoplin
it akiedis
it braitt

output:

mjagger^rthomas^xbono
rcharles^jbrown
jmayer
jjohnson^bdylan^jjoplin^akiedis^braitt

The looping seems to be the limitation I'm running into with the index
function or I'm using it incorrectly.

"T. Valko" wrote:

There can be...unlimited names for a group.

Well, that's not good! You have to narrow down "unlimited".

There is a free add-in available that has a function that will do this
but
the resulting string is *limited* to no more than 255 characters. So,
that
means "tvalko^debih^biff" can't be more than 255 chars.

Are you interested in this?

--
Biff
Microsoft Excel MVP


"Melanie" wrote in message
...
I have reoccuring group names in column A and multiple names (i.e
tvalko,
debih, biff, etc.) in column B for each group. There can be one name or
unlimited names for a group. I want to identify the name s for each
different
group and obtain the list on one line (tvalko^debih^biff) with carats
as
delimeters.

"T. Valko" wrote:

You need to be more specific and provide some details.

--
Biff
Microsoft Excel MVP


"Melanie" wrote in message
...
I'm also trying to do this. I need to lookup a value in one column
that
returns multiple values in the second column, but I want to list it
out
with
"^" between the values. I need to do this dynamically for multiple
lookup
values.

Can you help me?

"Biff" wrote:

You can do that, however, since the formula returns possible
multiple
results for each lookup value you'd have to use another formula
(the
same
one, just change =$A$60 to the next cell reference).

Biff

"Debi H" wrote in message
...
I would like to copy down and do this for all the values in the
list
not
just
the value from A60

"Biff" wrote:

=INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)),ROW(1:1)),5)

Use this:

=INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)-ROW(A$2)+1),ROWS($1:1)),5)

can I change $A$2:$A$15=$A$60 to look up a range?

What do you mean?

Biff

"Debi H" wrote in message
...
One more question please....

If the fromula:
=INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)),ROW(1:1)),5)

can I change $A$2:$A$15=$A$60 to look up a range?


"Biff" wrote:

You're welcome!

Biff

"Debi H" wrote in message
...
That worked...thanks

"Biff" wrote:

Type the formula then, instead of hitting ENTER like you
normally
would,
hold down both the CTRL key AND the SHIFT key then hit
ENTER.
When
done
properly Excel will enclose the formula in squihhly braces
{ }.
You
can't
just type these braces in, you MUST use the key
combination
to
produce
them.

If you're still having problems I'll be glad to look at
your
file
and
see
if
I can figure it out. Just let me know how to contact you.

Biff

"Debi H" wrote in
message
...
That still does not work for me. Am I missing
something?
I did the key stroke of (CTRL+SHIFT+ENTER) 1ST
Entered the formula
and I get the VALUE error



"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?

Thanks! That worked! Although, for some reason, it wouldn't let me reference
the unique list (col E) from a pivot table in another worksheet so I
referenced the unique list using = in the same worksheet and for some reason
that worked(?). I'm just happy it works! I've been working on this on-and-off
for a week.

"T. Valko" wrote:

Download and install the free Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Then, based on the sample data you posted...

Assuming this data is in the range A2:B12. You have the unique groups listed
in the range E2:E5.

Enter this array formula** in F2 and copy down to F5:

=SUBSTITUTE(TRIM(MCONCAT(IF(A$2:A$12=E2,B$2:B$12," ")&" "))," ","^")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Melanie" wrote in message
...
Yes, I'm definitely interested. I know in reality that it can't be
unlimited,
but I need at least 10 names appended. I'll also have to figure out how to
move anything over 30 characters to the next line, but first things first.

Can this function handle multiple reoccurring group names (i.e. loop
within
a loop)?

group name
marketing mjagger
marketing rthomas
marketing xbono
accounting rcharles
accounting jbrown
hr jmayer
it jjohnson
it bdylan
it jjoplin
it akiedis
it braitt

output:

mjagger^rthomas^xbono
rcharles^jbrown
jmayer
jjohnson^bdylan^jjoplin^akiedis^braitt

The looping seems to be the limitation I'm running into with the index
function or I'm using it incorrectly.

"T. Valko" wrote:

There can be...unlimited names for a group.

Well, that's not good! You have to narrow down "unlimited".

There is a free add-in available that has a function that will do this
but
the resulting string is *limited* to no more than 255 characters. So,
that
means "tvalko^debih^biff" can't be more than 255 chars.

Are you interested in this?

--
Biff
Microsoft Excel MVP


"Melanie" wrote in message
...
I have reoccuring group names in column A and multiple names (i.e
tvalko,
debih, biff, etc.) in column B for each group. There can be one name or
unlimited names for a group. I want to identify the name s for each
different
group and obtain the list on one line (tvalko^debih^biff) with carats
as
delimeters.

"T. Valko" wrote:

You need to be more specific and provide some details.

--
Biff
Microsoft Excel MVP


"Melanie" wrote in message
...
I'm also trying to do this. I need to lookup a value in one column
that
returns multiple values in the second column, but I want to list it
out
with
"^" between the values. I need to do this dynamically for multiple
lookup
values.

Can you help me?

"Biff" wrote:

You can do that, however, since the formula returns possible
multiple
results for each lookup value you'd have to use another formula
(the
same
one, just change =$A$60 to the next cell reference).

Biff

"Debi H" wrote in message
...
I would like to copy down and do this for all the values in the
list
not
just
the value from A60

"Biff" wrote:

=INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)),ROW(1:1)),5)

Use this:

=INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)-ROW(A$2)+1),ROWS($1:1)),5)

can I change $A$2:$A$15=$A$60 to look up a range?

What do you mean?

Biff

"Debi H" wrote in message
...
One more question please....

If the fromula:
=INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)),ROW(1:1)),5)

can I change $A$2:$A$15=$A$60 to look up a range?


"Biff" wrote:

You're welcome!

Biff

"Debi H" wrote in message
...
That worked...thanks

"Biff" wrote:

Type the formula then, instead of hitting ENTER like you
normally
would,
hold down both the CTRL key AND the SHIFT key then hit
ENTER.
When
done
properly Excel will enclose the formula in squihhly braces
{ }.
You
can't
just type these braces in, you MUST use the key
combination
to
produce
them.

If you're still having problems I'll be glad to look at
your
file
and
see
if
I can figure it out. Just let me know how to contact you.

Biff

"Debi H" wrote in
message
...
That still does not work for me. Am I missing
something?
I did the key stroke of (CTRL+SHIFT+ENTER) 1ST
Entered the formula
and I get the VALUE error



"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?

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Melanie" wrote in message
...
Thanks! That worked! Although, for some reason, it wouldn't let me
reference
the unique list (col E) from a pivot table in another worksheet so I
referenced the unique list using = in the same worksheet and for some
reason
that worked(?). I'm just happy it works! I've been working on this
on-and-off
for a week.

"T. Valko" wrote:

Download and install the free Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Then, based on the sample data you posted...

Assuming this data is in the range A2:B12. You have the unique groups
listed
in the range E2:E5.

Enter this array formula** in F2 and copy down to F5:

=SUBSTITUTE(TRIM(MCONCAT(IF(A$2:A$12=E2,B$2:B$12," ")&" "))," ","^")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Melanie" wrote in message
...
Yes, I'm definitely interested. I know in reality that it can't be
unlimited,
but I need at least 10 names appended. I'll also have to figure out how
to
move anything over 30 characters to the next line, but first things
first.

Can this function handle multiple reoccurring group names (i.e. loop
within
a loop)?

group name
marketing mjagger
marketing rthomas
marketing xbono
accounting rcharles
accounting jbrown
hr jmayer
it jjohnson
it bdylan
it jjoplin
it akiedis
it braitt

output:

mjagger^rthomas^xbono
rcharles^jbrown
jmayer
jjohnson^bdylan^jjoplin^akiedis^braitt

The looping seems to be the limitation I'm running into with the index
function or I'm using it incorrectly.

"T. Valko" wrote:

There can be...unlimited names for a group.

Well, that's not good! You have to narrow down "unlimited".

There is a free add-in available that has a function that will do this
but
the resulting string is *limited* to no more than 255 characters. So,
that
means "tvalko^debih^biff" can't be more than 255 chars.

Are you interested in this?

--
Biff
Microsoft Excel MVP


"Melanie" wrote in message
...
I have reoccuring group names in column A and multiple names (i.e
tvalko,
debih, biff, etc.) in column B for each group. There can be one name
or
unlimited names for a group. I want to identify the name s for each
different
group and obtain the list on one line (tvalko^debih^biff) with
carats
as
delimeters.

"T. Valko" wrote:

You need to be more specific and provide some details.

--
Biff
Microsoft Excel MVP


"Melanie" wrote in message
...
I'm also trying to do this. I need to lookup a value in one
column
that
returns multiple values in the second column, but I want to list
it
out
with
"^" between the values. I need to do this dynamically for
multiple
lookup
values.

Can you help me?

"Biff" wrote:

You can do that, however, since the formula returns possible
multiple
results for each lookup value you'd have to use another formula
(the
same
one, just change =$A$60 to the next cell reference).

Biff

"Debi H" wrote in message
...
I would like to copy down and do this for all the values in the
list
not
just
the value from A60

"Biff" wrote:

=INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)),ROW(1:1)),5)

Use this:

=INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)-ROW(A$2)+1),ROWS($1:1)),5)

can I change $A$2:$A$15=$A$60 to look up a range?

What do you mean?

Biff

"Debi H" wrote in message
...
One more question please....

If the fromula:
=INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)),ROW(1:1)),5)

can I change $A$2:$A$15=$A$60 to look up a range?


"Biff" wrote:

You're welcome!

Biff

"Debi H" wrote in
message
...
That worked...thanks

"Biff" wrote:

Type the formula then, instead of hitting ENTER like
you
normally
would,
hold down both the CTRL key AND the SHIFT key then hit
ENTER.
When
done
properly Excel will enclose the formula in squihhly
braces
{ }.
You
can't
just type these braces in, you MUST use the key
combination
to
produce
them.

If you're still having problems I'll be glad to look at
your
file
and
see
if
I can figure it out. Just let me know how to contact
you.

Biff

"Debi H" wrote in
message
...
That still does not work for me. Am I missing
something?
I did the key stroke of (CTRL+SHIFT+ENTER) 1ST
Entered the formula
and I get the VALUE error



"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?

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
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?

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
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?

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
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?

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
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?

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
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?

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
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?

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