Match account numbers to 2nd column (vlookup?)

**Xx7** · 06-30-2011, 06:01 PM

I would like to match the account numbers from the "Old info" to the accounts in the "New info". Need formula in yellow area. If no account matches from New info to Old info.. then just leave blank.

The account wording is slightly different from Old and New. I don't know how to match them if the wording isn't exactly the same. Vlookup "approximate" match didn't work for me.

**tarquinious** · 07-01-2011, 07:16 AM

Firstly you need to move the Account Numbers to column B or the VLOOKUP will not work.

Then, in cell D6, paste in the following formula and copy/paste down for the range of data you want to look up.

In your spreadsheet this resulted in 3 matches.

=IF(ISERROR(VLOOKUP(E6,$A$6:$B$32,2,FALSE)),"",VLOOKUP(E6,$A$6:$B$32,2,FALSE))

I can re-post your sample spreadsheet with this in if my instructions aren't clear enough.

**TMS** · 07-01-2011, 07:31 AM

You could use INDEX/MATCH ... but most of them don't match:

=IFERROR(INDEX(A:A,MATCH(E7,B:B,0)),"")

Regards

**JBeaucaire** · 07-01-2011, 11:11 AM

Originally Posted by Xx7

I don't know how to match them if the wording isn't exactly the same. Vlookup "approximate" match didn't work for me.

You need to understand what "approximate" really means in Excel. It's used primarily for numeric tables that are sorted ascending to provide "tiered pricing". If the first tier is 0-99 and the next is 100-199, etc, the value 50 would match to the first tier, the value of 150 would match to the second tier. The "approximate" trick works by finding the next lower number in the table for the start of a tier when an exact match is not found.

This is nothing like a human mind perceives as "approximate" and your table of text strings will only match with human-like analysis. They're too different.