Search between category limits
 

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?

Search between category limits
 

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

"LiAD" wrote:

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?

Search between category limits
 

The most common solution to your problem is to use Vlookup, as in:
=vlookup(a1,C:D,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.

"LiAD" wrote in message
...
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?

Search between category limits
 

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,IN DEX($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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LiAD" wrote in message
...
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?

Search between category limits
 

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

"Mike H" wrote:

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

"LiAD" wrote:

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?