Match function

[kmrowlan]

Is there a way to do a match where it looks if the cell contains certain criteria? Below I am gathering all backcharge data where TX Transportation is at fault and as you can see there are several variations listed below along with several I have not listed. I would like to search for any cell that contains TX Transportation without listing all of the possible selections. Is that possible?


=SUMPRODUCT(--('BACKCHARGE DATA'!$F$2:$F$1143>0),--ISNUMBER(MATCH('BACKCHARGE DATA'!$I$2:$I$1143,{"TX Transportation, ","TX Transportation, IN Distribution, ","TX Transportation, TX Distribution, "},0)),--('BACKCHARGE DATA'!$K$2:$K$1143>=A3),--('BACKCHARGE DATA'!$K$2:$K$1143<A4)

Thanks

Kevin

 

[T. Valko]

Is there a way to do a match where it looks if the cell contains certain criteria? Below I am gathering all backcharge data where TX Transportation is at fault and as you can see there are several variations listed below along with several I have not listed. I would like to search for any cell that contains TX Transportation without listing all of the possible selections. Is that possible?



=SUMPRODUCT(--('BACKCHARGE DATA'!$F$2:$F$1143>0),--ISNUMBER(MATCH('BACKCHARGE DATA'!$I$2:$I$1143,{"TX Transportation, ","TX Transportation, IN Distribution, ","TX Transportation, TX Distribution, "},0)),--('BACKCHARGE DATA'!$K$2:$K$1143>=A3),--('BACKCHARGE DATA'!$K$2:$K$1143<A4)< p>
Thanks

Kevin

Maybe this...

Instead of using ISNUMBER(MATCH(...)) use ISNUMBER(SEARCH(...))

--(ISNUMBER(SEARCH("TX Transportation",'BACKCHARGE DATA'!$I$2:$I$1143)))