Check if number is within a range and return the value in colomn c in the same row

[vrblakelock]

Hi,

Sheet 1 has a number [Column A]. i am after a formula that looks that number up in sheet 2, see's if it is within the range, and returns the 3rd column of that row.
See below for sheet contents

Sheet 1

	A	B
1	Daily Occupied Head Count	LEVEL
2	201
3	109
4	300

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Sheet 2

Minimum	Maximum	Level
0	200	level 1
201	250	level 2
251	325	level 3
326	400	level 4
401	500	level 5
501	600	level 6
601	700	level 7
701	800	level 8
801	1000	level 9
1001	10000	level 10

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

Thanks
Vince

 

[AhoyNC]

Like this?

Excel Workbook
	A	B
1	Daily Occupied Head Count	LEVEL
2	201	level 2
3	109	level 1
4	300	level 3
5	500	level 5
6	754	level 8
7	929	level 9
Sheet1

Excel Workbook
	A	B	C
1	Minimum	Maximum	Level
2	0	200	level 1
3	201	250	level 2
4	251	325	level 3
5	326	400	level 4
6	401	500	level 5
7	501	600	level 6
8	601	700	level 7
9	701	800	level 8
10	801	1000	level 9
11	1001	10000	level 10
Sheet2

 

[vrblakelock]

LOL. Thanks again

I thought it would be a complicated if statement, but obviously since the range is specifically defined, i can just use the vlookup function using the approx match syntax

Thanks!
Vince

Like this?

Sheet1

*	A	B
1	Daily Occupied Head Count	LEVEL
2	201	level 2
3	109	level 1
4	300	level 3
5	500	level 5
6	754	level 8
7	929	level 9

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:150px;"><col style="width:93px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 =VLOOKUP(A2,Sheet2!$A$2:$C$11,3,1) B3 =VLOOKUP(A3,Sheet2!$A$2:$C$11,3,1) B4 =VLOOKUP(A4,Sheet2!$A$2:$C$11,3,1) B5 =VLOOKUP(A5,Sheet2!$A$2:$C$11,3,1) B6 =VLOOKUP(A6,Sheet2!$A$2:$C$11,3,1) B7 =VLOOKUP(A7,Sheet2!$A$2:$C$11,3,1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4


Sheet2

*	A	B	C
1	Minimum	Maximum	Level
2	0	200	level 1
3	201	250	level 2
4	251	325	level 3
5	326	400	level 4
6	401	500	level 5
7	501	600	level 6
8	601	700	level 7
9	701	800	level 8
10	801	1000	level 9
11	1001	10000	level 10

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:95px;"><col style="width:99px;"><col style="width:86px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4