Forum Discussion

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

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

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).

marinamos
Copper Contributor
May 09, 2022
OMG thank you!! I can't believe I didn't notice I forgot to update this part of the formula 😄

It worked Im so happy, thank you for your patience!!