Forum Discussion

marinamos's avatar
marinamos
Copper Contributor
May 09, 2022
Solved

index match - multiple criteria error?

Dear excel community,   I need your help on figuring out why my index match formula is not working. see below screenshots with the formula in orange on "DRD581IVO BARN" sheet..   I am trying to a...
admin
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    May 09, 2022

    marinamos 

    =INDEX('array sheet'!B2:B385,MATCH(1,(O5='array sheet'!A2:A385)*(U5='array sheet'!C2:C385),0)) 

     

    Great. I think it will work if you change the formula as above. The distribution center is in column C in the 'array sheet' and therefore the formula should have: (U5='array sheet'!C2:C385).

marinamos's avatar
marinamos
Copper Contributor
May 09, 2022
Hi! my apologies, I simplified the array sheet for the purpose of the exercise, and forgot to update the formula!! thanks for noticing
However it is still not working :(, still giving me the pop up message "there's a problem with your formula"...
Im sorry I wanted to upload my file to help, but I cant seem to be able to add it!

Please let me know if you figure what the issue could be otherwise..?

Thanks a lot!!
OliverScheurich's avatar
OliverScheurich
Gold Contributor
May 09, 2022

marinamos 

=INDEX('array sheet'!B2:B385,MATCH(1,(O5='array sheet'!A2:A385)*(U5='array sheet'!C2:C385),0))

Sorry i forgot to say that you have to enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. 

  • marinamos's avatar
    marinamos
    Copper Contributor
    May 09, 2022

    I tried to do that but it didn't seem to work so I must not be doing the right thing....
    1. I enter the formula in the bar
    2. then hold ctrl+shift+enter?

    When I do so I get again the same popup

    Sorry Quadruple_Pawn I am still quite beginner at more advanced formulas!!

    • marinamos's avatar
      marinamos
      Copper Contributor
      May 09, 2022

      I have reduced my array range, to test and see if it helps, so only B68 should be the result... but it is still giving me N/A error

       

       

       

      marinamos 

    • marinamos's avatar
      marinamos
      Copper Contributor
      May 09, 2022

      I managed to confirm the formula! but it is now giving me N/A error as a result 😄 The result should be the cell B281...

       

      Could it be that it is because B281:B285 have the same data so it confuses the formula?

       

       

       

       

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        May 09, 2022

        marinamos 

        =INDEX('array sheet'!B2:B385,MATCH(1,(O5='array sheet'!A2:A385)*(U5='array sheet'!C2:C385),0)) 

         

        Great. I think it will work if you change the formula as above. The distribution center is in column C in the 'array sheet' and therefore the formula should have: (U5='array sheet'!C2:C385).

Resources