vlookup a value not existing the table returning last value

[lezawang]

Hi

I have table like below, so I wrote vlookup function. user will enter id # and vlookup will return the salary. the vlookup function works no problem. then I tried to enter id = 100 which is not existed in the table, vlookup returned to me the last salary 17K. I tried different ID not existed in the range and vlookup keeps returning the last salary! why is that? I expected #value message or something like that. Thank you so much.

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]id[/TD]
[TD="width: 64"]first[/TD]
[TD="width: 64"]last[/TD]
[TD="width: 64"]dept[/TD]
[TD="width: 64"]salary[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]john1[/TD]
[TD]clark1[/TD]
[TD]it[/TD]
[TD]43 k[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]mary1[/TD]
[TD]jones1[/TD]
[TD]hr[/TD]
[TD]38 k[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]alex1[/TD]
[TD]wilson1[/TD]
[TD]sales[/TD]
[TD]55 k[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]sarah1[/TD]
[TD]king1[/TD]
[TD]marketing[/TD]
[TD]97 k[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]david1[/TD]
[TD]lkim1[/TD]
[TD]it[/TD]
[TD]37 k[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]john2[/TD]
[TD]clark2[/TD]
[TD]it[/TD]
[TD]17 k[/TD]
[/TR]
</tbody>[/TABLE]

 

[lezawang]

Sorry ignore my post. In vlookup functuon, I forgot to say False/True. Thank you.

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

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

B​

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

C​

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

D​

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

E​

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

F​

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

G​

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

H​

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

I​

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

1​

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

id​

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

first​

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

last​

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

dept​

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

salary​

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

​

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

​

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

​

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

​

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

2​

[/td][td]

1​

[/td][td]john1[/td][td]clark1[/td][td]it[/td][td]43 k[/td][td][/td][td]

1​

[/td][td]43 k[/td][td]H2: =IF(LOOKUP(G2, $A$2:$A$7) = G2, LOOKUP(G2, $A$2:$E$7), NA())[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

2​

[/td][td]mary1[/td][td]jones1[/td][td]hr[/td][td]38 k[/td][td][/td][td]

3​

[/td][td]55 k[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

3​

[/td][td]alex1[/td][td]wilson1[/td][td]sales[/td][td]55 k[/td][td][/td][td]

6​

[/td][td]17 k[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

4​

[/td][td]sarah1[/td][td]king1[/td][td]marketing[/td][td]97 k[/td][td][/td][td]

7​

[/td][td]

#N/A​

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

6​

[/td][td]

5​

[/td][td]david1[/td][td]lkim1[/td][td]it[/td][td]37 k[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

6​

[/td][td]john2[/td][td]clark2[/td][td]it[/td][td]17 k[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[shg]

If the numbers in the list are always sequential from 1, just =index(e2:e7, id)