Ask Your Question
1

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

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

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

Comments

1

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 +0100 )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 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

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

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

Stats

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

Seen: 1,012 times

Last updated: Jun 01 '18