Ask Your Question

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

asked 2018-05-30 02:05:57 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

In cell C6 , I have the value "Shot" without the quotes. In another cell I have the formula as shown below =IF(EXACT(C6,"Shot"),1,2)

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

edit retag flag offensive close merge delete



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

erAck gravatar imageerAck ( 2018-05-30 09:24:15 +0200 )edit

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

Jim K gravatar imageJim K ( 2018-05-30 20:29:19 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-06-01 10:49:52 +0200

hjek gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-05-30 02:05:57 +0200

Seen: 4,626 times

Last updated: Jun 01 '18