Excel formula help 1

[scriptscribe]

Hi,

I'm looking to get a count of the number of rows where the first three letters of the last name in one column match the first three letters of the last name in another. I want to use the same criteria on the first name. I wrote the below formula, but it doesn't seem to be working.

=IF((LEFT(A2,3) = LEFT(E2,3)) and (left(b2,3) = left(f2,3)), 1, 0)

Any help will be greatly appreciated.

Thanks,
Mike

 

[scriptscribe]

Okay, I figured out that I was making the boneheaded move of using "and" instead of "&", leaving me with this:

=IF((LEFT(B4,3) = LEFT(F4,3) & LEFT(E4,3) = LEFT(A4,3)),1,0)

Now the problem is that it's returning "0" for all rows, even on obvious matches such as:

A1 B1 E1 F1
Johnson Jim Johnson Jim

 

[SkipVought]

[tt]
=IF(AND(LEFT(B4,3) = LEFT(F4,3), LEFT(E4,3) = LEFT(A4,3)),1,0)
[/tt]
& means CONCATENATE.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[yogia]

Hi Mike:

How about posting a few rows of your data, and your expected result(s) along with an explanation as to why that is the correct result -- so we can clearly see what you are trying to accomplish.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com