Search between category limits

[LiAD]

Hi,

I hope this description will be clear, please forgive me if its not. I do
not know whats the easiest or neatest way to achieve this. I have a list of
data composed of input numbers, then a category column then a category limits
column, such as

A C D
0,2 1 0
0,4 2 0,3
0,6 3 0,6
4 0,9

So i have input data in A. In B I have a category, which is numerical. In
D I have a regularily increasing range of data.

I would like excel to look at the data in A, (0,2 for example) then search
through D to find which two values it lies between and then return the
category that corresponds to that input. The category is the higher ranking
number of the two values it lies between so a value of 0,1 for example is
category 2. If it is equal to a limit it takes the category that corresponds
to that limit, so 0,3 is category 2 for example.

The result output I would like to see in col E would read 2, 3, 3.

What is the best function to use to achieve this?

 

[Mike H]

Hi,

I assume that's a typo where in your data table the categories are shown in
column C and you mean column B as in your narrative. Maybe this

=OFFSET(INDEX($B$1:$B$4,MATCH(A1,$C$1:$C$4)),1,0)


Drag down as required. You don't say what you would want for (say) 0.3 so I
have assumed the higher category.

Mike

 

[Fred Smith]

The most common solution to your problem is to use Vlookup, as in:
=vlookup(a1,C,2,true)

However, you would have to rearrange your data to accommodate this. Vlookup
needs the lookup value (ie, your D column) in the first column, and the
value to be returned in columns to the right. And you'd have to change your
rows so you start with 0.3, rather than 0.

If it's required to have the category first, you can use a combination of
Index and Match to do this.

In your case, there's a mathematical relationship between category and
limits, so you can use:
=ceiling(a1/0.3,1)+1

Regards,
Fred.

 

[Bernard Liengme]

I do hope there is a more elegant answer but here is a start:

=IF(INDEX($C$1:$C$20,MATCH(A1,$C$1:$C$20,1))=A1,INDEX($B$1:$B$20,MATCH(A1,$C$1:$C$20,1)),INDEX($B$1:$B$20,MATCH(A1,$C$1:$C$20,1)+1))

best wishes

 

[LiAD]

Thanks to all for the replies. I used this one and it worked and I dont need
to re arrange tha data.

Thanks for the tips though. I've stayed away from the lookups in the past,
or just never needed thm actually but i should look at them sometime too.

Thanks