-1

I've been having problems using the following formula:

=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3),0))

So I started testing pieces of it to see what wasn't working. When I try

=(A1=range2)

and autofill it down to get

=(A2=range2)
=(A3=range2)

...

=(An=range2)

it correctly returns TRUE for the first few cells, but then erroneously returns FALSE for the remaining cells.

I've confirmed the data types are the same throughout both ranges. I've confirmed the formula autofills correctly in all cells. And I've confirmed the relationship should be TRUE.

What other issues could be causing this formula to evaluate inconsistently?

8
  • 2
    There are soooo many reasons this could be happening. And there is not enough information in your question to even start guessing, Show some data and your exact formula. Apr 8, 2016 at 15:40
  • 1. On your first formula do you use Ctrl-Shift-Enter when leaving edit mode to force it to be an Array formula? 2. Instead of =(A1=range2) as a test, try =COUNTIF(range2,A1)>0 as a test whether it exists in the range. Apr 8, 2016 at 16:03
  • @ScottCraner I just tried Ctrl-Shift-Enter which did not change the result.
    – sarah_d
    Apr 8, 2016 at 16:37
  • See my comment below. Apr 8, 2016 at 16:38
  • @ScottCraner - I tried =COUNTIF(range2,A1)>0 which correctly returned TRUE for all cells.
    – sarah_d
    Apr 8, 2016 at 16:38

1 Answer 1

-1

A1=range2 is test for equality between the content of cell A1 and the range 'range2'. Excel uses the first cell of the range for the comparison.

Now it might well be that your cell A1 is equal to the first cell in range2, but your cell A4 is not equal.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .