# Excel: Getting FALSE, when the value is in the row array

I have a table where I want to pull data based on the row and column labels. So I'm using the INDEX MATCH with arrays in the MATCH function but I keep getting a #N/A because b1:d1=d1 is FALSE. Why is this?

``````    1 2 3
Jan x x o
Feb x x x
Mar x x x
``````

The value I want is in the cell with "o" so I'm using
INDEX(B2:D4, MATCH(1,(B2:D2=B2)*(B2:D2=D2), 0))

• please show test data, expected output and the actual formula you are using. Put these in the original post using edit Commented Apr 20, 2018 at 14:33

To return o in your example above you would need

``````=INDEX(B2:D4,MATCH("Jan",A2:A4,0),MATCH(3,B1:D1,0))
``````

The syntax for `INDEX()` is `INDEX(array, row_num,[column_num])`. You're specifying only the `row_num` with the `MATCH()` in your formula.

@RET's formula will give you the cell you're looking for because it specifies both the `row_num` and `column_num`.

Excel has a helpful feature for troubleshooting these kinds of issues. If you select the cell with your formula, and then click inside the parameter list of one of the functions in your formula, you'll see a dropdown showing what the parameters should be:

Then, clicking one of the parameters in the dropdown highlights what Excel thinks that parameter is in your formula. Just that much can help you find missing commas and parentheses, but the most helpful feature is when you hit the F9 key - that makes Excel evaluate the highlighted portion of your formula:

The above screenshot shows that `(B2:D2=B2)*(B2:D2=D2)` evaluates to the array `{0,0,0}`, which is why `MATCH()` can't find a 1 in there.

If you highlight the two parts of the product separately, you'll find that `(B2:D2=B2)` evaluates to `{True,True,False}` and `(B2:D2=D2)`evaluates to `{False,False,True}`. Arithmetic operations on arrays converts `True` and `False` to 1's and 0's, and performs the equivalent of a logical `AND()`. So that's why the expression evaluates to an array of 0's - There's no `True*True` that would give a 1.

Note: If you use the F9 feature, be sure to type UNDO (CTRL-Z) afterward. Otherwise, if you type Return or tab out of the formula, Excel will save the altered formula in that cell. I guess they always like to hand out some bad with the good.

I hope this helps you out, and best of luck.