how to return mulitple corresponding values

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

Try this: (array entered)

=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng3>0),ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))

Biff

"gfactor" <gfactor DeleteThis @discussions.microsoft.com> wrote in message
news:0BC1D732-CEB4-4347-A0C5-178C6204E252@microsoft.com...
> 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:"&ROWS(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 here:
>>
>> 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:"&ROWS(Rng2)))),ROW(A1)))
>>
>> Biff
>>
>> "gfactor" <gfactor DeleteThis @discussions.microsoft.com> wrote in message
>> news:646EC043-0E9A-49B9-BBA8-CBAB1309FD02@microsoft.com...
>> > 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" <MetricsShiva DeleteThis @discussions.microsoft.com> wrote in
>> >> message
>> >> news:DAC4D500-E098-42F4-BE13-C8CCCCC92563@microsoft.com...
>> >> > 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" <MetricsShiva DeleteThis @discussions.microsoft.com> wrote in
>> >> >> message
>> >> >> news:7CB57B18-A2B0-43D2-A726-6650586A3B94@microsoft.com...
>> >> >> >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?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

test

"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)*(Rng3>0),ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))
>
> Biff
>
> "gfactor" <gfactor.DeleteThis@discussions.microsoft.com> wrote in message
> news:0BC1D732-CEB4-4347-A0C5-178C6204E252@microsoft.com...
> > 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:"&ROWS(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 here:
> >>
> >> 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:"&ROWS(Rng2)))),ROW(A1)))
> >>
> >> Biff
> >>
> >> "gfactor" <gfactor.DeleteThis@discussions.microsoft.com> wrote in message
> >> news:646EC043-0E9A-49B9-BBA8-CBAB1309FD02@microsoft.com...
> >> > 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" <MetricsShiva.DeleteThis@discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:DAC4D500-E098-42F4-BE13-C8CCCCC92563@microsoft.com...
> >> >> > 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" <MetricsShiva.DeleteThis@discussions.microsoft.com> wrote in
> >> >> >> message
> >> >> >> news:7CB57B18-A2B0-43D2-A726-6650586A3B94@microsoft.com...
> >> >> >> >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?
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

biff,

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

=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng3>0),ROW(INDIRECT("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$500>0),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)*(Rng3>0),ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))
>
> Biff
>
> "gfactor" <gfactor.TakeThisOut@discussions.microsoft.com> wrote in message
> news:0BC1D732-CEB4-4347-A0C5-178C6204E252@microsoft.com...
> > 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:"&ROWS(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 here:
> >>
> >> 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:"&ROWS(Rng2)))),ROW(A1)))
> >>
> >> Biff
> >>
> >> "gfactor" <gfactor.TakeThisOut@discussions.microsoft.com> wrote in message
> >> news:646EC043-0E9A-49B9-BBA8-CBAB1309FD02@microsoft.com...
> >> > 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" <MetricsShiva.TakeThisOut@discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:DAC4D500-E098-42F4-BE13-C8CCCCC92563@microsoft.com...
> >> >> > 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" <MetricsShiva.TakeThisOut@discussions.microsoft.com> wrote in
> >> >> >> message
> >> >> >> news:7CB57B18-A2B0-43D2-A726-6650586A3B94@microsoft.com...
> >> >> >> >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?
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Morning Biff. Thanks for the reply. Getting and error. Here's what you
recommended:

=INDEX(Rng2,SMALL(IF((Rng1=$Z$1)*(Rng3>0),ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))

this 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$500>0),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO
Detail'!B87)))

it looks like we're mutiplying rng3 x corresponding value in rng1. one of
the issues is rng1 is text. is there a way to just check to see if rng3 is
greater than "0". i tried, but now luck.

any thoughts?

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)*(Rng3>0),ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))
>
> Biff
>
> "gfactor" <gfactor DeleteThis @discussions.microsoft.com> wrote in message
> news:0BC1D732-CEB4-4347-A0C5-178C6204E252@microsoft.com...
> > 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:"&ROWS(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 here:
> >>
> >> 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:"&ROWS(Rng2)))),ROW(A1)))
> >>
> >> Biff
> >>
> >> "gfactor" <gfactor DeleteThis @discussions.microsoft.com> wrote in message
> >> news:646EC043-0E9A-49B9-BBA8-CBAB1309FD02@microsoft.com...
> >> > 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" <MetricsShiva DeleteThis @discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:DAC4D500-E098-42F4-BE13-C8CCCCC92563@microsoft.com...
> >> >> > 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" <MetricsShiva DeleteThis @discussions.microsoft.com> wrote in
> >> >> >> message
> >> >> >> news:7CB57B18-A2B0-43D2-A726-6650586A3B94@microsoft.com...
> >> >> >> >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

Your problem lies in the last part of the formula.
The argument for the SMALL() function that Biff proposed was ROW(A1)
which would be 1, but would be stepped up to 2, 3 etc. as you copied
down.

In this posting you are using ROW('PO Detail'!B87) and in your previous
posting ROW('PO Detail'!B87)
Change to ROW(A1) and it should work.

(N.B. It doesn't need to have any sheet reference, as it is not being
used to refer to any particular cell, it is just a method of getting the
smallest, 2nd smallest etc.)


--
Regards

Roger Govier


"gfactor" <gfactor DeleteThis @discussions.microsoft.com> wrote in message
news:CB7BDE1C-9E1F-4AE6-B0B0-CA5C3D4421B0@microsoft.com...
> Morning Biff. Thanks for the reply. Getting and error. Here's what
> you
> recommended:
>
> =INDEX(Rng2,SMALL(IF((Rng1=$Z$1)*(Rng3>0),ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))
>
> this 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$500>0),ROW(INDIRECT("1:"&ROWS('PO
> Detail'!B$1:B$500)))),ROW('PO
> Detail'!B87)))
>
> it looks like we're mutiplying rng3 x corresponding value in rng1.
> one of
> the issues is rng1 is text. is there a way to just check to see if
> rng3 is
> greater than "0". i tried, but now luck.
>
> any thoughts?
>
> 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)*(Rng3>0),ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))
>>
>> Biff
>>
>> "gfactor" <gfactor DeleteThis @discussions.microsoft.com> wrote in message
>> news:0BC1D732-CEB4-4347-A0C5-178C6204E252@microsoft.com...
>> > 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:"&ROWS(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 here:
>> >>
>> >> 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:"&ROWS(Rng2)))),ROW(A1)))
>> >>
>> >> Biff
>> >>
>> >> "gfactor" <gfactor DeleteThis @discussions.microsoft.com> wrote in message
>> >> news:646EC043-0E9A-49B9-BBA8-CBAB1309FD02@microsoft.com...
>> >> > 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" <MetricsShiva DeleteThis @discussions.microsoft.com> wrote
>> >> >> in
>> >> >> message
>> >> >> news:DAC4D500-E098-42F4-BE13-C8CCCCC92563@microsoft.com...
>> >> >> > 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" <MetricsShiva DeleteThis @discussions.microsoft.com>
>> >> >> >> wrote in
>> >> >> >> message
>> >> >> >> news:7CB57B18-A2B0-43D2-A726-6650586A3B94@microsoft.com...
>> >> >> >> >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 for the input roger. the b87 ref was due to the fact that i was
copying the formula from the 87th row in my list. however it did set it back
to a1 and i'm showing below the formula from the 1st row in my range.

however i am still getting results of items for which the value in G$1:G$500
corresponding to rng2 is >0.

=INDEX('PO Detail'!D$1$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO
Detail'!G$1:G$500>0),ROW(INDIRECT("1:"&ROWS('PO
Detail'!B$1:B$500)))),ROW(A1)))

thanks in advance for any help.

g

"Roger Govier" wrote:

> Hi
>
> Your problem lies in the last part of the formula.
> The argument for the SMALL() function that Biff proposed was ROW(A1)
> which would be 1, but would be stepped up to 2, 3 etc. as you copied
> down.
>
> In this posting you are using ROW('PO Detail'!B87) and in your previous
> posting ROW('PO Detail'!B87)
> Change to ROW(A1) and it should work.
>
> (N.B. It doesn't need to have any sheet reference, as it is not being
> used to refer to any particular cell, it is just a method of getting the
> smallest, 2nd smallest etc.)
>
>
> --
> Regards
>
> Roger Govier
>
>
> "gfactor" <gfactor.DeleteThis@discussions.microsoft.com> wrote in message
> news:CB7BDE1C-9E1F-4AE6-B0B0-CA5C3D4421B0@microsoft.com...
> > Morning Biff. Thanks for the reply. Getting and error. Here's what
> > you
> > recommended:
> >
> > =INDEX(Rng2,SMALL(IF((Rng1=$Z$1)*(Rng3>0),ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))
> >
> > this 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$500>0),ROW(INDIRECT("1:"&ROWS('PO
> > Detail'!B$1:B$500)))),ROW('PO
> > Detail'!B87)))
> >
> > it looks like we're mutiplying rng3 x corresponding value in rng1.
> > one of
> > the issues is rng1 is text. is there a way to just check to see if
> > rng3 is
> > greater than "0". i tried, but now luck.
> >
> > any thoughts?
> >
> > 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)*(Rng3>0),ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))
> >>
> >> Biff
> >>
> >> "gfactor" <gfactor.DeleteThis@discussions.microsoft.com> wrote in message
> >> news:0BC1D732-CEB4-4347-A0C5-178C6204E252@microsoft.com...
> >> > 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:"&ROWS(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 here:
> >> >>
> >> >> 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:"&ROWS(Rng2)))),ROW(A1)))
> >> >>
> >> >> Biff
> >> >>
> >> >> "gfactor" <gfactor.DeleteThis@discussions.microsoft.com> wrote in message
> >> >> news:646EC043-0E9A-49B9-BBA8-CBAB1309FD02@microsoft.com...
> >> >> > 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" <MetricsShiva.DeleteThis@discussions.microsoft.com> wrote
> >> >> >> in
> >> >> >> message
> >> >> >> news:DAC4D500-E098-42F4-BE13-C8CCCCC92563@microsoft.com...
> >> >> >> > 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" <MetricsShiva.DeleteThis@discussions.microsoft.com>
> >> >> >> >> wrote in
> >> >> >> >> message
> >> >> >> >> news:7CB57B18-A2B0-43D2-A726-6650586A3B94@microsoft.com...
> >> >> >> >> >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?
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>