Cell Text display for different criterias - help!

[spike87]

Hello

I have a spreadsheet where I have a value in one cell and a range of 'outcomes' in a small table.

The idea of the spreadsheet is that if a value is in one cell (called Hours), the cell next to it needs to display a relevant 'level' if it fits in between the range.

For example; Here is the range of 'Levels' and hours

	Column H	Column I
Row 20	Level	Hours up to:
Row 21	0	0.5
Row 22	1	3.5
Row 23	2	10.5
Row 24	3	15
Row 25	4	21
Row 26	5	31

<tbody>
</tbody>

For example; In cell D3, i have a figure (say '12.0') - I need to have a forumla so that the cell next to it (cell E3) will show the correct level from the table above (so for the example of 12.0, it will show the words 'Level 3' as it's greater than 10.5 [Level 2] but less than 15.0 [Level 3] so fits into the Level 3 criteria).

I need this to be a forumla so that if the hours in D3 were to change, then the level can change if it moves to between a different critera

Can anyone help?

 

[carpy1985]

Change your columns around... Then in e3

="Level "&vlookup(d3,h21:i26,2,TRUE)

This will lookup the hours and find the first level that is too big and drop back to the next one as its an approx match not exact

 

[shift-del]

Hi

Here are two ways.

	D	E	F	G	H	I
2	Hours up to	Level	Level
3	12	3	3
4
19
20					Level	Hours up to
21					0	0,5
22					1	3,5
23					2	10,5
24					3	15
25					4	21
26					5	31

<colgroup><col style="width: 28ptpx"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"></colgroup><tbody>
</tbody>

Zelle	Formel
E3	=INDEX(H21:H26,COUNT(I21:I26)-COUNTIF(I21:I26,">="&D3)+1)
F3	=LARGE(H21:H26,COUNTIF(I21:I26,">="&D3))

<colgroup><col style="width: 40ptpx"><col></colgroup><tbody>
</tbody>

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>