[Solved] Assistance with Vlookup to match old accounts to new accounts

Hi all,

I am doing an internship and have been given a task involving macros to which I could use some help. I apologize if I confuse you with my description.

We are currently switching accounting systems and I am interested in a macro that takes the old account number and in a separate column gives me the new account number.

There is a workbook that lists the old account numbers and their corresponding new account numbers. In a separate workbook I have 88 worksheets for various different projects that need the accounts to be matched with the new account numbers.

Essentially, I need a macro that say if I have "xxxx" in A# then I# = "xxxx". If A# is empty then I# is empty.

Here is an example:
Book1.xlsx

Thanks in advance for your help,
Mike

So just to get this right in my head, are you looking for a macro that will cycle through all of the worksheets(88) and for any row with a value in column A, lookup the new value and put it in column I?

I assume where in the Old Accounts you have 2 numbers seperated by a slash that any account with either of those numbers will take on the single new number? eg 6405 and 6400 both become 5420

if you can reorganise the reference table to remove the / entries ie split those into 2 rows then VLOOKUP will be your friend. if not then it'll need a bit of VBA

Basically, create a table of Old and New Accounts in Old Number order, give the range a name (ive used "accNumbers"), you do this by highlighting all of the range (minus the headers) and typing a name in the box that normally shows the cell reference. Then refer to it in a VLOOKUP in column I

=IFNA(VLOOKUP(A2,accNumbers,2,FALSE),"")

Ive wrapped the VLOOKUP in an IFNA to give blanks where no resulting value is found.

Ive done the first sheet for you in your example, simply copy and paste the formula above into I2 of the each sheet and drag it down to the bottom.

Mike, welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.