Return string when value between 2 numbers

[Twatwood]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]3.0
[/TD]
[TD]5.0[/TD]
[TD]HIGH
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2.1
[/TD]
[TD]2.9
[/TD]
[TD]MODERATE
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1.7
[/TD]
[TD]2.0
[/TD]
[TD]MEDIUM
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]0[/TD]
[TD]1.6[/TD]
[TD]LOW
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD]2.2
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Return the appropriate string from column C where B6 is > column A and <= column B

 

[AhoyNC]

See if this works for you.
This ia an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C
1	3	5	HIGH
2	2.1	2.9	MODERATE
3	1.7	2	MEDIUM
4	0	1.6	LOW
5
6		2.2	MODERATE
Sheet

 

[StephenCrump]

B6 is > column A and <= column B

Even if you round your numbers to the nearest 0.1, you're going to have gaps, e.g. is 1.7 LOW or MEDIUM?

I'm guessing that if you set your boundary conditions appropriately, a simple VLOOKUP or INDEX/MATCH will suffice, e.g. perhaps:

B8: =IFERROR(INDEX(B$1:B$5,MATCH(A8,A$1:A$5,-1)),"Too high!")

Excel 2010
	A	B
1	5.0	HIGH
2	3.0	MODERATE
3	2.1	MEDIUM
4	1.7	LOW
5	0.0	Too Low!
6
7	Test	Result
8	0.000	Too Low!
9	0.100	LOW
10	1.600	LOW
11	1.699	LOW
12	1.700	LOW
13	1.701	MEDIUM
14	1.710	MEDIUM
15	2.000	MEDIUM
16	2.100	MEDIUM
17	2.101	MODERATE
18	2.900	MODERATE
19	2.950	MODERATE
20	3.000	MODERATE
21	3.001	HIGH
22	5.000	HIGH
23	5.001	Too high!
24	6.000	Too high!
Sheet1

 

[mikerickson]

If you sort column A from the OP ascending rather than descending, you could use a formula like

=LOOKUP(2.2, A1:A4, C1:C4)

 

[Aladin Akyurek]

@Twatwood

If you sort the table in ascending order on column A, you can use a simple and very fast formula...

[TABLE="class: grid, width: 229"]
<tbody>[TR]
[TD]0[/TD]
[TD]1.6[/TD]
[TD]LOW[/TD]
[/TR]
[TR]
[TD]1.7[/TD]
[TD]2[/TD]
[TD]MEDIUM[/TD]
[/TR]
[TR]
[TD]2.1[/TD]
[TD]2.9[/TD]
[TD]MODERATE[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5[/TD]
[TD]HIGH[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2.2[/TD]
[TD]MODERATE[/TD]
[/TR]
</tbody>[/TABLE]

In C6 just enter:

=LOOKUP(B6,$A$1:$C$4)

 

[Aladin Akyurek]

Unsorted, this appears to work also...

In C6 just enter:

=INDEX($C$1:$C$4,MATCH(B6,$B$1:$B$4,-1))

 

[mikerickson]

Aladin, wouldn't the INDEX(..MATCH(...,-1)) require that it be sorted descending (as it is) rather than unsorted?

 

[Aladin Akyurek]

Aladin, wouldn't the INDEX(..MATCH(...,-1)) require that it be sorted descending (as it is) rather than unsorted?

I meant to say of course keep the table as is, that is, no re-sort as we suggested...

 

[Twatwood]

Thanks, this one works best for me. Appreciate the help

 

[Aladin Akyurek]

Thanks, this one works best for me. Appreciate the help

Sounds great.