Why doesn't the union reference operator work below?

Suppose J24:J28 contains 11,20,31,43,53, and K24:K28 contains the text
grp1,grp2,grp3,grp4,grp5.

And suppose A1 contains a value = 11, e.g. 24. I can use the following
formula to convert A1 to a category in K24:K28:

=index(K24:K28,match(A1,J24:J28))

But now suppose that I want to convert A1 to every other category, as if the
lookup ranges contained 11,31,53 and grp1,grp3,grp5.

Based on the help page "about calculation operators", I thought I could
write:

=index((K24,K26,K28),match(A1,(J24,J26,J28))

But that returns an error (#N/A). Why doesn't the union reference operator
work as intended?

Please do not try to provide alternative formulations to solve the
particular example. It is only an example. I am just trying to understand
the union reference operator.

hi, Joe !

(first:) what (exactly) the content of [A1] is ?
(second:) what (exactly) do you expect as a "valid" result ?

*IF* you DON'T want alternative formulations... try this:

- change the match part of your intended formulation (i.e.) to this: =match({11;31;53},j24:j28)
- you will get *only* 1 (one), but now...
- press {F2} + {F9} and you will see in the formula-bar: - ={1;3;5}

- now, "complete" your formulation (outer index) for this one: =index(k24:k28,match({11;31;53},j24:j28))
- you will get *only* the first *matching-index"... - grp1

- now... press {F1} and look for help on functions index and match (and the use/meaning of their arguments)

hth,
hector.

__ OP __
Why doesn't the union reference operator work below?
Suppose J24:J28 contains 11,20,31,43,53, and K24:K28 contains the text grp1,grp2,grp3,grp4,grp5.
And suppose A1 contains a value = 11, e.g. 24.
I can use the following formula to convert A1 to a category in K24:K28:
=index(K24:K28,match(A1,J24:J28))
But now suppose that I want to convert A1 to every other category
as if the lookup ranges contained 11,31,53 and grp1,grp3,grp5.
Based on the help page "about calculation operators", I thought I could write:
=index((K24,K26,K28),match(A1,(J24,J26,J28))
But that returns an error (#N/A).
Why doesn't the union reference operator work as intended?

Please do not try to provide alternative formulations to solve the particular example. It is only an example.
I am just trying to understand the union reference operator.

Very few functions accept that type of referencing. Off the top of my head:

SUM
MIN
MAX
SMALL
LARGE
FREQUENCY

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
Why doesn't the union reference operator work below?

Suppose J24:J28 contains 11,20,31,43,53, and K24:K28 contains the text
grp1,grp2,grp3,grp4,grp5.

And suppose A1 contains a value = 11, e.g. 24. I can use the following
formula to convert A1 to a category in K24:K28:

=index(K24:K28,match(A1,J24:J28))

But now suppose that I want to convert A1 to every other category, as if
the lookup ranges contained 11,31,53 and grp1,grp3,grp5.

Based on the help page "about calculation operators", I thought I could
write:

=index((K24,K26,K28),match(A1,(J24,J26,J28))

But that returns an error (#N/A). Why doesn't the union reference
operator work as intended?

Please do not try to provide alternative formulations to solve the
particular example. It is only an example. I am just trying to
understand the union reference operator.

"T. Valko" wrote:
Very few functions accept that type of referencing.

As I suspected. Thanks for the confirmation.


----- original message -----

"T. Valko" wrote in message
...
Very few functions accept that type of referencing. Off the top of my
head:

SUM
MIN
MAX
SMALL
LARGE
FREQUENCY

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
Why doesn't the union reference operator work below?

Suppose J24:J28 contains 11,20,31,43,53, and K24:K28 contains the text
grp1,grp2,grp3,grp4,grp5.

And suppose A1 contains a value = 11, e.g. 24. I can use the following
formula to convert A1 to a category in K24:K28:

=index(K24:K28,match(A1,J24:J28))

But now suppose that I want to convert A1 to every other category, as if
the lookup ranges contained 11,31,53 and grp1,grp3,grp5.

Based on the help page "about calculation operators", I thought I could
write:

=index((K24,K26,K28),match(A1,(J24,J26,J28))

But that returns an error (#N/A). Why doesn't the union reference
operator work as intended?

Please do not try to provide alternative formulations to solve the
particular example. It is only an example. I am just trying to
understand the union reference operator.

You're welcome!

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"T. Valko" wrote:
Very few functions accept that type of referencing.

As I suspected. Thanks for the confirmation.


----- original message -----

"T. Valko" wrote in message
...
Very few functions accept that type of referencing. Off the top of my
head:

SUM
MIN
MAX
SMALL
LARGE
FREQUENCY

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
Why doesn't the union reference operator work below?

Suppose J24:J28 contains 11,20,31,43,53, and K24:K28 contains the text
grp1,grp2,grp3,grp4,grp5.

And suppose A1 contains a value = 11, e.g. 24. I can use the following
formula to convert A1 to a category in K24:K28:

=index(K24:K28,match(A1,J24:J28))

But now suppose that I want to convert A1 to every other category, as if
the lookup ranges contained 11,31,53 and grp1,grp3,grp5.

Based on the help page "about calculation operators", I thought I could
write:

=index((K24,K26,K28),match(A1,(J24,J26,J28))

But that returns an error (#N/A). Why doesn't the union reference
operator work as intended?

Please do not try to provide alternative formulations to solve the
particular example. It is only an example. I am just trying to
understand the union reference operator.