2

Could someone help me turn this 2 Criteria match function into a 4 criteria match function please? This one works, but is only the start:

=INDEX(range1,MATCH(1,(A19=range2)*(B19=range3),0))

I also want a third and fourth match in the above formula, with those two being an OR option. I thought based upon the working version that this might work, but it doesn't: 

=INDEX(range1,MATCH(1,(A19=range2)*(B19=range3)*(OR(C19=range4,D19=range5)),0))

I've been trying to use AND commands, my initial version of the first code above being this:

=INDEX(range1,MATCH(1,AND(A19=range2,B19=range3),0))

It always returns #N/A after CTRL+ALT+ENTER is entered though, so it's obviously an issue with my understanding of either MATCH or AND (or both I guess),

The first example works EXACTLY as intended, but unfortunately I don't know why and I can't work it out well enough to adapt it. Maybe I'm too tired and have run out of space in my head for the peculiar way in which Excel formulas work, but I've read and re-read the help files for them and still it doesn't make sense to me.

Any help would be greatly appreciated, as always.

Thanks,

Joe

JoeP
  • 856
  • 4
  • 15
  • 29
  • 3
    I think John Bustos has the perfect solution for you - to explain a little, typically you can't use AND and OR in array formulas because those functions don't return arrays which apply to each value in your ranges, but a single result (only TRUE with `AND` if `every` cell fulfills the criteria, TRUE with `OR` if `any` cell fulfills the criterion)....so to simulate AND you multiply with * (as per your original formula) and to simulate OR you add with + as per John's suggestion – barry houdini Nov 27 '12 at 19:02
  • Right, that actually makes perfect sense - thank you! – JoeP Nov 28 '12 at 09:09

1 Answers1

4

I'm just guessing here, but would this work?

=INDEX(range1,MATCH(1,(A19=range2)*(B19=range3)*(((C19 = range4)+(D19 = range5))>0),0))
John Bustos
  • 19,036
  • 17
  • 89
  • 151