how to return mulitple corresponding 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?