Issue with INDEX function

users
#1

AllI have created a one dimensional array called CATEGORY.When I do this function: =Index(Category,25,0)I get the contents
      of that index - lets say it is the word "cats"When I do this function =if((index(category,25,0)="cats"),1,0)I
      get FALSE?!!Anyone have any ideas what I am doing wrong?gs

#2

Gregory Smith - Oracle Sparc Verification wrote:

AllI have created a one dimensional array called CATEGORY.When I do this function: =Index(Category,25,0)I get the contents
       of that index - lets say it is the word "cats"When I do this function =if((index(category,25,0)="cats"),1,0)I
       get FALSE?!!Anyone have any ideas what I am doing wrong?gs

With that formula, you should get 0 if the strings don't match (or 1 if they do); do you really get FALSE displayed in the cell?

Assuming you actually get 0 (because the condition (index(category,25,0)="cats") is evaluating to FALSE), make sure there are no spaces in the values. "cats " is not equal to "cats", even though they look the same. If spurious spaces are likely to be a problem, you may want to use:
   =if((trim(index(category,25,0))="cats"),1,0)
to remove spaces either side of the value (and collapse multiple spaces within the value into single spaces) before comparison.

Mark.

#3

As has already been suggested, the most likely problem is that the two strings do not exactly match, with one perhaps including one or more blanks. You'd notice a stray blank in the second formula, but you might not notice it in the result of the first.

There are some other things you might want to note about your second formula, in fact - although they will *not* be the cause of your present problem.

o You do not need to parenthesise the logical expression, so
=IF((INDEX(Category,25,0)="cats"),1,0)
simplifies to
=IF(INDEX(Category,25,0)="cats",1,0)

o You say you get FALSE as the result, which implies that your result cell(s) must be formatted as Boolean Value. The second and third parameters in your expression are simply TRUE and FALSE expressed as numbers, and since these are the obvious default values of optional parameters, they can be omitted:
=IF(INDEX(Category,25,0)="cats")

o In any case, what you apparently want is simply the value of the logical expression that is the first parameter, so there is no need for the IF() function at all. Just use:
=INDEX(Category,25,0)="cats"

I trust this helps.

Brian Barker

#4

The trim function did the trick.  I have been
      burned by this in the past, so don't know why I didn't think of
      it!  It is really the only thing that makes sens.gsOn 08/22/14 13:07, Mark Bourne wrote:<br>
Gregory
      Smith - Oracle Sparc Verification wrote:AllI have created a one dimensional array

        called CATEGORY.When I do this function: =Index(Category,25,0)I

        get the contents       of that index - lets say it is the word "cats"When I do
        this function =if((index(category,25,0)="cats"),1,0)I       get FALSE?!!Anyone have any ideas what I am doing
        wrong?gsWith that formula, you should get 0 if the strings don't match (or
      1 if they do); do you really get FALSE displayed in the cell?Assuming you actually get 0 (because the condition
      (index(category,25,0)="cats") is evaluating to FALSE), make sure
      there are no spaces in the values. "cats " is not equal to "cats",
      even though they look the same. If spurious spaces are likely to
      be a problem, you may want to use:  =if((trim(index(category,25,0))="cats"),1,0)to remove spaces either side of the value (and collapse multiple
      spaces within the value into single spaces) before comparison.Mark.