Compare first 7 characters of COL A with COL B or COL C

dahvaio

New Member
Joined
Apr 6, 2005
Messages
6
I have 3 columns and could not figure out how to compare them using only the first 7 characters. I need to compare all entries in COL A with COL B and then with COL C.

In COL E it should put the name that matched in COL B
In COL F it should put the name that matched in COL C

Here is the current formula that I am using on COL E but I do not know ho to make it search only a set number of characters:

=IF(ISERROR(MATCH(A2,$B$3:$B$60000,0)),"",A3)

For example:

COL A:
acarter
BERKLEYPL
BOLTONJM
EBOSTON
FHERSHEY
BRAZILDD
BREWERMM


COL B:
ABANIELHE
ADELEON
AGUIRRELC
AGUILARK1
FHERSHEY
AKFOLEY
ALANGENW

COL C:
acarter
AGUILARK1
AKAAIAWA
AKavin
ALANIZDO
Alcortao
ALEXANDERCA
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Something like this?

=IF(ISNUMBER(MATCH(LEFT($A1,7),LEFT(B$1:B$7,7),0)),$A1,"")

confirmed with CTRL+SHIFT+ENTER not just ENTER and then copied down and right .

adjust ranges to suit and reconfirm with CSE key combo.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,953
Messages
5,545,153
Members
410,666
Latest member
forzasec
Top