Lookup through a Range of Values in different cells

[EMarre]

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

 

[Max]

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$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

 

[Max]

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$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]

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

 

[EMarre]

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]

.. 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$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
---

 

[Max]

.. 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$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]

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

 

[EMarre]

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]

Welcome, glad it did.
Thanks for the feedback & rating.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[Max]

Welcome, glad it did.
Thanks for the feedback & rating.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik