V Look Up? or Conditional Format?

[VPLUMWOOD]

Hi,

I have created a spreadsheet that calculates the average score of something to give a number, the table below is in a sperate tab, so if the number is between 0 and 1.75 I need the look up to bring back prior attainment group 1, similarly if the average score is 1.75 to 1.99 I need it to being back 2........ there are 19 different averages (I only copied the top 4) and Im not sure how exactly is the best way to work out the formula?

Prior Attainment Group	KS1 Average points score	Average KS2 Reading Score for PAG	Average KS2 Writing Score for PAG	Average KS2 Maths Score for PAG
1​	0 to <1.75	63.01​	62.82​	63.15​
2​	>=1.75 to<2	67.27​	66.68​	67.89​
3​	>=2 to <2.25	70.64​	70.1​	71.45​
4​	>=2.25 to <2.5

 

[Fluff]

You could use something like

Fluff.xlsm
	A	B	C	D	E
1	Prior Attainment Group	KS1 Average points score
2	1	0		2.15	3
3	2	1.75
4	3	2
5	4	2.25
Data

Cell Formulas
Range	Formula
E2	E2	=INDEX(A2:A5,MATCH(D2,B2:B5,1))

 

[VPLUMWOOD]

Ive added this in - =INDEX('Prior Attainment Group'!$B$4:$B$22,MATCH(RWM!M5,'Prior Attainment Group'!$C$4:$C$22,1))

But its not working as 6.5 should be 13, 7.5 should be 15, 9.5 should be 18...........

This is the table array.....

12​	6
13​	>6 to <7
14​	7
15​	>7 to <8
16​	8
17​	>8 to <9.5
18​	>=9.5 to <10
19​	10

KS1 Attainment Average Score	Prior Attainment Group (this is the formula column)
10​	19​
10​	19​
8​	16​
6.5​	12​
6​	12​
9.5​	16​
7.5​	14​
8​	16​
8​	16​
10​	19​
8​	16​
8​	16​
9​	16​
8.5​	16​
9.5​	16​

 

[Fluff]

You need to layout column C as I showed.

 

[VPLUMWOOD]

Column c is blank?

 

[Fluff]

Then why are you trying to match M5 with column C?

 

[VPLUMWOOD]

Because M5 is where the score is that i am then trying to match to the table?

 

[Fluff]

But why are you trying to match that with values in col C if col C is empty?

 

[VPLUMWOOD]

I have an overview sheet with the Average Points for a specific child - which is in Column M, so the scores for the children are 10,10,8,6.5,6,9.5 - so I would then expect the vlookup to bring back - 19,19,16,13,12,18.

Then I have another sheet for the groupings - so I am trying to match the amount in column M on the overview sheet - (1st table below) with the corresponding value in Column C on the second sheet that has the look up.

KS1 Attainment Average Score

10​

10​

8​

6.5​

6​

9.5​

Column B	Column C
Prior Attainment Group	KS1 Average points score
1​	0 to <1.75
2​	>=1.75 to<2
3​	>=2 to <2.25
4​	>=2.25 to <2.5
5​	>=2.5 to <2.75
6​	>=2.75 to >3
7​	>=3 to <3.5
8​	>=3.5 to <=4
9​	>4 to <5
10​	5
11​	>5 to <6
12​	6
13​	>6 to <7
14​	7
15​	>7 to <8
16​	8
17​	>8 to <9.5
18​	>=9.5 to <10
19​	10

 

[Fluff]

As I said in post#4 you need to change the layout of the "KS1 Average points score" so that it's the same as I showed in post#2.