CODEWITHSUNDEEP
excelvba
xLokos
xLokos

Reputation: 109

Check if any index match result matches criteria

I need to convert the following formula to check whether any of the index/match results meet the criteria (are equal to M2).

=IF(ISERROR(MATCH(L2,B:B,0)),0,IF(INDEX(M:M,MATCH(L2,B:B,0))=M2,1,0))

To be exact I need this part updated:

IF(INDEX(M:M,MATCH(L2,B:B,0))=M2,1,0)

First I check whether the value from L2 exists in column B. Let's say there are two rows for which this is true. I want to check whether the value in column M in any of the two results is equal to M2 and if yes, output 1, if not, output 0.

Is it possible? Would it be better to use a macro?

Upvotes: 1

Views: 101

Answers (1)

Miguel_Ryu
Miguel_Ryu

Reputation: 1418

This should be enough to check the existence of L2 in B:B at the same time if the position on M:M contains the value of M2.

=IF(COUNTIFS(B:B,L2,M:M,M2)>0,1,0)

Upvotes: 3

Related Questions