# Thread: excel if and ranges question

1. ## 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.

=VLOOKUP(I2,\$D\$4:\$F\$38,3)

Steve

3. 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?

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

5. 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?

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

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

8. 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)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•