Lookup Function

[Jason2121]

Can anyone help?

My Accounting Dept is going thru a conversion with the Chart of Accounts. I Can't remember how to write a Lookup so i can automatically link the old chart of accounts to the new ones. For example:

1190.7 A/R PLANTERS NET 1170
1190.8 ACCOUNTS RECEIVABLE-PTLD 1170
1190.85 A/R - RECIP COMP 1170
1190.86 A/R WIRELESS CARRIER BILL 1170
1190.9 OTHER A/R - PLANTERS CELL 1170
1190.91 AR PLANTERS COMMUNICATION 1170
1190.92 A/R - SEE-TV, LLC 1170
1190.95 A/R - SALVAGE 1170
1210 INTEREST & DIVIDENDS REC 1200
1220 MATERIALS & SUPPLIES N/E 1220
3100.99 RETIREMENT - WORK IN PROG 3100
3112 DEPR RES - MOTOR VEHICLES 3100
3116 DEPR RES - OTHER WORK EQP 3100
4020 NOTES PAYABLE-NEWINGTON 4000
4030.1 CREDITS HELD BUT REMOVED 4130

Thank you for any tips

Jason

 

[SkipVought]

Hi,

You need a table, preferably with unique headings, that has a column for the old and a column for the new acct nbr.

You never explaine what it was that you posted as an example. AND you had no HEADINGS. Tables ought ALWAYS to have headings.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Jason2121]

Sorry about that:


GL Account Description New GL Account
1190.7 A/R PLANTERS NET 1170
1190.8 ACCOUNTS RECEIVABLE-PTLD 1170
1190.85 A/R - RECIP COMP 1170
1190.86 A/R WIRELESS CARRIER BILL 1170
1190.9 OTHER A/R - PLANTERS CELL 1170
1190.91 AR PLANTERS COMMUNICATION 1170
1190.92 A/R - SEE-TV, LLC 1170
1190.95 A/R - SALVAGE 1170
1210 INTEREST & DIVIDENDS REC 1200
1220 MATERIALS & SUPPLIES N/E 1220
3100.99 RETIREMENT - WORK IN PROG 3100
3112 DEPR RES - MOTOR VEHICLES 3100
3116 DEPR RES - OTHER WORK EQP 3100
4020 NOTES PAYABLE-NEWINGTON 4000
4030.1 CREDITS HELD BUT REMOVED 4130

If need be, I can send you the excel file if it would make more sense to you? I have mutliple clients that use similar Accounts and Descrptions that flow into our new conversion chart. I have created Crystal reports that their data, once converted, will flow into.

Jason

 

[SkipVought]

Using Named Ranges, going either way...
[tt]
=INDEX(GL_Account,MATCH(NewLookupAcctNbr,New_GL_Account,),1)
=INDEX(New_GL_Account,MATCH(OldLookupAcctNbr,GL_Account,),1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Jason2121]

Thanks, this will help