how to use exact function to compare a text value from a cell to a constant value

In cell C6 , I have the value “Shot” without the quotes. In another cell I have the formula as shown below

always I get the result 2 irrespective of what value I have in cell c6
How do I compare the text value in cell c6 with the constant value “Shot” and return 1 if they are same and else 2

Make sure C6 exactly contains the value Shot, no spaces or other characters that might not be visible, and also case is significant.

@CN: In the future, please do not mark the community wiki checkbox. See guidelines for asking.

I can’t reproduce this error. This is what I did:

  • Open new spreadsheet
  • Enter Shot in C6
  • Enter =IF(EXACT(C6,"Shot"),1,2) in another cell, which then evaluates to 1
  • Change the value of C6 to ``, and C6 then evaluates to 2.

Perhaps C6 has some weird formatting applied to it? For example the number 0 could formatted to appear as Shot, so while C6 is printed as Shot, the value of C6 could still be 0 and while printed as Shot, it would not match Shot when compared using EXACT().

From the way you phrased your question, it sounds like this issue is part of a larger spreadsheet. Perhaps try to reproduce this in a new spreadsheet? Or perhaps you could try removing all formatting: CTRL+A then CTRL+M.

I was creating a spreadsheet and an if/then statement wasn’t working with a name in it, but your example works perfect! Thank you!