vlookup with approximate values

[lezawang]

Hi
The vlookup = =VLOOKUP(B2,$H$2:$I$5,2,1) with approximate values is working if the table like this

This table start H2:I5

average	grade
0	f
50	c
60	b
80	a

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

name	average	grade
mary1	91	a
mary2	40	f
mary3	12	f
mary4	76	b

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

but is I change the table above to

average	grade
80	a
60	b
50	c
0	f

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

then I get wrong answers

name	average	grade
mary1	91	f
mary2	40	#N/A
mary3	12	#N/A
mary4	76	f

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

Why is that? Thank you so much.

 

[Fluff]

To use an approximate match the table must be sorted lowest to highest

 

[Marcelo Branco]

Maybe something like this...

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	name​	average​	grade​					average​	grade​
2​	mary1​	91​	a​					80​	a​
3​	mary2​	40​	f​					60​	b​
4​	mary3​	12​	f​					50​	c​
5​	mary4​	76​	b​					0​	f​
6​	mary5​	60​	b​

Formula in C2 copied down
=INDEX(I$2:I$5,IFERROR(MATCH(B2,H$2:H$5,-1)+1*ISNA(MATCH(B2,H$2:H$5,0)),1))

M.