Problem with IF function
 

Hi

Im using the if function, If example A1=B1 insert C1.

A1 (text field) has this formula in it =IF((I38=3),MID(H38,1,1)," ")
B1 (text field) =3
C1 (text field) = 0011

But every time i put the forumal =IF(A1=B1,C1,FALSE) it come up as
false even though they have the same value (all the field are text not
number as it will have letters in as well)

If i cange the 3 to an A the formual works fine.

this isnt the actual formual i want as im going to use a lookup table
but im just narrowing the problem down.

I hope this makes sense any help would be great. :)


--
haitch2
------------------------------------------------------------------------
haitch2's Profile: http://www.excelforum.com/member.php...o&userid=27677
View this thread: http://www.excelforum.com/showthread...hreadid=474627

I think you're already on to the issue, comparing text strings to numeric
values. You said the '3' is a text field; is that because you formatted as
text (which would n't help as far as matching) or becuase you entered it as
'3 (with a leading apostrophe, which forces it to be treated as text)? To be
safe, you can probably change the IF to read if(a1=trim(b1),c1,false). The
trim function will force a numeric value to be converted to a string.
Hope this helps. --Bruce

"haitch2" wrote:


Hi

Im using the if function, If example A1=B1 insert C1.

A1 (text field) has this formula in it =IF((I38=3),MID(H38,1,1)," ")
B1 (text field) =3
C1 (text field) = 0011

But every time i put the forumal =IF(A1=B1,C1,FALSE) it come up as
false even though they have the same value (all the field are text not
number as it will have letters in as well)

If i cange the 3 to an A the formual works fine.

this isnt the actual formual i want as im going to use a lookup table
but im just narrowing the problem down.

I hope this makes sense any help would be great. :)


--
haitch2
------------------------------------------------------------------------
haitch2's Profile: http://www.excelforum.com/member.php...o&userid=27677
View this thread: http://www.excelforum.com/showthread...hreadid=474627

Thankyou for the help.

I havnt tested it yet as i have found a rather longwinded way of doing
it by ading another field and coverting eqvilant values to code, and
matching them to that field instead, but it works for now. What you
have said does make sense so i will give it a go, tidy every think up a
bit :) .

Thankyou again


--
haitch2
------------------------------------------------------------------------
haitch2's Profile: http://www.excelforum.com/member.php...o&userid=27677
View this thread: http://www.excelforum.com/showthread...hreadid=474627

Try using this formula

=IF(VALUE(A1)=B1,C1,FALSE)

--
HTH

Bob Phillips

"haitch2" wrote in
message ...

Hi

Im using the if function, If example A1=B1 insert C1.

A1 (text field) has this formula in it =IF((I38=3),MID(H38,1,1)," ")
B1 (text field) =3
C1 (text field) = 0011

But every time i put the forumal =IF(A1=B1,C1,FALSE) it come up as
false even though they have the same value (all the field are text not
number as it will have letters in as well)

If i cange the 3 to an A the formual works fine.

this isnt the actual formual i want as im going to use a lookup table
but im just narrowing the problem down.

I hope this makes sense any help would be great. :)


--
haitch2
------------------------------------------------------------------------
haitch2's Profile:
http://www.excelforum.com/member.php...o&userid=27677
View this thread: http://www.excelforum.com/showthread...hreadid=474627