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))

  • 1
    please show test data, expected output and the actual formula you are using. Put these in the original post using edit – Scott Craner Apr 20 '18 at 14:33

To return o in your example above you would need

| improve this answer | |

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:

enter image description here

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:

enter image description here

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.