Partial Match with value returned

[justxan]

I have two columns of data: Column A = Account Numbers, Column B= Transaction Description with Account Number

They cannot be sorted to match and they are not in any particular order.

I need to match column A to the correct description in column B, and preferably have the matching account number from column A inserted into a new column C.

Here's an example of what I have and the end result I need:

Before:

ACCOUNT​ TRANSACTION 91011121 EARLY W/D 7189202 <tbody> </tbody> 31415161 REG W/D 31415161 <tbody> </tbody> 71819202 TFRN DB <tbody> </tbody> 12345678 TRANSFER 12345678 <tbody> </tbody> <tbody> </tbody>

<colgroup><col><col></colgroup><tbody>
</tbody>

After:

ACCOUNT​ TRANSACTION Results 91011121 EARLY W/D 7189202 <tbody> </tbody> 71819202 31415161 REG W/D 31415161 <tbody> </tbody> 31415161 71819202 TFRN DB <tbody> </tbody> No Match 12345678 TRANSFER 12345678 <tbody> </tbody> 12345678 <tbody> </tbody>

<colgroup><col><col></colgroup><tbody>
</tbody>

I have tried different vlookups and index-match formulas with no results. Please help...!

 

[RonB1111]

Assuming your 1st transaction listed as "EARLY W/D 7189202" has a typo and should be "EARLY W/D 71819202"
Try:

=IFERROR(VLOOKUP(--RIGHT(B2,8),A:A,1,FALSE),"No Match")

The -- (minus minus) preceding RIGHT is to coerce the text returned into a value.

 

[justxan]

Assuming your 1st transaction listed as "EARLY W/D 7189202" has a typo and should be "EARLY W/D 71819202"
Try:

=IFERROR(VLOOKUP(--RIGHT(B2,8),A:A,1,FALSE),"No Match")

The -- (minus minus) preceding RIGHT is to coerce the text returned into a value.

Thanks so much for the response, I appreciate it. My apologies as well for the typo.

I tried the formula and it sort of works, but sort of doesn't. For example, some of the results return the correct account number, but others do not even though a valid account number in column A is present.

In the formula, (B2,8)-- I'm assuming the 8 is the character length of the account number. Would this affect the results if the length of the transaction description is long or short (i.e. "Acct Xfer 12345678" vs. Account Transfer 12345678")?

 

[problemss]

The 8 will give you the last 8 characters. As long as the account# is always 8 characters you should be fine. Can you post the example of the ones that did not work?

 

[problemss]

Also for the ones that did not work, use the 'Evaluate Formula' button in the formulas ribbon to see why and where it is failing.

 

[justxan]

Also for the ones that did not work, use the 'Evaluate Formula' button in the formulas ribbon to see why and where it is failing.

After looking at it again, the lines didn't match because of typos. The accounts that didn't match look similar but actually have transposed or missing numbers. After sorting it all, everything worked out beautifully. I'm able to eliminate hours of manual research. Thanks so much!

 

[RonB1111]

You're welcome! I'm glad you figured it out.