Forum Discussion
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 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
=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 Replies
='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'.
- 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!!=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.
