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?
=(A1=range2)
as a test, try=COUNTIF(range2,A1)>0
as a test whether it exists in the range.