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

**TC1980** · 06-28-2013, 09:11 AM

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

**Sam Capricci** · 06-28-2013, 09:20 AM

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.

**Ace_XL** · 06-28-2013, 09:21 AM

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

**alansidman** · 06-28-2013, 09:24 AM

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

Try the above.

**TC1980** · 06-28-2013, 09:36 AM

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

**TC1980** · 06-28-2013, 09:40 AM

Thanks Ace_XL and alansidman, both of these work nicely!

**Sam Capricci** · 06-28-2013, 09:53 AM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions