Fill the values regardnig the condition

[milos]

Hi, I have a table with norms:
a b

1 10 over65
2 9 65
3 8 58
4 7 50
5 6 43
6 5 36
7 4 29
8 3 22
92 14
101 7

And I have a table of results
c d
1 35 x1
2 15 x2
3 13 x3
4 23 x4
5 32 x5
6 35 x6
etc.
x1-x6 should be filled automatically with the proper category - if first
result is 35, then the category (x1) should be 5 (it's more then 29, but less
then 36). x2 should be 3, x3 - 2 etc. how can i write conditions for this.
thanx

[hans bal(nl)]

Invert your table like this :
A B C
0 1
10 7 2
9 14 3
8 22 4
7 29 5
6 36 6
5 43 7
4 50 8
3 58 9
2 65 10
1 Over 65

And then use a lookup formula : =VLOOKUP(E3,$B$1:$C$11,2,1)

( assuming that your results table starts in Cell E3)

HTH





"milos" wrote:

> Hi, I have a table with norms:
> a b
>
> 1 10 over65
> 2 9 65
> 3 8 58
> 4 7 50
> 5 6 43
> 6 5 36
> 7 4 29
> 8 3 22
> 92 14
> 101 7
>
> And I have a table of results
> c d
> 1 35 x1
> 2 15 x2
> 3 13 x3
> 4 23 x4
> 5 32 x5
> 6 35 x6
> etc.
> x1-x6 should be filled automatically with the proper category - if first
> result is 35, then the category (x1) should be 5 (it's more then 29, but less
> then 36). x2 should be 3, x3 - 2 etc. how can i write conditions for this.
> thanx

[Pete_UK]

Enter this formula in D1:

=INDEX(A$1:A$10,MATCH(C1,B$1:B$10,-1))

and copy down for as many values as you have in column C.

I would suggest that you change your entry in B1 to 110 (or something
similar) rather than the text "over65".

Hope this helps.

Pete

milos wrote:
> Hi, I have a table with norms:
> a b
>
> 1 10 over65
> 2 9 65
> 3 8 58
> 4 7 50
> 5 6 43
> 6 5 36
> 7 4 29
> 8 3 22
> 92 14
> 101 7
>
> And I have a table of results
> c d
> 1 35 x1
> 2 15 x2
> 3 13 x3
> 4 23 x4
> 5 32 x5
> 6 35 x6
> etc.
> x1-x6 should be filled automatically with the proper category - if first
> result is 35, then the category (x1) should be 5 (it's more then 29, but less
> then 36). x2 should be 3, x3 - 2 etc. how can i write conditions for this.
> thanx