CODEWITHSUNDEEP
stringexcelif-statementcomparison
maestromani
maestromani

Reputation: 861

Comparing cell contents against string in Excel

Following is my table

file:*.css  file:*.csS
file:*.PDF  file:*.PDF
file:*.ppt  file:*.xls
file:*.xls  file:*.doc
file:*.doc  file:*.CFM
file:*.dot  file:*.cfc
file:*.CFM  file:*.CFC
file:*.cfc  file:*.DOC

I need a formula to populate the H column with True or False if it finds column G in column F (exact case).

I used following but nothing seems to be resulting correct value, either it fails with case or if the same row didn't match returns false.

1 =IF(F2=(G$2:G$43), "True", "false") -> returns false if the same row didn't match.

2. =EXACT(F2,G$2:G$43) -> same as above

3. =MATCH(F2,G$2:G$41,0) -> fails with case sensitive.

4. =MATCH (TRUE, EXACT ( G$2:G$41, F2 ), 0 ) -> fails like #1

Upvotes: 0

Views: 66

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

We need an Array formula. In G2 enter:

=NOT(ISERROR(MATCH(1,--EXACT(F$2:F$7,E2),0)))

and copy down.

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

enter image description here

Note: The curly brackets that appear in the Formula Bar should not be typed.

Upvotes: 2

Related Questions