PDA

View Full Version : Excel formula help match



malleshg24
01-02-2020, 07:07 PM
Hi Team,


I am using below excel formula, (range $A$2:$A$6 and Range $B$2:$B$6) Contains list of Unique names.
after applying a formula , I get result true or false, and I am deleting false after applying autofilter.

Formula:-
=OR(ISNUMBER(MATCH(A2,Criteria!$A$2:$A$6,0)),ISNUMBER(MATCH(Data!B2,Criteri a!$B$2:$B$6,0)))


I want formula should give True for blank cells as , which are highlighted in column D in Blue color.
Because I want to delete false.


Plz suggest excel formula or should I need add anything in Criteria to cover Blank cells as well.




Thanks
mg

Bob Phillips
01-03-2020, 04:14 AM
Try

=OR(A2="",B2="",ISNUMBER(MATCH(A2,Criteria!$A$2:$A$6,0)),ISNUMBER(MATCH(Data!B2,Criteria!$ B$2:$B$6,0)))

malleshg24
01-07-2020, 11:43 AM
Hi xld,

Thanks for your help, this code is working as expected :thumb!


Regards,
Mg

malleshg24
01-28-2020, 11:01 PM
Hi xld,

I need slight change in below formula,
Below formula gives me correct result, Needs to add one more Criteria in this, If both the cells blank, I want result True.


if range(a4) and range(b4) is blank , I want result true in C4.


=OR(ISNUMBER(MATCH(A4,Criteria!$A$2:$A$6,0)),ISNUMBER(MATCH(Data!B4,Criteri a!$B$2:$B$6,0)))


Thanks
mg

p45cal
01-30-2020, 09:48 AM
=OR(ISNUMBER(MATCH(A2,Criteria!$A$2:$A$6,0)),ISNUMBER(MATCH(Data!B2,Criteri a!$B$2:$B$6,0)),COUNTBLANK(A2:B2)=1)