Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A challange (index?/array?) (2000)

    I have a list of number ranges on one sheet, with a reference number assigned to each range. I'm trying to learn how to use a function so I could enter a number on another sheet, and if it falls within one of the ranges would put the corresponding ref# next to it.
    (Sheet1)
    Ref# Low High
    1.....26.......75
    2....101....125
    3....326....350
    4....476....500

    (Sheet2) Ex: enter 27, would fill in "1", etc.
    Input Ref#
    27.....1
    70.....1
    123.....2
    340.....3
    483.....4
    499.....4

    Thanks for any help,

    Randy

  2. #2
    New Lounger
    Join Date
    May 2002
    Location
    Colorado, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A challange (index?/array?) (2000)

    Hi ghh3rd,

    It is not obvious to me how to accomplish this with worksheet functions, but this would be a snap with a custom user-defined function (UDF). Are you willing to entertain such a solution?

    Damon

  3. #3
    Star Lounger
    Join Date
    Feb 2002
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A challange (index?/array?) (2000)

    Damon,

    I'd love to give that a try, but am unfamiliar with them. Can you give me some pointers?

    Thanks,

    Randy

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: A challange (index?/array?) (2000)

    Where the value on Sheet 2 is in cell A5, in the precise example you have

    =MATCH(A5,Sheet1!$B$4:$B$7)

    works. However, if the numbers 1234 down the column on Sheet1 were instead 4321, you'd need

    =OFFSET(Sheet1!$A$3,MATCH(A5,Sheet1!$B$4:$B$7),0)
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: A challange (index?/array?) (2000)

    What value would you like to return if 90 was entered ?

    Andrew C

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A challange (index?/array?) (2000)

    Let A1:C5 in Sheet1 house the sample you provided.

    Let A1:A7 in Sheet2 house the input data for which the associated Ref#'s must be retrieved from the data in Sheet1.

    In B2 in Sheet2 enter and copy down:

    =SUMPRODUCT((A2>=Sheet1!$B$2:$B$5)*(A2<=Sheet1!$C$ 2:$C$5)*(Sheet1!$A$2:$A$5))

    Aladin
    Attached Files Attached Files
    Microsoft MVP - Excel

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A challange (index?/array?) (2000)

    If you are willing to change your table a little so that it looks like this:

    <pre>0 0
    26 1
    76 0
    101 2
    126 0
    326 3
    351 0
    476 4
    501 0
    </pre>


    Then you could use this formula on Sheet2 to look up the value in cell A1:

    <pre>=VLOOKUP(A1,Sheet1!A1:B9,2,TRUE)
    </pre>


    If you have to stay with that table format, then you could use the following User Defined Function:

    <pre>Public Function MyTableLookup(lVal As Long, oTable As Range) As Variant
    Dim I As Long
    With oTable.Item(1, 1)
    For I = 0 To oTable.Rows.Count - 1
    If lVal >= .Offset(I, 1).Value And lVal <= .Offset(I, 2).Value Then
    MyTableLookup = .Offset(I, 0)
    Exit Function
    End If
    Next I
    End With
    MyTableLookup = CVErr(xlValue)
    End Function
    </pre>


    Then use this formula in Sheet2 to lookup the value in cell A1:

    <pre>=mytablelookup(A1,Sheet1!A1:C4)
    </pre>

    Legare Coleman

  8. #8
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: A challange (index?/array?) (2000)

    I vote for Legare's VLOOKUP suggestion. That's the elegant way to solve this puzzle.
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  9. #9
    Star Lounger
    Join Date
    Feb 2002
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A challange (index?/array?) (2000)

    Thanks for all of the replies to this post.

    Legare, your function seems to work very well for me. I wish I had the VB experience to whip up a solution like that, although I'm getting into VBA a lot in Access now.

    Thanks again!

    Randy

Posting Permissions

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