index match - multiple criteria error?

[marinamos]

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 automate administrative templates for my company to not have to manually enter each piece of information, and this way avoid human mistakes...

However, when I try to use the index/match formula, with several criteria, it is not going anywhere. I am pulling all my array data from the "array sheet", and what I need as a result is the "option ID" for the corresponding SKU "DRD581IVO" only for the "BARN" DC (Distribution Center) only.

So my formula currently looks like:

='INDEX('array sheet'!D2:D385,MATCH(1,(O5='array sheet'!C2:C385)*(U5='array sheet'!E2:E385),0))

Could someone please explain to me why it is not working and what the solution is? (Im using office 2019 version)

- is it because I am pulling data from another sheet?

- is it because some of the data I am using have formulas in (O5 and U5)?

- or any other reason :\ ?

I have been on this for hours it seems like, would very much appreciate some help!!

Thanks A LOT in advance

Marina

[#2]

@marinamos 

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

Does this work in your sheet? The value from cell U5 is "BARN" and it appears in column C in the 'array sheet'. The value "DRD581IVO" from cell O5 should be in column A in the 'array sheet' i suppose. The "option ID" is in column B in the 'array sheet'.

[#3]

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

[#4]

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

[#5]

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

[#6]

I managed to confirm the formula! but it is now giving me N/A error as a result :D 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?

[#7 · Accepted solution]

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

[#8]

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 

[#9]

OMG thank you!! I can't believe I didn't notice I forgot to update this part of the formula :D

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