Results 1 to 8 of 8
Thread: excel if and ranges question

20140220, 12:59 #1
 Join Date
 Feb 2014
 Posts
 6
 Thanks
 0
 Thanked 0 Times in 0 Posts
excel if and ranges question
hi folks
I have been asked to make prepare formula that identify an account number as being a particular category; cash, accounts receivable.... there are 34 different choices.
There are lots of uses of the account numbers so I am thinking I want to use a mid function to pull the account number from the string containing it
I think I then want to vlookup the account against a list of accounts and their catagories.
I have ranges identifying an account as being cash 100000  109999 Accounts receivable 110000  119999 etc
my formula so far is
=IF(AND(I2>=D$4,I2<=E$4),F$4,IF(AND(I2>=D$5,I2<=E$ 5)etc etc.
d4 =10000 e4 = 109999
d5 = 110000 e4 = 119999
f4 and f5 have the first two category labels on them
i2 = the first account number
the second formula then will be i3 > and < etc
with 34 catagories this formula is going to get long fast
there must be a different / better formula to use.
any help is appreciated.

20140220, 13:35 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
How about:
=VLOOKUP(I2,$D$4:$F$38,3)
Steve

20140220, 14:18 #3
 Join Date
 Feb 2014
 Posts
 6
 Thanks
 0
 Thanked 0 Times in 0 Posts
perfect thanks Steve. I knew there would be something I was overlooking. To learn a little the D:F is the "between" part of the formula I was looking for?

20140220, 17:09 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
The E col is irrelevant. You could delete it and just use D for the lookeup and have the value in E (the formula would be:
=VLOOKUP(I2,$D$4:$E$38,2)
Since you want to lookup and get the 2nd column.
The "between" is from one row to the next, not within items in the same row (as your IFs worked). The function starts at the first entry in the list (D4) and compares it to I2. It stops when it finds an exact match or a number greater than I2. The row with the value =I2 or the row BEFORE the value >I2 is "chosen" and the value in the 3rd (or 2nd depending on the range and formula you use) column (with the lookup column D counted as the 1st column) is returned.
Steve

20140220, 17:14 #5
 Join Date
 Feb 2014
 Posts
 6
 Thanks
 0
 Thanked 0 Times in 0 Posts
so this will only work if the ranges I was provided with are contiguous and in a sorted order
cash 100000
accounts receivable 110000
next 120000
next 130000
will work
cash 100000
accounts receivable 110000
next 120000
next 115000
would not work?

20140220, 18:03 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Yes they must be ascending. You can do an exact match for any order, but you must have each individual value and add the optional 4th parameter as FALSE (if omitted the TRUE for sorted in ascending order is assumed).
If the list is not in ascending order you can get odd results
Steve

20140220, 23:32 #7
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,687
 Thanks
 121
 Thanked 667 Times in 608 Posts
lanr,
Would this User Defined Function (UDF) work for you? In this example, Enter in Cell H2 the formula =Category(I2) then copy down column H. Column I has random sample account numbers from 10000 to 449999. Your category ranges and names can be what ever you like in D4 to F38. You can have as many account numbers as you like in Column I in any order. The UDF will return the appropriate category and place next to each account number in column H. Modify the code as needed.
HTH,
Maud
lookup1.png
Place the code in a standard module
Code:Public Function Category(num As Double) As String For I = 4 To 38 If num >= Cells(I, 4) And num <= Cells(I, 5) Then Category = Cells(I, 6) Next I End Function
Last edited by Maudibe; 20140220 at 23:40. Reason: add file

20140221, 09:20 #8
 Join Date
 May 2003
 Posts
 29
 Thanks
 3
 Thanked 1 Time in 1 Post
It's the LOOKUP table that needs to be in order, and you have said you only have 34 choices, so it sounds as though the list exists. You need to have the earliest possible account number in the first column and it's description in the second column. You say you can get the account number from the string so it's then
=vlookup(AccountNumber,ListofNumbersWithDescriptio ns,2)