Find 1st 6 matching numbers in 2 different columns

[Jacki]

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

 

[sastoka]

Hi there,

can't you use Vlookup formula ?

Rgds,

 

[Mike Szczesny]

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]

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.