CODEWITHSUNDEEP
excelindexingmatch
Rishikesh Sahani
Rishikesh Sahani

Reputation: 25

Combination of index and match in Excel is checking only the first row repeatedly

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

Upvotes: 1

Views: 74

Answers (1)

Harun24hr
Harun24hr

Reputation: 36770

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

Upvotes: 1

Related Questions