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!

The basic formula is something like this:

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

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

Then copy down.

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

Need more specific details to offer a more robust suggestion.

Biff

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

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

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?

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

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
...
this formula works if the sheet is sorted by the value i'm looking up and
if
there are no duplicates in the field I want returned. Otherwise i get
either
incorrect values returned or errors..

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

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



"Biff" wrote:

Hi!

The basic formula is something like this:

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

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

Then copy down.

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

Need more specific details to offer a more robust suggestion.

Biff

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

This is 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
...
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?

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

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

Hi Biff,

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

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

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

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

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

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

This is what I want the result to look like.

1010 12
5
7
1020 22
13
1030 10

Can you help, please?



"Biff" wrote:

Hi!

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

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

Post the *EXACT* formula that you tried.

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

Pivot table or filter

Biff

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

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

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



"Biff" wrote:

Hi!

The basic formula is something like this:

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

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

Then copy down.

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

Need more specific details to offer a more robust suggestion.

Biff

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

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

I can get the result to look like this:

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

Do you want to go with that?

--
Biff
Microsoft Excel MVP


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

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

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

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

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

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

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

This is what I want the result to look like.

1010 12
5
7
1020 22
13
1030 10

Can you help, please?



"Biff" wrote:

Hi!

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

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

Post the *EXACT* formula that you tried.

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

Pivot table or filter

Biff

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

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

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



"Biff" wrote:

Hi!

The basic formula is something like this:

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

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

Then copy down.

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

Need more specific details to offer a more robust suggestion.

Biff

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

Hello,

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

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

Regards,
Bernd

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?

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?

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!

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?

Awesome, I have been searching for this... How do i mod it for to return a -
etc if there is an error. Such as it has checked the list and returned
everything but i have a defined range.

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

How about providing some specific details about what you're wanting to do.

--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
Awesome, I have been searching for this... How do i mod it for to return
a -
etc if there is an error. Such as it has checked the list and returned
everything but i have a defined range.

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

When I drag it down and fill in the cells, i get #NUM as it cannot locate any
more matches. How do i ISERROR that out to return a "-" after it meets the
end threshold.

"T. Valko" wrote:

How about providing some specific details about what you're wanting to do.

--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
Awesome, I have been searching for this... How do i mod it for to return
a -
etc if there is an error. Such as it has checked the list and returned
everything but i have a defined range.

"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 generic method is like this:

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

However, that's not very efficient or robust. If you provide some details we
can come up with something that's better.

--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
When I drag it down and fill in the cells, i get #NUM as it cannot locate
any
more matches. How do i ISERROR that out to return a "-" after it meets the
end threshold.

"T. Valko" wrote:

How about providing some specific details about what you're wanting to
do.

--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
Awesome, I have been searching for this... How do i mod it for to
return
a -
etc if there is an error. Such as it has checked the list and returned
everything but i have a defined range.

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





.

is there a way to modify this formula to match 2 values and return multiple
corresponding values? i need column a = x and column b = y and then return
results.

"Biff" wrote:

Hi!

The basic formula is something like this:

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

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

Then copy down.

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

Need more specific details to offer a more robust suggestion.

Biff

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

i need column a = x and column b = y and then return results.

Return what results? From where?

--
Biff
Microsoft Excel MVP


"eec" wrote in message
...
is there a way to modify this formula to match 2 values and return
multiple
corresponding values? i need column a = x and column b = y and then
return
results.

"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 for the lack of detail. i am pulling info from one worksheet with all
of our invoice information and trying to make a report that only shows the
invoices that match 2 criteria. so if a row of data contains both x and y
then i want it to show on the report. which for now is just a separate tab
in the same workbook. from the previous posts in this string i can pull data
that matches one criteria i just don't know how or if i can expand it to
match two criteria. thanks

"T. Valko" wrote:

i need column a = x and column b = y and then return results.

Return what results? From where?

--
Biff
Microsoft Excel MVP


"eec" wrote in message
...
is there a way to modify this formula to match 2 values and return
multiple
corresponding values? i need column a = x and column b = y and then
return
results.

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





.

Here's a small sample file that demonstrates this.

EEC.xls 19kb

http://cjoint.com/?cdr7UrLcDS

--
Biff
Microsoft Excel MVP


"eec" wrote in message
...
sorry for the lack of detail. i am pulling info from one worksheet with
all
of our invoice information and trying to make a report that only shows the
invoices that match 2 criteria. so if a row of data contains both x and y
then i want it to show on the report. which for now is just a separate
tab
in the same workbook. from the previous posts in this string i can pull
data
that matches one criteria i just don't know how or if i can expand it to
match two criteria. thanks

"T. Valko" wrote:

i need column a = x and column b = y and then return results.

Return what results? From where?

--
Biff
Microsoft Excel MVP


"eec" wrote in message
...
is there a way to modify this formula to match 2 values and return
multiple
corresponding values? i need column a = x and column b = y and then
return
results.

"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 can use the INDEX and MATCH functions together to return multiple corresponding values. Here's how:

1. First, select the cell where you want to display the first corresponding value.
2. Type the following formula:
   Formula:

   =INDEX(range,MATCH(lookup_value,lookup_range,0)) 

   - Replace "range" with the range of cells that contains the values you want to return.
   - Replace "lookup_value" with the value you want to look up (in this case, the name you're searching for).
   - Replace "lookup_range" with the range of cells that contains the names you're searching for.
3. Press Enter to display the first corresponding value.
4. To display the next corresponding value, select the cell below the first corresponding value and type the same formula, but change the "MATCH" function to search for the next occurrence of the lookup value.
   - For example, if the first formula was
   Formula:

   =INDEX(range,MATCH("John",lookup_range,0)) 

   , the second formula would be
   Formula:

   =INDEX(range,MATCH("John",lookup_range,MATCH(lookup_value,lookup_range,0)+1)) 

5. Repeat step 4 for each additional corresponding value you want to display.

Note: If there are no more corresponding values to display, the formula will return an error. To avoid this, you can use the IFERROR function to display a blank cell instead of an error message.