Formula to match non case sensitive text

[austenr]

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]

=OR(B2=A2:A4)

as an array formula

[austenr]

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]

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]

Thanks Zack

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

[Bob Phillips]

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]

thanks

[Bob Phillips]

You can even use


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