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