how to return mulitple corresponding values

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.RemoveThis@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?
>
>
>

If the functions in the freely downloadable file at
http://home.pacbell.net are available to your workbook you might
consider something like

=VLookups(lookup_value,lookup_table,return_value_column)

array entered into a column long enough to accommodate the number of
occurrences of lookup_value.

Alan Beban

MetricsShiva wrote:
> 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.RemoveThis@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!

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

Hey Biff, i've got it working now. the first formula below is the one that
works... i removed the row reference numbers in the first reference to the
array...

"=INDEX('Cancel Push compiled'!$A:$W,SMALL(IF('Cancel Push
compiled'!$A$2:$A$82=Sheet1!$A$2,ROW('Cancel Push
compiled'!$A$2:$A$82)),ROW(1:1)),11)"


This is the formula with the row references... i can't understand why this
one doesn't work....

"=INDEX('Cancel Push compiled'!$A2:$W82,SMALL(IF('Cancel Push
compiled'!$A$2:$A$82=Sheet1!$A$2,ROW('Cancel Push
compiled'!$A$2:$A$82)),ROW(1:1)),11)"

Thank you so much!!

"Metrics"


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

>This is the formula with the row references... i can't understand why this
>one doesn't work....

"=INDEX('Cancel Push compiled'!$A2:$W82,SMALL(IF('Cancel Push
compiled'!$A$2:$A$82=Sheet1!$A$2,ROW('Cancel Push
compiled'!$A$2:$A$82)),ROW(1:1)),11)"

The problem is here:

ROW('Cancel Push compiled'!$A$2:$A$82)

The INDEX function is used to hold the array A2:W82. The actual size of this
array is 81 elements. Where:

A2:W2 = element 1
A3:W3 = element 2
A4:W4 = element 3
...
A82:W82 = element 81

The first call to the ROW function is used to specify which element to
return from the INDEXED array. Since the elements in INDEX are "numbered"
starting from 1, so too must the reference used inside the ROW function. If
the the refernces are mismatched the results you get can and will be
incorrect. (unless you have dumb luck on your side!)

So:

ROW('Cancel Push compiled'!$A$2:$A$82)

should be written as:

ROW('Cancel Push compiled'!$A$1:$A$81)

Another thing, you don't need the sheet name or the columns because you're
not actually referencing any physical location. The ROW function is just a
means to return an array of numbers equal to the size of the INDEXED array.

ROW($1:$81)

Here's another way to look at it:

Assume the indexed range was A247:W327. This array STILL contains 81
elements so:

=INDEX(A247:W327,............................ROW($1:$81)...............)

This is usually where people make mistakes with type of formula. Once you
understand how it works, it's a very simple formula.

Biff

"MetricsShiva" <MetricsShiva RemoveThis @discussions.microsoft.com> wrote in message
news:BC679C3B-32AE-4269-BE83-BF4B35AF34F7@microsoft.com...
> Hey Biff, i've got it working now. the first formula below is the one
> that
> works... i removed the row reference numbers in the first reference to the
> array...
>
> "=INDEX('Cancel Push compiled'!$A:$W,SMALL(IF('Cancel Push
> compiled'!$A$2:$A$82=Sheet1!$A$2,ROW('Cancel Push
> compiled'!$A$2:$A$82)),ROW(1:1)),11)"
>
>
> This is the formula with the row references... i can't understand why this
> one doesn't work....
>
> "=INDEX('Cancel Push compiled'!$A2:$W82,SMALL(IF('Cancel Push
> compiled'!$A$2:$A$82=Sheet1!$A$2,ROW('Cancel Push
> compiled'!$A$2:$A$82)),ROW(1:1)),11)"
>
> Thank you so much!!
>
> "Metrics"
>
>
> "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 RemoveThis @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 RemoveThis @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?
>> >>
>> >>
>> >>
>>
>>
>>

I cannot get any of this to work in Excel. I need to lookup a name in Column
A that appears multiple times and bring back each of the values (number) in
Column B.

Please send to email.

"Biff" wrote:

> >This is the formula with the row references... i can't understand why this
> >one doesn't work....
>
> "=INDEX('Cancel Push compiled'!$A2:$W82,SMALL(IF('Cancel Push
> compiled'!$A$2:$A$82=Sheet1!$A$2,ROW('Cancel Push
> compiled'!$A$2:$A$82)),ROW(1:1)),11)"
>
> The problem is here:
>
> ROW('Cancel Push compiled'!$A$2:$A$82)
>
> The INDEX function is used to hold the array A2:W82. The actual size of this
> array is 81 elements. Where:
>
> A2:W2 = element 1
> A3:W3 = element 2
> A4:W4 = element 3
> ...
> A82:W82 = element 81
>
> The first call to the ROW function is used to specify which element to
> return from the INDEXED array. Since the elements in INDEX are "numbered"
> starting from 1, so too must the reference used inside the ROW function. If
> the the refernces are mismatched the results you get can and will be
> incorrect. (unless you have dumb luck on your side!)
>
> So:
>
> ROW('Cancel Push compiled'!$A$2:$A$82)
>
> should be written as:
>
> ROW('Cancel Push compiled'!$A$1:$A$81)
>
> Another thing, you don't need the sheet name or the columns because you're
> not actually referencing any physical location. The ROW function is just a
> means to return an array of numbers equal to the size of the INDEXED array.
>
> ROW($1:$81)
>
> Here's another way to look at it:
>
> Assume the indexed range was A247:W327. This array STILL contains 81
> elements so:
>
> =INDEX(A247:W327,............................ROW($1:$81)...............)
>
> This is usually where people make mistakes with type of formula. Once you
> understand how it works, it's a very simple formula.
>
> Biff
>
> "MetricsShiva" <MetricsShiva.DeleteThis@discussions.microsoft.com> wrote in message
> news:BC679C3B-32AE-4269-BE83-BF4B35AF34F7@microsoft.com...
> > Hey Biff, i've got it working now. the first formula below is the one
> > that
> > works... i removed the row reference numbers in the first reference to the
> > array...
> >
> > "=INDEX('Cancel Push compiled'!$A:$W,SMALL(IF('Cancel Push
> > compiled'!$A$2:$A$82=Sheet1!$A$2,ROW('Cancel Push
> > compiled'!$A$2:$A$82)),ROW(1:1)),11)"
> >
> >
> > This is the formula with the row references... i can't understand why this
> > one doesn't work....
> >
> > "=INDEX('Cancel Push compiled'!$A2:$W82,SMALL(IF('Cancel Push
> > compiled'!$A$2:$A$82=Sheet1!$A$2,ROW('Cancel Push
> > compiled'!$A$2:$A$82)),ROW(1:1)),11)"
> >
> > Thank you so much!!
> >
> > "Metrics"
> >
> >
> > "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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>