Help, Vlookup not working correctly

[EmmaN]

Hi all, I'm having problems with a vlookup formula, I am trying to return a grade in column J using the value in column J. My formula keeps returning the last line from my lookup table (H20 = z), it should be pulling an F as the value is 31.

I have tried a Vlookup and an IF function. Both return the same incorrect value from the last line of my lookup table (z). Any suggestions greatfully recieved.

An example of the data i have is as follows:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	IGCSE English Lit 2016-18	Gender	Set	Teacher	Poetry Coursework	Poetry Weighted	Drama	Prose	Mock Total	Literature Total	Grade	OCT REPORT OTF	Lit target	MPGRADE	MTGRADE
2
3	Candidate A	F	1	JL	23	31			0	31	z	A	A*
4	Candidate B	F	1	JL	24	32			0	32	z	A	A*
5	Candidate C	F	1	JL	23	31			0	31	z	A	A*
6	Candidate D	F	1	JL	24	32			0	32	z	A	A*
	Lookup table. 'Data' Sheet
	F	G	H
2	Lit		=IF(J3=0,"",VLOOKUP(J3,Data!$F$3:$H$20,3,TRUE))
3	0	0	U		=VLOOKUP(J3,Data!$F$3:$H$20,3,TRUE))
4	0.17	17	U
5	0.18	18	G
6	0.24	24	G
7	0.25	25	F
8	0.32	32	F
9	0.33	33	E
10	0.4	40	E
11	0.41	41	D
12	0.48	48	D
13	0.49	49	C
14	0.57	57	C
15	0.58	58	B
16	0.67	67	B
17	0.68	68	A
18	0.77	77	A
19	0.78	78	z
20	1	100	z

<tbody>
</tbody>

Thank you. Emma

 

[Asman_Kei]

Try

[COLOR=#333333]=VLOOKUP(J3,Data!$G$3:$H$20,2,TRUE))[/COLOR]

This will look for the Column J value in Column G (notice I changed the look table array to start with $G$3) and then retrieve the Letter Grade in column H (I also changed the third parameter to 2).

 

[Aladin Akyurek]

You seem to need:

=VLOOKUP(J3,Data!$G$3:$H$20,2,TRUE)

 

[EmmaN]

Try

[COLOR=#333333]=VLOOKUP(J3,Data!$G$3:$H$20,2,TRUE))[/COLOR]

This will look for the Column J value in Column G (notice I changed the look table array to start with $G$3) and then retrieve the Letter Grade in column H (I also changed the third parameter to 2).

THANK YOU SO MUCH!!! This was driving me round the bend. Emma