Using LEFT(Cell,#) as a condition in an IF statement

Posted by Melody on April 27, 2001 8:22 AM

I am trying to make the following formula work:

=IF(Data!$Y$8:$Y$30000=(LEFT($E8,2)),Data!$B$8:Data!$B$30000,"")

Now, I know that on this particular row, (LEFT($E8,2)) is 10, and if I type '10' in place of (LEFT($E8,2)), the formula works fine. However, I need it to read it row by row for me (I don't want to go and figure it out on 30000 rows!).

Can anyone help me?

Posted by cpod on April 27, 2001 8:46 AM

The Left function returns a string. You have to convert this to a number. Try this:

=IF(Data!$Y$8:$Y$30000=(LEFT($E8,2)*1),Data!$B$8:Data!$B$30000,"")

Posted by Stephane Parent on April 27, 2001 8:51 AM

Hi Melody,

The problem you have is that left returns a text value and the if compare it to a numeric value.
This one should work:
=IF(Data!$Y$8:$Y$30000=value((LEFT($E8,2))),Data!$B$8:Data!$B$30000,"")

Stephane Parent

Posted by Melody on April 27, 2001 8:51 AM

It Worked, It Worked, It Worked!!!

That worked like a dream. You are wonderful!!!!

: I am trying to make the following formula work: