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

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

Damon,

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

Thanks,

Randy

Reply | Quote

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)

Reply | Quote

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

Andrew C

Reply | Quote

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

Reply | Quote

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

0	0
26	1
76	0
101	2
126	0
326	3
351	0
476	4
501	0

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

=VLOOKUP(A1,Sheet1!A1:B9,2,TRUE)

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

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

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

=mytablelookup(A1,Sheet1!A1:C4)

Reply | Quote

I vote for Legare’s VLOOKUP suggestion. That’s the elegant way to solve this puzzle.

Reply | Quote

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

Reply | Quote