vlookup with approximate values

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,569
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
averagegrade
0f
50c
60b
80a

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

nameaveragegrade
mary191a
mary240f
mary312f
mary476b

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

but is I change the table above to

averagegrade
80a
60b
50c
0f

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

then I get wrong answers

nameaveragegrade
mary191f
mary240#N/A
mary312#N/A
mary476f

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

Why is that? Thank you so much.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
To use an approximate match the table must be sorted lowest to highest
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,397
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,930
Messages
5,545,083
Members
410,652
Latest member
Zot
Top