Finding a value within a set of ranges

[DJ479794]

I have a set min/max levels that have a definition. For example.
1-99 = SMALL
100-499 = MEDIUM
500-999 = LARGE
1000+ = XLARGE

In a seperate column I have values that I want to find the descriptor for: For example: If I have the value = 234, Then the output of the formula should be "MEDIUM".

More Detail if needed:

Column A Values:
1
100
500
1000
Column B Values
99
499
999

Column C values
SMALL
MEDIUM
LARGE
XLarge

On A seperate sheet I have the values in column A:

236
43
499
14923
345

Same sheet in Column B I want a formula that would find the descriptor for each of the values in Column A.

[Ron Coderre]

With
On Sheet1
A1:C4 contains this list...

Please Login or Register  to view this content.

Then, this formula on Sheet2 finds the category
for the value in A1

Please Login or Register  to view this content.

Does that help?

[xld]

=INDEX(Sheet1!$C$1:$C$4,MATCH(A1,Sheet1!$A$1:$A$4))

[DJ479794]

I believe the Index/Match may work. Will it work if the values are not sequential. The reason why I ask is that the formula doesn't ever look at Column B of the range.

For example if the data was:

5-456
500-593
985-3495

[Ron Coderre]

Are you saying that the values 0 thru 4, 457 thru 499, and 594 thru 984 will never occur?
Or do you want them handled differently?


5-456
500-593
985-3495

[DJ479794]

They could occur. I was just going to treak it as "Other" or something to affect. My real data is to complex or long to go in and classify those in between values as "Other". I am ok with a #n/a value or something to affect. I can adjust accordinally.

[Ron Coderre]

I attached a workbook to help demonstrate the approach I took.

Here's the description:

Sheet1 has this lookup list in A1:C7

Please Login or Register  to view this content.

On Sheet2, this formula assigns a category to the value in A1...

Please Login or Register  to view this content.

Does that help?