Find 1st 6 matching numbers in 2 different columns

Jacki

New Member
Joined
Jul 27, 2014
Messages
17
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?

Thanks in advance :)

Jacki
 

Some videos you may like

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)

Mike Szczesny

Active Member
Joined
Feb 7, 2008
Messages
411
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
Joined
Nov 15, 2005
Messages
1,202
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:

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,308
Members
409,862
Latest member
lbisacca
Top