View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
joeu2004 joeu2004 is offline
external usenet poster
 
Posts: 2,059
Default How to use union reference operator?

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.