Excel IF statement Comparing text with number

Question

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?

Answer 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.

Answer 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.