PDA

View Full Version : [SOLVED] Formula to match non case sensitive text



austenr
05-04-2005, 12:42 PM
I know that this formula:


=OR(EXACT(B2,A2:A4)) will match like case strings, is there a way to match cases like:

sam jones to SAM JONES or Sam Jones to SAM JONES etc.

Thanks

Bob Phillips
05-04-2005, 12:50 PM
=OR(B2=A2:A4)

as an array formula

austenr
05-04-2005, 01:52 PM
Hmmmmm. Tried that formula on a simple two column match. And yes I entered it as an array formula.

Orange Banana False
Apple
Banana

Column C had the formula as an array =OR(B1=A1:A4). Know it is something simple.
:banghead:

Zack Barresse
05-04-2005, 01:55 PM
As a non-array entered formula, you can use something like this Austen ...


=SUMPRODUCT(0+(B1=A2:A5))

Where B1 holds the value to search for and A2:A5 is the range to look in. This is not case sensitive. For single criterion/cell matches, use the SEARCH function; as opposed to the FIND function, it's not case sensitive either.

austenr
05-04-2005, 01:57 PM
Thanks Zack

Can you modify it to TRue False instead of 1 if true?

Bob Phillips
05-04-2005, 02:34 PM
Austenr,

My original form ula works, even with the test data you posted.

Firefytr's formula has to test the result to return TRue or False


=SUMPRODUCT(--(B1=A2:A5))>0

(you could use
=IF(SUMPRODUCT(--(B1=A2:A5))>0,True,False) but that is just wastefull)

austenr
05-04-2005, 02:37 PM
thanks

Bob Phillips
05-04-2005, 02:40 PM
You can even use


=ISNUMBER(MATCH(B1,A2:A5,0))