| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup through a Range of Values in different cells
I have a detail General Ledger Account data file (please see below) that
needs to have a column (FRPT MAPPING) to map each account transaction to a specific summary account grouping that exist in another worksheet within the same file. Please notice that the account mapping worksheet uses the same mapping for a range of accounts and funds. Can someone help me determine how can I perform the lookup so I can assign the summary account category to each transaction in the GL data file? GL data file FUND DEPT ACCOUNT ACCOUNT DESCRIPTION FRPT MAPPING 100 101000 CASH GENERAL ACCOUNT 100 100 101000 CASH GENERAL ACCOUNT 100 101 101000 CASH GENERAL ACCOUNT 100 110 101000 CASH GENERAL ACCOUNT 100 120 101000 CASH GENERAL ACCOUNT Account Summary Grouping Table MAPPING FND FRM FND TO ACCT FRM ACCT TO CASH AND CASH EQUIVALENTS LINE MAPPING 100 100 101000 101999 LINE MAPPING 101 101 101000 101999 LINE MAPPING 102 102 101000 101999 Thanks in advance for any help on this issue. Emarre |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup through a Range of Values in different cells
Assuming the "Account Summary Grouping Table" is in sheet: x,
where cols D and E houses the account ranges eg: ACCT FRM ACCT TO 101000 101999 102000 102999 etc (account data are assumed real numbers) with col A containing the corresponding mappings to be extracted over Then in the GL's data sheet (this sheet is assumed in the same book), where accounts are listed in C2 down, eg: 101000, 101500, 102300, etc (these account data are also assumed real numbers) You could put this expression in say, E2, press normal ENTER to confirm: =INDEX(x!A$2:A$10,MATCH(1,INDEX((C2=x!D$2:D$10)*( C2<=x!E$2:E$10),),0)) Copy E2 down to return the required mapping results from col A in sheet: x. Adapt the ranges to suit the extents of your actual data. Success? High five it by clicking the YES button below. -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:370 Subscribers:68 xdemechanik --- "EMarre" wrote: I have a detail General Ledger Account data file (please see below) that needs to have a column (FRPT MAPPING) to map each account transaction to a specific summary account grouping that exist in another worksheet within the same file. Please notice that the account mapping worksheet uses the same mapping for a range of accounts and funds. Can someone help me determine how can I perform the lookup so I can assign the summary account category to each transaction in the GL data file? GL data file FUND DEPT ACCOUNT ACCOUNT DESCRIPTION FRPT MAPPING 100 101000 CASH GENERAL ACCOUNT 100 100 101000 CASH GENERAL ACCOUNT 100 101 101000 CASH GENERAL ACCOUNT 100 110 101000 CASH GENERAL ACCOUNT 100 120 101000 CASH GENERAL ACCOUNT Account Summary Grouping Table MAPPING FND FRM FND TO ACCT FRM ACCT TO CASH AND CASH EQUIVALENTS LINE MAPPING 100 100 101000 101999 LINE MAPPING 101 101 101000 101999 LINE MAPPING 102 102 101000 101999 Thanks in advance for any help on this issue. Emarre |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup through a Range of Values in different cells
Max,
Thanks so much for your help. You are a lifesaver... It works great.. however there is a minor twist that I didn't mentioned and I am sorry for that. Please notice there are two other columns with another range that need to be taken into consideration for the lookup. The columns are the 'FND FROM' AND 'FND TO' in the Account Summary Grouping table. If you can include this in the formula I will sincerely appreciate it. Thanks again, EMarre "Max" wrote: Assuming the "Account Summary Grouping Table" is in sheet: x, where cols D and E houses the account ranges eg: ACCT FRM ACCT TO 101000 101999 102000 102999 etc (account data are assumed real numbers) with col A containing the corresponding mappings to be extracted over Then in the GL's data sheet (this sheet is assumed in the same book), where accounts are listed in C2 down, eg: 101000, 101500, 102300, etc (these account data are also assumed real numbers) You could put this expression in say, E2, press normal ENTER to confirm: =INDEX(x!A$2:A$10,MATCH(1,INDEX((C2=x!D$2:D$10)*( C2<=x!E$2:E$10),),0)) Copy E2 down to return the required mapping results from col A in sheet: x. Adapt the ranges to suit the extents of your actual data. Success? High five it by clicking the YES button below. -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:370 Subscribers:68 xdemechanik --- "EMarre" wrote: I have a detail General Ledger Account data file (please see below) that needs to have a column (FRPT MAPPING) to map each account transaction to a specific summary account grouping that exist in another worksheet within the same file. Please notice that the account mapping worksheet uses the same mapping for a range of accounts and funds. Can someone help me determine how can I perform the lookup so I can assign the summary account category to each transaction in the GL data file? GL data file FUND DEPT ACCOUNT ACCOUNT DESCRIPTION FRPT MAPPING 100 101000 CASH GENERAL ACCOUNT 100 100 101000 CASH GENERAL ACCOUNT 100 101 101000 CASH GENERAL ACCOUNT 100 110 101000 CASH GENERAL ACCOUNT 100 120 101000 CASH GENERAL ACCOUNT Account Summary Grouping Table MAPPING FND FRM FND TO ACCT FRM ACCT TO CASH AND CASH EQUIVALENTS LINE MAPPING 100 100 101000 101999 LINE MAPPING 101 101 101000 101999 LINE MAPPING 102 102 101000 101999 Thanks in advance for any help on this issue. Emarre |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup through a Range of Values in different cells
.. there are two other columns with another range that
need to be taken into consideration for the lookup. The columns are the 'FND FROM' AND 'FND TO' in the Account Summary Grouping table Just expand the criteria like this in the GL's data sheet's E2: =INDEX(x!A$2:A$10,MATCH(1,INDEX((A2=x!B$2:B$10)*( A2<=x!C$2:C$10)*(C2=x!D$2:D$10)*(C2<=x!E$2:E$10), ),0)) where 'FND FROM' & 'FND TO' data are in cols B and C in Acc Summary (sheet: x) and A2 down houses the Fund#s in the GL's data sheet Success moment? Celebrate it, do click the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "EMarre" wrote: Max, Thanks so much for your help. You are a lifesaver... It works great.. however there is a minor twist that I didn't mentioned and I am sorry for that. Please notice there are two other columns with another range that need to be taken into consideration for the lookup. The columns are the 'FND FROM' AND 'FND TO' in the Account Summary Grouping table. If you can include this in the formula I will sincerely appreciate it. Thanks again, EMarre |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup through a Range of Values in different cells
Max,
Once again, thanks so much for your help. It works perfect! I will be more than happy to select 'Yes' in the helpful post request. EMarre "Max" wrote: .. there are two other columns with another range that need to be taken into consideration for the lookup. The columns are the 'FND FROM' AND 'FND TO' in the Account Summary Grouping table Just expand the criteria like this in the GL's data sheet's E2: =INDEX(x!A$2:A$10,MATCH(1,INDEX((A2=x!B$2:B$10)*( A2<=x!C$2:C$10)*(C2=x!D$2:D$10)*(C2<=x!E$2:E$10), ),0)) where 'FND FROM' & 'FND TO' data are in cols B and C in Acc Summary (sheet: x) and A2 down houses the Fund#s in the GL's data sheet Success moment? Celebrate it, do click the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "EMarre" wrote: Max, Thanks so much for your help. You are a lifesaver... It works great.. however there is a minor twist that I didn't mentioned and I am sorry for that. Please notice there are two other columns with another range that need to be taken into consideration for the lookup. The columns are the 'FND FROM' AND 'FND TO' in the Account Summary Grouping table. If you can include this in the formula I will sincerely appreciate it. Thanks again, EMarre |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup through a Range of Values in different cells
Welcome, glad it did.
Thanks for the feedback & rating. -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "EMarre" wrote in message ... Max, Once again, thanks so much for your help. It works perfect! I will be more than happy to select 'Yes' in the helpful post request. EMarre |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup and return range of values | Excel Worksheet Functions | |||
| Lookup within a range of values | Excel Discussion (Misc queries) | |||
| Lookup 3 matching values of which one is a range and return a sum | Excel Worksheet Functions | |||
| Lookup a Canada postal code in a range of values | Excel Worksheet Functions | |||
| How do I use LOOKUP to return a range of values, then SUM values? | Excel Worksheet Functions | |||
Linear Mode
