2

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]1

player0
  • 124,011
  • 12
  • 67
  • 124
Niel Agi
  • 23
  • 3
  • Kindly add input table and expected output table as **text table** (NOT as IMAGE) to the question. [Click here](https://webapps.stackexchange.com/a/161855/) to create a table easily. Adding such tables greatly increases your chances of getting a elegant answer, as **it is easier to copy/paste**. If you share spreadsheets, your question maybe closed, as questions here must be [self contained](https://meta.stackoverflow.com/a/260455). [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), if you share Google files. – TheMaster Oct 12 '22 at 19:29
  • @TheMaster Isn't that what I did? – Niel Agi Oct 13 '22 at 15:20
  • To inform you that your email will be public, if you share spreadsheets. It's a canned response. – TheMaster Oct 13 '22 at 18:24

1 Answers1

1

use:

=ARRAYFORMULA(MATCH(1, (B1:B4=A1)*(C1:C4=A1), 0))

enter image description here

you are multiplying arrays so you will need ARRAYFORMULA wrapping and also do not forget on 3rd MATCH argument

player0
  • 124,011
  • 12
  • 67
  • 124
  • That works. Why is it necessary to add that formula when you are using a MATCH but it isn't necessary when using an INDEX-MATCH? – Niel Agi Oct 13 '22 at 08:40
  • 1
    @NielAgi INDEX is another type of ARRAYFORMULA and it's interchangeable – player0 Oct 13 '22 at 08:50
  • Alright, thanks. I have a follow up question, should I make a new post for it or just ask it here? https://imgur.com/a/Ms8UePH I match where the reviewee name is andy and the rating is 2 so I would expect the match to return "3" but it just returns an entire matrix? – Niel Agi Oct 13 '22 at 09:00
  • @NielAgi can you share a copy / sample of your sheet with an example of the desired output? – player0 Oct 13 '22 at 09:10