# vlookup with approximate values

#### lezawang

##### Well-known Member
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.

### 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
To use an approximate match the table must be sorted lowest to highest

#### Marcelo Branco

##### MrExcel MVP
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.

Replies
7
Views
658
Replies
3
Views
247
Replies
3
Views
184
Replies
1
Views
512
Replies
7
Views
334