# Thread: A challange (index?/array?) (2000)

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

5. ## Re: A challange (index?/array?) (2000)

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

Andrew C

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

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

8. ## Re: A challange (index?/array?) (2000)

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

9. ## 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
•