Vlookup using array ?

[Charles]

Hi,

I need some help with this.
I'm using a userform and in a text box I enter a value of "466" and in
another text box the info should be "435 - 477"
I tried to use a Vlookup, but It errors.
The data is in sheet "Data" the following is an example of it.
Emily 150 IA-1 <435 Likely Too Thin n/a
Emily 150 IA-1 435 - 477 Leave Epi 250
Emily 150 IA-1 478 - 528 Remove Epi 250
Emily 150 IA-1 529 - 573 Leave Epi 300
Emily 150 IA-1 574 - 617 Remove Epi 300
Emily 150 IA-1 618 - 672 Leave Epi 350
Emily 150 IA-1 673 - 727 Remove Epi 350
Emily 150 IA-1 >727 Likely Too Thick n/a

You can see that the value as I entered was "466" and what I want to
return to my userform is the values "435-477" because the value I
entered is between "435-477"( column D). The worksheet Vlookup failed
and I assume it's because I'm looking for a number that is between 2
numbers.

I also posted this in the "Excel" forum, but it looks like they are
having problems with it.
Any help would be appreciated.

 

[Carl Willems]

Hi, you should try using MATCH. Depending on the last parameter it will
give the row number within the range on which the data can be found.
Embedding this Match function inside a INDEX will allow you to retrieve the
value you are looking for.

Hope this helps,
Carl