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

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.

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

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.

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...

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

Thanks Ace_XL and alansidman, both of these work nicely!

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.

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