Excel IF statement Comparing text with number

问题:

I was using IF statement to group some numbers together. In column A I have numeric and text values.

  A   B
1 s   =IF(A1>200,2345,"ad")

If I do this then the B1 returns 2345.

How does Excel compare string values with number values?

回答1:

For the Excel compare operators (e.g. <, =, >), number values are less than text values which are less than logical values. You can use the VALUE function to convert text values to number values for number comparisons. You can use the TEXT function to convert number values to text values for text comparison.

回答2:

You can concatenate a letter to both the formula (string) and the number and excel will be able to compare them. I was having the same problem when comparing the result of an index match formula with a number and this solved it easily.

Example:

String:

=CONCATENATE("Q",Result from formula)

Number:

=CONCATENATE("Q",Number)

IF string=number should work now.