Comparing static value with value derived from a formula not working....

[hikerguy]

Here's the situation:

Let's say I have an IP address in cell E4 of 10.234.8.11. I use a formula to extract the 2nd and 3rd octets, and place that result in F4:

=TRIM(LEFT(SUBSTITUTE(REPLACE(E4,1,FIND(".",E4),""),".",REPT(" ",100),2),100)) ----> result is 234.8 in cell F4

I also some data arranged like this (in two columns):

STL 234.8
OMJ 236.7
CHI 238.9

Let's say for STL, the value 234.8 is in cell C4. My goal is to create a formula in cell G10 (an IF statement) to see if F4 matches C4. If it matches, place "textA" in G10l. If it doesn't match, place "textB" in G10.

I've formatted F4 and C4 as text.

I can physically see that the result of the formula in F4 does in fact match C4, yet if I do this as a test:

=IF(C4=F4,"yes","no")

It comes out "no" (when I can clearly see they ARE identical).

Yet if I do this:

=EXACT(C4,F4)

it comes out TRUE.

What the heck is going on????? Should I be doing something else to say "Compare the result of the formula in F4 with C4? It just makes no sense the the EXACT command gives me the result I'd expect, but the IF formula doesn't.

Thanks,

Andy

 

[Toadstool]

Hi Hikerguy,

You're comparing numeric to tex (the formatting won't actually change that) so you've two choices:
1. Change the F4 to numeric by adding +0 at the end of the TRIM
2. Change the numeric in C4 to text

Book1
	B	C	D	E	F	G
4	STL	234.8		10.234.8.11	234.8	TextA
5	OMJ	236.7		10.236.7.11	236.7	TextA
6	CHI	238.9		10.238.9.11	238.9	TextA
Sheet1

Cell Formulas
Range	Formula
F4:F6	F4	=TRIM(LEFT(SUBSTITUTE(REPLACE(E4,1,FIND(".",E4),""),".",REPT(" ",100),2),100))+0
G4:G6	G4	=IF(C4=F4,"TextA","TextB")

or

Book1
	B	C	D	E	F	G
4	STL	234.8	234.8	10.234.8.11	234.8	TextA
5	OMJ	236.7	236.7	10.236.7.11	236.7	TextA
6	CHI	238.9	238.9	10.238.9.11	238.9	TextA
Sheet1 (2)

Cell Formulas
Range	Formula
F4:F6	F4	=TRIM(LEFT(SUBSTITUTE(REPLACE(E4,1,FIND(".",E4),""),".",REPT(" ",100),2),100))
G4:G6	G4	=IF(D4=F4,"TextA","TextB")
D4:D6	D4	=TEXT(C4,"###.#")

 

[hikerguy]

Thanks for that info toadstool. Since posting this, I discovered that if I formatted both cells to General, then Excel saw them as equal values. But I'm keeping your info as well in case I run into other issues not solved by switching to General.

Andy