A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

how to return mulitple corresponding values



 
 
Thread Tools Display Modes
  #1  
Old September 8th, 2005, 06:25 PM
MetricsShiva
external usenet poster
 
Posts: n/a
Default how to return mulitple corresponding values

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?
  #2  
Old September 8th, 2005, 07:54 PM
Biff
external usenet poster
 
Posts: n/a
Default

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?



  #3  
Old September 8th, 2005, 08:31 PM
MetricsShiva
external usenet poster
 
Posts: n/a
Default

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?




  #4  
Old September 8th, 2005, 09:28 PM
Alan Beban
external usenet poster
 
Posts: n/a
Default

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_c olumn)

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,R OW($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?




  #5  
Old September 8th, 2005, 09:48 PM
Biff
external usenet poster
 
Posts: n/a
Default

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?






  #6  
Old September 8th, 2005, 10:17 PM
MetricsShiva
external usenet poster
 
Posts: n/a
Default

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






  #7  
Old September 9th, 2005, 02:30 AM
Biff
external usenet poster
 
Posts: n/a
Default

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 he

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. Whe

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" wrote in message
...
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" 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?








  #8  
Old July 13th, 2006, 09:29 PM posted to microsoft.public.excel.worksheet.functions
Debi H
external usenet poster
 
Posts: 1
Default how to return mulitple corresponding 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 he

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. Whe

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" wrote in message
...
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" 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?









  #9  
Old July 13th, 2006, 10:02 PM posted to microsoft.public.excel.worksheet.functions
Biff
external usenet poster
 
Posts: 1,559
Default how to return mulitple corresponding values

So:
ROW('Cancel Push compiled'!$A$2:$A$82)
should be written as:
ROW('Cancel Push compiled'!$A$1:$A$81)


Or, it could be written as:

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

This method seems to be less confusing (??).

The posting date of the original thread is almost a year old. What took you
so long the reply? g

Biff

"Debi H" Debi wrote in message
...
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 he

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. Whe

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" wrote in message
...
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" 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?











  #10  
Old July 13th, 2006, 10:09 PM posted to microsoft.public.excel.worksheet.functions
Debi H
external usenet poster
 
Posts: 8
Default how to return mulitple corresponding values

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?




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Return Single Instance of Numeric Values from a Column Sam via OfficeKB.com Worksheet Functions 4 August 26th, 2005 03:10 AM
return all values turkey New Users 1 May 5th, 2005 04:27 PM
Using a Vlookup to return values in a data list? rtjeter Worksheet Functions 2 April 26th, 2005 05:56 AM
return random number of values hgrove Worksheet Functions 2 July 9th, 2004 07:54 PM
Search columns and rows for values to return common value Dale Worksheet Functions 2 December 18th, 2003 04:45 PM


All times are GMT +1. The time now is 11:16 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.