# Find 1st 6 matching numbers in 2 different columns

#### Jacki

##### New Member
Hi,

I have a spreadsheet that contains account numbers up to 15 digits long. In a 2nd sheet I have a shortened version of these account numbers (1st 5 numbers match). I need to be able to show which account numbers match. Can someone please help me with a formula for this?

Jacki

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### sastoka

##### Board Regular
Hi there,

can't you use Vlookup formula ?

Rgds,

#### Mike Szczesny

##### Active Member
Assume column A data contains the 15 digit number. Assume column B contains numbers (1st numbers match). You can use =exact(left(A1,5),B1). Copy down. Same numbers will produce true. Different numbers will produce false. Granted, this formula will work assuming column only contains the first five matching numbers.

#### Joyner

##### Well-known Member
Hello Jacki, what do you want for a result, do you want the Row number of the match? Do you want a formula that just identified if there is a match for the 15 digit account number?

something like this will give you the row number of the match, and #N/A for no match:

=MATCH(LEFT(A1,5),Other Sheet Name!A:A,0)

Assumes the 15 digit Acct no starts in A1 and the 5 digit numbers on the other sheet are in column A

=IFERROR(MATCH(LEFT(A1,5),Other Sheet Name!A:A,0),"No Match") to return "No Match"

You can use VLOOKUP is a similar way to return the matching 5 digit account number.

If you are looking for the first 6 that match, you will need an array formula.

Last edited:

Replies
3
Views
136
Replies
5
Views
76
Replies
15
Views
132
Replies
8
Views
159
Replies
3
Views
74