Return Account Nickname if Account Number Matches

[spacle]

Hi! I need a formula that will return an account's "nickname" if the account number matches. Our reports output in a CSV file that have Proper Account Names. On our internal reports, we use account "nicknames" so that they take up less space. On the report I am working on, I have added pivot tables and a macro to do all the tedious stuff, however, in order for this to be ideal, I need a formula that will look at the account number and if it finds it in the list it will return the account nickname (from "Nicknames" tab column B) instead of the long proper name. If it does not find it in the list, I would like it to return the Proper name (from "Data" tab column B).

Here is what I have: A tab ("Nicknames") that has just two columns, Column A=Account Number and Column B=Nickname, and a tab for data ("Data") which is just the ouput from the csv file (account number is still in Column A with Proper name in Column B).

I have tried a few different types of formulas, but since the row position changes, I keep getting errors. I need it to look at the actual account number and not the placement (i.e. not match A2 to A2 but actually look at the data in the cell) since accounts get created everyday and an account number will not always be on the same row day to day.

Any help you could give would be greatly appreciated

[oeldere]

Use Vlookup to look up in the table of tab (nicknames)

e.g. Vlookup(tabB!a2,tabA!a2:b1000,2,0)

[spacle]

I am assuming that by Tab B you mean the "Data" tab and Tab A the "Nicknames" tab? If so, I just tried it and it returned the proper name as opposed to the nickname. I tried it the other was as well and just got the VALUE error.

I have done some more digging and I think I may need an INDEX+MATCH formula but I cannot seem to find the right combination to get the result I am looking for. Any ideas?

Thanks again for your help!

[Craig K.]

Try doing this. In your Data tab that has your raw data extract, add another column (ex "Preferred Name" or whatever you want) and copy this formula down for all records

Please Login or Register  to view this content.

You will now have a column in your raw data that accounts for whether you have a nickname for the account or not. And when you create your pivot, use this new field.

[Mazzaropi]

Excuse-me Craig K. I didnīt see you message in time.
----------
Spacle, Good afternoon.

Your Layout:
TAB - Nicknames
....Column A - Account Number
....Column B - Nickname

TAB - Data
....Column A - Account Number
....Column B - Proper Name

Try to do this:
TAB - Report
....Column A - Account Number
....Column B - =IF(ISERROR(VLOOKUP(A2,Nicknames!A2:B5,2,0)),VLOOKUP(A2,Data!A2:B5,2,1),VLOOKUP(A2,Nicknames!A2:B5,2,0))


Please, tell us if it worked for you.

I hope this helps.

Have a nice day.


Best wishes from Brazil!

[spacle]

This is perfect! The only change I made was to add dollar signs to make the A2 cell stay constant as I pasted the formula down a long column. Thank you so much for all your help. This will save me me a ton of time!

P.S. Sorry it took so long to respond-I got put on another project and couldn't get back to this one until today. Thanks again!