# Thread: two values to find on the same cell

1. ## two values to find on the same cell

Can the below be shorten up? Its basically to count if dr or ba are in cell E2180;

=IF(ISNUMBER(SEARCH("(dr)",E2180)),"1",IF(ISNUMBER(SEARCH("(ba)",E2180)),"1",""))

Many thanks  Reply With Quote

2. You could use

=SUM(COUNTIF(E21,{"*(dr)*","*(ba)*"}))  Reply With Quote

3. Hi Bob,

Thank your for your help. Your formula worked, but it only counted it as one if they appeared, when I needed for it to give BA or DR, depending which one appeared on it. In the end somebody came up with formula =LOOKUP("ZZ",REPT({"DR","BA"},SEARCH({"DR","BA"},A1)^0)). It is very cool but don't quite understand "ZZ" and ^0. Don't know if you can help me to understand this.

Thanks Bob  Reply With Quote

4. Originally Posted by toony Your formula worked, but it only counted it as one if they appeared, when I needed for it to give BA or DR, depending which one appeared on it.
Where in your original post does it say that? You gave an example that returned 1 and asked for it to be shortened. That I did. This other formula finds the first of the pair found. Completely different objectives.  Reply With Quote

5. Sorry there if not properly explained ver y well.....actually, i did use the one u gave me in another case, so it was useful anyway  Reply With Quote

excel 