Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup within a range
I have a list of accounts, and a table that gives a range of accounts and
then the corresponding grouping. I need to assign a grouping to each value in my list of accounts. The grouping table has about 6000 lines, so I need to use some kind of lookup function to do the assignment. The data looks something like this: Accounts: Grouping: 0001-1000 Loans 1001-2000 Deposits 2001-3000 Equity and so on... Any suggestions on how to do a lookup on this data would be much appreciated! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup within a range
Enter 1, 1001, 2001,... in Col A (A1, A2,...)
In B enter Loans, Deposits,... (in B1, B2,..) i.e. instead of 1-1000, 1001-2000 simply have 1, 1001, 2001,... Now with account number in C1 =VLOOKUP(C1,A:B,2,TRUE) will give you Loans, Deposits,... as appropriate "Carissa" wrote: I have a list of accounts, and a table that gives a range of accounts and then the corresponding grouping. I need to assign a grouping to each value in my list of accounts. The grouping table has about 6000 lines, so I need to use some kind of lookup function to do the assignment. The data looks something like this: Accounts: Grouping: 0001-1000 Loans 1001-2000 Deposits 2001-3000 Equity and so on... Any suggestions on how to do a lookup on this data would be much appreciated! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup within a range
Unfortunately that didn't work, but I found a work around. I seperated the
min and max account values into two columns. Then I used the formula below (ex. to look for account #1050 wich would fall into the 1001-2000 range) SUMPRODUCT(--(A1:A3<=1050),--(B1:B3=1050),C1:C3) Account Min: Account Max: Grouping: 0001 1000 Loans 1001 2000 Deposits 2001 3000 Equity Thanks! "Sheeloo" wrote: Enter 1, 1001, 2001,... in Col A (A1, A2,...) In B enter Loans, Deposits,... (in B1, B2,..) i.e. instead of 1-1000, 1001-2000 simply have 1, 1001, 2001,... Now with account number in C1 =VLOOKUP(C1,A:B,2,TRUE) will give you Loans, Deposits,... as appropriate "Carissa" wrote: I have a list of accounts, and a table that gives a range of accounts and then the corresponding grouping. I need to assign a grouping to each value in my list of accounts. The grouping table has about 6000 lines, so I need to use some kind of lookup function to do the assignment. The data looks something like this: Accounts: Grouping: 0001-1000 Loans 1001-2000 Deposits 2001-3000 Equity and so on... Any suggestions on how to do a lookup on this data would be much appreciated! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup within a range
Curious, as your desired return from col C are text (not numbers)
doesn't your sumproduct expression: SUMPRODUCT(--(A1:A3<=1050),--(B1:B3=1050),C1:C3) return a zero? I'd use this more generic index/match to do the job, normal ENTER: =INDEX(C1:C3,MATCH(1,INDEX((A1:A3<=1050)*(B1:B3=1 050),),0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "Carissa" wrote: Unfortunately that didn't work, but I found a work around. I seperated the min and max account values into two columns. Then I used the formula below (ex. to look for account #1050 wich would fall into the 1001-2000 range) SUMPRODUCT(--(A1:A3<=1050),--(B1:B3=1050),C1:C3) Account Min: Account Max: Grouping: 0001 1000 Loans 1001 2000 Deposits 2001 3000 Equity |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function Range Lookup AGAIN! | Excel Worksheet Functions | |||
Lookup range help | Excel Discussion (Misc queries) | |||
help with lookup and range | Excel Worksheet Functions | |||
Lookup in a range. | Excel Discussion (Misc queries) | |||
Range Lookup | Excel Discussion (Misc queries) |