# Formula to search range for either or both values, ignoring blanks

1. ## Formula to search range for either or both values, ignoring blanks

Hi,

I'm usually pretty good with formulas but my minds drawing a blank on this one. I've tried googling but either I'm not articulating what I need clearly or no-one else has asked the question before (I suspect the former!).

I have a range of cells, lets say B5:B10, that I want to search. That range may contain the value "UK", "Intl" or be blank.

I'd like a formula to search the range and if it only finds UK, return UK, if only Intl, return Intl - or if both UK and Intl then return UK / Intl.

Sounds simple enough but just not sure which formula would be best to use - vLookup, match...?

Being a bit dim... TC  Register To Reply

2. ## Re: Formula to search range for either or both values, ignoring blanks

Sounds like an IF stmt should work.
=IF(B5="UK","UK",IF(B5="Intl","Intl",IF(B5="UK/Intl","UK/Intl","")))
if i'm interpreting your needs correctly.  Register To Reply

3. ## Re: Formula to search range for either or both values, ignoring blanks

Try

=TRIM(IF(ISNUMBER(MATCH("*UK*",B5:B10,0)),"UK","")&" "&IF(ISNUMBER(MATCH("*Intl*",B5:B10,0)),"Intl",""))

It will return "UK Intl" if both are found  Register To Reply

4. ## Re: Formula to search range for either or both values, ignoring blanks

=if(Counta(B5:B10)=countif(B5:B10,"UK"),"UK",if(Counta(B5:B10)=Countif(B5:B10,"Intl"),"Intl","UK/Intl"))

Try the above.  Register To Reply

5. ## Re: Formula to search range for either or both values, ignoring blanks

Thanks for your reply Sambo Kid - if statement would work for just one cell as you say, but I need one formula to search a Range B5:B10...  Register To Reply

6. ## Re: Formula to search range for either or both values, ignoring blanks

Thanks Ace_XL and alansidman, both of these work nicely!   Register To Reply

7. ## Re: Formula to search range for either or both values, ignoring blanks

should have known I was thinking of it too simply. Glad you got the answer though.   Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 