How to use union reference operator?

[JoeU2004]

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.

 

[Héctor Miguel]

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 __

 

[Héctor Miguel]

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 __

 

[T. Valko]

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

SUM
MIN
MAX
SMALL
LARGE
FREQUENCY

 

[T. Valko]

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

SUM
MIN
MAX
SMALL
LARGE
FREQUENCY

 

[JoeU2004]

Very few functions accept that type of referencing.

As I suspected. Thanks for the confirmation.


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

 

[JoeU2004]

Very few functions accept that type of referencing.

As I suspected. Thanks for the confirmation.


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

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

As I suspected. Thanks for the confirmation.


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

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

As I suspected. Thanks for the confirmation.


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