Lookup within a range

[Carissa]

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!

 

[Sheeloo]

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]

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!

 

[Max]

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>=1050),),0))

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---