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
[TABLE="class: grid, width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]average[/TD]
[TD="width: 64"]grade[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD="align: right"]60[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD="align: right"]80[/TD]
[TD]a[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]name[/TD]
[TD="width: 64"]average[/TD]
[TD="width: 64"]grade[/TD]
[/TR]
[TR]
[TD]mary1[/TD]
[TD="align: right"]91[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]mary2[/TD]
[TD="align: right"]40[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]mary3[/TD]
[TD="align: right"]12[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]mary4[/TD]
[TD="align: right"]76[/TD]
[TD]b[/TD]
[/TR]
</tbody>[/TABLE]

but is I change the table above to

[TABLE="class: grid, width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]average[/TD]
[TD="width: 64"]grade[/TD]
[/TR]
[TR]
[TD="align: right"]80[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD="align: right"]60[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]f[/TD]
[/TR]
</tbody>[/TABLE]

then I get wrong answers

[TABLE="class: grid, width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]name[/TD]
[TD="width: 64"]average[/TD]
[TD="width: 64"]grade[/TD]
[/TR]
[TR]
[TD]mary1[/TD]
[TD="align: right"]91[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]mary2[/TD]
[TD="align: right"]40[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]mary3[/TD]
[TD="align: right"]12[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]mary4[/TD]
[TD="align: right"]76[/TD]
[TD]f[/TD]
[/TR]
</tbody>[/TABLE]

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...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

name​

[/td][td]

average​

[/td][td]

grade​

[/td][td][/td][td][/td][td][/td][td][/td][td]

average​

[/td][td]

grade​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

mary1​

[/td][td]

91​

[/td][td]

a​

[/td][td][/td][td][/td][td][/td][td][/td][td]

80​

[/td][td]

a​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

mary2​

[/td][td]

40​

[/td][td]

f​

[/td][td][/td][td][/td][td][/td][td][/td][td]

60​

[/td][td]

b​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

mary3​

[/td][td]

12​

[/td][td]

f​

[/td][td][/td][td][/td][td][/td][td][/td][td]

50​

[/td][td]

c​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

mary4​

[/td][td]

76​

[/td][td]

b​

[/td][td][/td][td][/td][td][/td][td][/td][td]

0​

[/td][td]

f​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

mary5​

[/td][td]

60​

[/td][td]

b​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.