Results 1 to 8 of 8
  1. #1
    New Lounger
    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    New Lounger
    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?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    New Lounger
    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?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 649 Times in 592 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
    Attached Files Attached Files
    Last edited by Maudibe; 2014-02-20 at 23:40. Reason: add file

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

Posting Permissions

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