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.

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?

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

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

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?

One more question please....

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

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


"Biff" wrote:

You're welcome!

Biff

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

"Biff" wrote:

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

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

Biff

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



"Biff" wrote:

Hi!

The basic formula is something like this:

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

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

Then copy down.

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

Need more specific details to offer a more robust suggestion.

Biff

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

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

Use this:

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

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

What do you mean?

Biff

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

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

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


"Biff" wrote:

You're welcome!

Biff

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

"Biff" wrote:

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

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

Biff

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



"Biff" wrote:

Hi!

The basic formula is something like this:

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

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

Then copy down.

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

Need more specific details to offer a more robust suggestion.

Biff

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

I would like to copy down and do this for all the values in the list not just
the value from A60

"Biff" wrote:

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

Use this:

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

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

What do you mean?

Biff

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

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

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


"Biff" wrote:

You're welcome!

Biff

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

"Biff" wrote:

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

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

Biff

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



"Biff" wrote:

Hi!

The basic formula is something like this:

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

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

Then copy down.

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

Need more specific details to offer a more robust suggestion.

Biff

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

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

Biff

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

"Biff" wrote:

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

Use this:

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

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

What do you mean?

Biff

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

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

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


"Biff" wrote:

You're welcome!

Biff

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

"Biff" wrote:

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

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

Biff

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



"Biff" wrote:

Hi!

The basic formula is something like this:

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

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

Then copy down.

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

Need more specific details to offer a more robust suggestion.

Biff

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

Do you have code to do this dynamic? and loop inside of another llop?

"Biff" wrote:

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

Use this:

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

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

What do you mean?

Biff

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

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

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


"Biff" wrote:

You're welcome!

Biff

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

"Biff" wrote:

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

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

Biff

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



"Biff" wrote:

Hi!

The basic formula is something like this:

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

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

Then copy down.

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

Need more specific details to offer a more robust suggestion.

Biff

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

Why don't you send me your file so I can see what your trying to do!

Let me know how to contact you.

Biff

"Debi H" wrote in message
...
Do you have code to do this dynamic? and loop inside of another llop?

"Biff" wrote:

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

Use this:

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

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

What do you mean?

Biff

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

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

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


"Biff" wrote:

You're welcome!

Biff

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

"Biff" wrote:

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

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

Biff

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



"Biff" wrote:

Hi!

The basic formula is something like this:

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

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

Then copy down.

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

Need more specific details to offer a more robust suggestion.

Biff

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

you can email me at this weekend or call my mobile
864-320-5503. I will sent you the file if you send me your email address.

"Biff" wrote:

Why don't you send me your file so I can see what your trying to do!

Let me know how to contact you.

Biff

"Debi H" wrote in message
...
Do you have code to do this dynamic? and loop inside of another llop?

"Biff" wrote:

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

Use this:

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

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

What do you mean?

Biff

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

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

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


"Biff" wrote:

You're welcome!

Biff

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

"Biff" wrote:

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

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

Biff

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



"Biff" wrote:

Hi!

The basic formula is something like this:

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

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

Then copy down.

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

Need more specific details to offer a more robust suggestion.

Biff

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

did you get the speadsheet I sent?

"Biff" wrote:

Why don't you send me your file so I can see what your trying to do!

Let me know how to contact you.

Biff

"Debi H" wrote in message
...
Do you have code to do this dynamic? and loop inside of another llop?

"Biff" wrote:

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

Use this:

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

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

What do you mean?

Biff

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

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

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


"Biff" wrote:

You're welcome!

Biff

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

"Biff" wrote:

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

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

Biff

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



"Biff" wrote:

Hi!

The basic formula is something like this:

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

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

Then copy down.

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

Need more specific details to offer a more robust suggestion.

Biff

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

did you get the speadsheet I sent?

Yes, I'll take a look at it tonight.

Biff

"Debi H" wrote in message
...
did you get the speadsheet I sent?

"Biff" wrote:

Why don't you send me your file so I can see what your trying to do!

Let me know how to contact you.

Biff

"Debi H" wrote in message
...
Do you have code to do this dynamic? and loop inside of another llop?

"Biff" wrote:

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

Use this:

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

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

What do you mean?

Biff

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

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

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


"Biff" wrote:

You're welcome!

Biff

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

"Biff" wrote:

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

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

Biff

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



"Biff" wrote:

Hi!

The basic formula is something like this:

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

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

Then copy down.

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

Need more specific details to offer a more robust suggestion.

Biff

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

Thanks.. you can send it to if it is before 6am tomorrow
morning or to the bme email after 6am.

Thanks

"Biff" wrote:

did you get the speadsheet I sent?

Yes, I'll take a look at it tonight.

Biff

"Debi H" wrote in message
...
did you get the speadsheet I sent?

"Biff" wrote:

Why don't you send me your file so I can see what your trying to do!

Let me know how to contact you.

Biff

"Debi H" wrote in message
...
Do you have code to do this dynamic? and loop inside of another llop?

"Biff" wrote:

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

Use this:

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

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

What do you mean?

Biff

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

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

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


"Biff" wrote:

You're welcome!

Biff

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

"Biff" wrote:

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

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

Biff

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



"Biff" wrote:

Hi!

The basic formula is something like this:

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

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

Then copy down.

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

Need more specific details to offer a more robust suggestion.

Biff

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

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?

biff -

you nailed it kid. got the result i was looking for in terms of being able
to have a variable length array. here's my next question:

lets assume my formula reads as follows:

=INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

....and my data looks like this:

a(rng2) b(rng1) c (rng4)
1 y1 p1 5
2 x1 p2 1
3 y1 p3 0
4 z1 p4 3
5 q1 p5 4

if my $z$1 value is "y1"...the formula returns 2 data sets:

y1 p1
y1 p3

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

can you help me?

thanks in advance.

g



"Biff" wrote:

You can use dynamic ranges that automatically adjust as the size of your
data set changes.

See instructions he

http://contextures.com/xlNames01.html#Dynamic

Then the formula would be something like this: (array entered)

=INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
Biff,

I'm using your formula:

=INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1)))

My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be named
ranges with variable amounts of data? I export data out of our accounting
and want to use the formula, but the number of data points changes. It
seems
that the function only works if all the cells between $b$1 and $b$190 have
data. Am i missing something?

thanks in advance,

greg

"Biff" wrote:

Hi!

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

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

Post the *EXACT* formula that you tried.

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

Pivot table or filter

Biff

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

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

Try this: (array entered)

=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
biff -

you nailed it kid. got the result i was looking for in terms of being
able
to have a variable length array. here's my next question:

lets assume my formula reads as follows:

=INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

...and my data looks like this:

a(rng2) b(rng1) c (rng4)
1 y1 p1 5
2 x1 p2 1
3 y1 p3 0
4 z1 p4 3
5 q1 p5 4

if my $z$1 value is "y1"...the formula returns 2 data sets:

y1 p1
y1 p3

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

can you help me?

thanks in advance.

g



"Biff" wrote:

You can use dynamic ranges that automatically adjust as the size of your
data set changes.

See instructions he

http://contextures.com/xlNames01.html#Dynamic

Then the formula would be something like this: (array entered)

=INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
Biff,

I'm using your formula:

=INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1)))

My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be
named
ranges with variable amounts of data? I export data out of our
accounting
and want to use the formula, but the number of data points changes. It
seems
that the function only works if all the cells between $b$1 and $b$190
have
data. Am i missing something?

thanks in advance,

greg

"Biff" wrote:

Hi!

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

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

Post the *EXACT* formula that you tried.

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

Pivot table or filter

Biff

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

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

=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

...here is my exact formula (Array entered)

=INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO
Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO
Detail'!B18)))

its still bringing back values from range 2 if range 3 is 0. in the
formula, it looks like were mutliplying the rng2 valule by the corresponding
value in rng3, however the rng2 value is not a number. it is in most cases
text. not sure if that is the problem. maybe we can just check to see if
rng3 is 0? i tried this, but syntax wasn't right. any thoughts?

thanks in advance,

g


"Biff" wrote:

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

Try this: (array entered)

=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
biff -

you nailed it kid. got the result i was looking for in terms of being
able
to have a variable length array. here's my next question:

lets assume my formula reads as follows:

=INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

...and my data looks like this:

a(rng2) b(rng1) c (rng4)
1 y1 p1 5
2 x1 p2 1
3 y1 p3 0
4 z1 p4 3
5 q1 p5 4

if my $z$1 value is "y1"...the formula returns 2 data sets:

y1 p1
y1 p3

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

can you help me?

thanks in advance.

g



"Biff" wrote:

You can use dynamic ranges that automatically adjust as the size of your
data set changes.

See instructions he

http://contextures.com/xlNames01.html#Dynamic

Then the formula would be something like this: (array entered)

=INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
Biff,

I'm using your formula:

=INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1)))

My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be
named
ranges with variable amounts of data? I export data out of our
accounting
and want to use the formula, but the number of data points changes. It
seems
that the function only works if all the cells between $b$1 and $b$190
have
data. Am i missing something?

thanks in advance,

greg

"Biff" wrote:

Hi!

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

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

Post the *EXACT* formula that you tried.

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

Pivot table or filter

Biff

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

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)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
biff -

you nailed it kid. got the result i was looking for in terms of being
able
to have a variable length array. here's my next question:

lets assume my formula reads as follows:

=INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

...and my data looks like this:

a(rng2) b(rng1) c (rng4)
1 y1 p1 5
2 x1 p2 1
3 y1 p3 0
4 z1 p4 3
5 q1 p5 4

if my $z$1 value is "y1"...the formula returns 2 data sets:

y1 p1
y1 p3

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

can you help me?

thanks in advance.

g



"Biff" wrote:

You can use dynamic ranges that automatically adjust as the size of your
data set changes.

See instructions he

http://contextures.com/xlNames01.html#Dynamic

Then the formula would be something like this: (array entered)

=INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
Biff,

I'm using your formula:

=INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1)))

My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be
named
ranges with variable amounts of data? I export data out of our
accounting
and want to use the formula, but the number of data points changes. It
seems
that the function only works if all the cells between $b$1 and $b$190
have
data. Am i missing something?

thanks in advance,

greg

"Biff" wrote:

Hi!

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

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

Post the *EXACT* formula that you tried.

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

Pivot table or filter

Biff

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

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

=INDEX(Rng2,SMALL(IF((Rng1=$Z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

this is my exact formula (array entered):

=INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO
Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO
Detail'!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)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
biff -

you nailed it kid. got the result i was looking for in terms of being
able
to have a variable length array. here's my next question:

lets assume my formula reads as follows:

=INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

...and my data looks like this:

a(rng2) b(rng1) c (rng4)
1 y1 p1 5
2 x1 p2 1
3 y1 p3 0
4 z1 p4 3
5 q1 p5 4

if my $z$1 value is "y1"...the formula returns 2 data sets:

y1 p1
y1 p3

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

can you help me?

thanks in advance.

g



"Biff" wrote:

You can use dynamic ranges that automatically adjust as the size of your
data set changes.

See instructions he

http://contextures.com/xlNames01.html#Dynamic

Then the formula would be something like this: (array entered)

=INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
Biff,

I'm using your formula:

=INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1)))

My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be
named
ranges with variable amounts of data? I export data out of our
accounting
and want to use the formula, but the number of data points changes. It
seems
that the function only works if all the cells between $b$1 and $b$190
have
data. Am i missing something?

thanks in advance,

greg

"Biff" wrote:

Hi!

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

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

Post the *EXACT* formula that you tried.

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

Pivot table or filter

Biff

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

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" wrote in message
...
Morning Biff. Thanks for the reply. Getting and error. Here's what
you
recommended:

=INDEX(Rng2,SMALL(IF((Rng1=$Z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

this is my exact formula (array entered):

=INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO
Detail'!B$1:B$500=$B$3)*('PO
Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO
Detail'!B$1:B$500)))),ROW('PO
Detail'!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)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
biff -

you nailed it kid. got the result i was looking for in terms of
being
able
to have a variable length array. here's my next question:

lets assume my formula reads as follows:

=INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

...and my data looks like this:

a(rng2) b(rng1) c (rng4)
1 y1 p1 5
2 x1 p2 1
3 y1 p3 0
4 z1 p4 3
5 q1 p5 4

if my $z$1 value is "y1"...the formula returns 2 data sets:

y1 p1
y1 p3

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

can you help me?

thanks in advance.

g



"Biff" wrote:

You can use dynamic ranges that automatically adjust as the size
of your
data set changes.

See instructions he

http://contextures.com/xlNames01.html#Dynamic

Then the formula would be something like this: (array entered)

=INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
Biff,

I'm using your formula:

=INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1)))

My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190
be
named
ranges with variable amounts of data? I export data out of our
accounting
and want to use the formula, but the number of data points
changes. It
seems
that the function only works if all the cells between $b$1 and
$b$190
have
data. Am i missing something?

thanks in advance,

greg

"Biff" wrote:

Hi!

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

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

Post the *EXACT* formula that you tried.

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

Pivot table or filter

Biff

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

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:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO
Detail'!G$1:G$5000),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" wrote in message
...
Morning Biff. Thanks for the reply. Getting and error. Here's what
you
recommended:

=INDEX(Rng2,SMALL(IF((Rng1=$Z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

this is my exact formula (array entered):

=INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO
Detail'!B$1:B$500=$B$3)*('PO
Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO
Detail'!B$1:B$500)))),ROW('PO
Detail'!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)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
biff -

you nailed it kid. got the result i was looking for in terms of
being
able
to have a variable length array. here's my next question:

lets assume my formula reads as follows:

=INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

...and my data looks like this:

a(rng2) b(rng1) c (rng4)
1 y1 p1 5
2 x1 p2 1
3 y1 p3 0
4 z1 p4 3
5 q1 p5 4

if my $z$1 value is "y1"...the formula returns 2 data sets:

y1 p1
y1 p3

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

can you help me?

thanks in advance.

g



"Biff" wrote:

You can use dynamic ranges that automatically adjust as the size
of your
data set changes.

See instructions he

http://contextures.com/xlNames01.html#Dynamic

Then the formula would be something like this: (array entered)

=INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
Biff,

I'm using your formula:

=INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1)))

My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190
be
named
ranges with variable amounts of data? I export data out of our
accounting
and want to use the formula, but the number of data points
changes. It
seems
that the function only works if all the cells between $b$1 and
$b$190
have
data. Am i missing something?

thanks in advance,

greg

"Biff" wrote:

Hi!

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

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

Post the *EXACT* formula that you tried.

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

Pivot table or filter

Biff

"MetricsShiva" wrote
in
message
...
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 posted
=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

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

You therefore seem to have

=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng1)))),ROW(A1)))

^^^^^
This should not affect the result as it is only a way of getting an
array of results corresponding with the size of your table.
It certainly doesn't affect results with the original sample of data you
posted, which works perfectly with Biff's formula using rng1 or rng2
I don't know what your full set of data contains. I presume $B$3 does
contain the value you are looking for.
It should work OK.

Did you use Ctrl+Shift+Enter to make it an array formula when you
amended?
You say you are getting an error, what error is it coming up with?


--
Regards

Roger Govier


"gfactor" wrote in message
...
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:D$500,SMALL(IF(('PO
Detail'!B$1:B$500=$B$3)*('PO
Detail'!G$1:G$5000),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" wrote in message
...
Morning Biff. Thanks for the reply. Getting and error. Here's
what
you
recommended:

=INDEX(Rng2,SMALL(IF((Rng1=$Z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

this is my exact formula (array entered):

=INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO
Detail'!B$1:B$500=$B$3)*('PO
Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO
Detail'!B$1:B$500)))),ROW('PO
Detail'!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)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
biff -

you nailed it kid. got the result i was looking for in terms of
being
able
to have a variable length array. here's my next question:

lets assume my formula reads as follows:

=INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

...and my data looks like this:

a(rng2) b(rng1) c (rng4)
1 y1 p1 5
2 x1 p2 1
3 y1 p3 0
4 z1 p4 3
5 q1 p5 4

if my $z$1 value is "y1"...the formula returns 2 data sets:

y1 p1
y1 p3

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

can you help me?

thanks in advance.

g



"Biff" wrote:

You can use dynamic ranges that automatically adjust as the
size
of your
data set changes.

See instructions he

http://contextures.com/xlNames01.html#Dynamic

Then the formula would be something like this: (array entered)

=INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
Biff,

I'm using your formula:

=INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1)))

My question is this: Can the ranges $B$1:$B$190 and
A$1:$A$190
be
named
ranges with variable amounts of data? I export data out of
our
accounting
and want to use the formula, but the number of data points
changes. It
seems
that the function only works if all the cells between $b$1
and
$b$190
have
data. Am i missing something?

thanks in advance,

greg

"Biff" wrote:

Hi!

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

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

Post the *EXACT* formula that you tried.

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

Pivot table or filter

Biff

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

in the formula, it looks like were mutliplying the rng2
valule by the corresponding value in rng3, however
the rng2 value is not a number. it is in most cases
text. not sure if that is the problem.

No, it's not a problem. It's not actually multiplying numbers. It's
multiplying logical tests that return either TRUE or FALSE. For example:

('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000)

This will return an array like this:

(TRUE)*(TRUE)
(TRUE)*(FALSE)
(FALSE)*(TRUE)
(FALSE)*(FALSE)

These logicals are multiplied together and result in an array of 1's or 0's
where 1 evalautes to the value_if_true and 0 evaluates to the
value_if_false. The value_if_true is a number from 1 to 500 that's derived
from this expression:

ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500))

When the value_if_true condition is met then the corresponding numbers from
that expression are then passed to the Small function.

..here is my exact formula (Array entered)
=INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO
Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO
Detail'!B$1:B$500)))),ROW('PO
Detail'!B18)))

If you're not using dynamic named ranges then I would "dummy down" this
portion:

ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500))

To:

ROW($1:$500)

You don't actually need the sheet name or the cell references but you'll see
where some people use them just for a better understanding.

Biff

"gfactor" wrote in message
...
biff,

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

=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

..here is my exact formula (Array entered)

=INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO
Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO
Detail'!B$1:B$500)))),ROW('PO
Detail'!B18)))

its still bringing back values from range 2 if range 3 is 0. in the
formula, it looks like were mutliplying the rng2 valule by the
corresponding
value in rng3, however the rng2 value is not a number. it is in most
cases
text. not sure if that is the problem. maybe we can just check to see
if
rng3 is 0? i tried this, but syntax wasn't right. any thoughts?

thanks in advance,

g


"Biff" wrote:

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

Try this: (array entered)

=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
biff -

you nailed it kid. got the result i was looking for in terms of being
able
to have a variable length array. here's my next question:

lets assume my formula reads as follows:

=INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

...and my data looks like this:

a(rng2) b(rng1) c (rng4)
1 y1 p1 5
2 x1 p2 1
3 y1 p3 0
4 z1 p4 3
5 q1 p5 4

if my $z$1 value is "y1"...the formula returns 2 data sets:

y1 p1
y1 p3

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

can you help me?

thanks in advance.

g



"Biff" wrote:

You can use dynamic ranges that automatically adjust as the size of
your
data set changes.

See instructions he

http://contextures.com/xlNames01.html#Dynamic

Then the formula would be something like this: (array entered)

=INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
Biff,

I'm using your formula:

=INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1)))

My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be
named
ranges with variable amounts of data? I export data out of our
accounting
and want to use the formula, but the number of data points changes.
It
seems
that the function only works if all the cells between $b$1 and
$b$190
have
data. Am i missing something?

thanks in advance,

greg

"Biff" wrote:

Hi!

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

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

Post the *EXACT* formula that you tried.

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

Pivot table or filter

Biff

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

Looked at some of you responses regarding excel and you seem very
knowledgeable. Have a question involving a lookup.



Scenario

Two Sheets



Sheet one

Has an invoice number on it.





Sheet two

Has a check number that may or may not have paid multiple invoices.

It is listed this way.



Column 1 column 2 column 3
et all

Check number invoice number invoice number







What I would like to do is use a lookup on sheet one that checks sheet two
for the invoice number (separate invoice numbers are located in columns B
through whatever) and returns what check it was paid on (checks located in
the left most column).







Cant figure out an easy way to do it and was wondering if you had any ideas.





Regards,

jb

"Biff" wrote:

You can use dynamic ranges that automatically adjust as the size of your
data set changes.

See instructions he

http://contextures.com/xlNames01.html#Dynamic

Then the formula would be something like this: (array entered)

=INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
Biff,

I'm using your formula:

=INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1)))

My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be named
ranges with variable amounts of data? I export data out of our accounting
and want to use the formula, but the number of data points changes. It
seems
that the function only works if all the cells between $b$1 and $b$190 have
data. Am i missing something?

thanks in advance,

greg

"Biff" wrote:

Hi!

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

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

Post the *EXACT* formula that you tried.

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

Pivot table or filter

Biff

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

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

Can you help me?

"Biff" wrote:

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

Biff

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

"Biff" wrote:

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

Use this:

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

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

What do you mean?

Biff

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

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

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


"Biff" wrote:

You're welcome!

Biff

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

"Biff" wrote:

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

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

Biff

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



"Biff" wrote:

Hi!

The basic formula is something like this:

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

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

Then copy down.

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

Need more specific details to offer a more robust suggestion.

Biff

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

You need to be more specific and provide some details.

--
Biff
Microsoft Excel MVP


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

Can you help me?

"Biff" wrote:

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

Biff

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

"Biff" wrote:

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

Use this:

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

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

What do you mean?

Biff

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

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

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


"Biff" wrote:

You're welcome!

Biff

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

"Biff" wrote:

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

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

Biff

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



"Biff" wrote:

Hi!

The basic formula is something like this:

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

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

Then copy down.

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

Need more specific details to offer a more robust suggestion.

Biff

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

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

"T. Valko" wrote:

You need to be more specific and provide some details.

--
Biff
Microsoft Excel MVP


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

Can you help me?

"Biff" wrote:

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

Biff

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

"Biff" wrote:

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

Use this:

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

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

What do you mean?

Biff

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

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

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


"Biff" wrote:

You're welcome!

Biff

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

"Biff" wrote:

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

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

Biff

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



"Biff" wrote:

Hi!

The basic formula is something like this:

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

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

Then copy down.

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

Need more specific details to offer a more robust suggestion.

Biff

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

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

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

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

Are you interested in this?

--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

You need to be more specific and provide some details.

--
Biff
Microsoft Excel MVP


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

Can you help me?

"Biff" wrote:

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

Biff

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

"Biff" wrote:

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

Use this:

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

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

What do you mean?

Biff

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

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

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


"Biff" wrote:

You're welcome!

Biff

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

"Biff" wrote:

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

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

Biff

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



"Biff" wrote:

Hi!

The basic formula is something like this:

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

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

Then copy down.

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

Need more specific details to offer a more robust
suggestion.

Biff

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