I'm trying to understand the =MATCH() function with multiple criteria.
As far as I understand it binary outputs a 0 or 1 if a criteria is met. So I would expect in the background for my example the result would be (1,1,0,0)*(0,1,1,0) = (0,1,0,0) so if I then =MATCH(1,(0,1,0,0)) I expect it to return 2 instead of 1.
Can anyone explain where my mistake is?
| A | B | C |
|---|---|---|
| yes | yes | no |
| yes | yes | |
| no | yes | |
| no | no |
=MATCH(1,(B1:B4=A1)*(C1:C4=A1))
[example]
CodePudding user response：
use:
=ARRAYFORMULA(MATCH(1, (B1:B4=A1)*(C1:C4=A1), 0))
you are multiplying arrays so you will need ARRAYFORMULA wrapping and also do not forget on 3rd MATCH argument