Vendor Name Lookup
 

Vendor Name
ABC Programming
ABC Programming
ABS Imaging
ABC Programming
ACP Services
ABC Programming

What would be the formula that searches this column of vendor names by
looking at the first 6 characters/letters and returns a "True" if there is
another vendor name that is similar, or a "False" if it is not. I can't put
in the exact lookup since it varies from cell to cell.

Thanks!!

Vendor Name Lookup
 

stephiesunny wrote:
Vendor Name
ABC Programming
ABC Programming
ABS Imaging
ABC Programming
ACP Services
ABC Programming

What would be the formula that searches this column of vendor names by
looking at the first 6 characters/letters and returns a "True" if there is
another vendor name that is similar, or a "False" if it is not. I can't put
in the exact lookup since it varies from cell to cell.

Thanks!!


=SUMPRODUCT(--(LEFT($A$2:$A$7,6)=LEFT(A2,6)))1

Vendor Name Lookup
 

I would use a helper column with formulas like:
=left(a1,6)

And then compare the value against this helper column.

But you could use an array formula:

=ISNUMBER(MATCH(A1,LEFT(B1:B10,6),0))
or maybe:
=ISNUMBER(MATCH(LEFT(A1,6),LEFT(B1:B10,6),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

stephiesunny wrote:

Vendor Name
ABC Programming
ABC Programming
ABS Imaging
ABC Programming
ACP Services
ABC Programming

What would be the formula that searches this column of vendor names by
looking at the first 6 characters/letters and returns a "True" if there is
another vendor name that is similar, or a "False" if it is not. I can't put
in the exact lookup since it varies from cell to cell.

Thanks!!

--

Dave Peterson

Vendor Name Lookup
 

=SUMPRODUCT(--(LEFT($A$1:$A$100,6)=LEFT(A1,6)))1


"stephiesunny" wrote:

Vendor Name
ABC Programming
ABC Programming
ABS Imaging
ABC Programming
ACP Services
ABC Programming

What would be the formula that searches this column of vendor names by
looking at the first 6 characters/letters and returns a "True" if there is
another vendor name that is similar, or a "False" if it is not. I can't put
in the exact lookup since it varies from cell to cell.

Thanks!!