I have an Excel sheet in which there is some data. I want to pick a particular value from the 3rd column based on the conditions in the following formula
=INDEX($B:$O,MATCH(1,($B:$B=$Q2)($E:$E="CE")($M:$M=R2),0),3)
It is resulting in #N/A. I checked step calculations and noticed that $M:$M=R2 is checking only the value that is present in cell M2 repeatedly whereas it should be checking all the cells in column M
I have described the step calculations above
>Solution :
You must enter the formula as array entry with CTRL+SHIFT+ENTER. Also use * asterisk between each condition.
=INDEX($B:$O,MATCH(1,($B:$B=$Q2)*($E:$E="CE")*($M:$M=R2),0),3)
And if you have access to FILTER() function then could try
=FILTER($D:$D,($B:$B=$Q2)*($E:$E="CE")*($M:$M=R2))