Vlookup with Excel 2013

[Silverback1992]

Dear All,

I'm using excel 2013 @ my workplace, and colleague asked me a question regarding VLOOKUP approximate match.

Now the way I learned it from Mike Girvin is that it looks through the list and finds the 1st value that is larger, jumps back 1 row and delivers wichever col_index_no you've given it.

My issue is the following:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6845[/TD]
[TD]b[/TD]
[TD][/TD]
[TD]6851[/TD]
[TD]=VLOOKUP(D2,A:B,2)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]6894[/TD]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]6851[/TD]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I'd expect that the formula entered in E2 will deliver "b". It looks through the list, the first larger number is 6894, it jumps back 1 and delivers "b".

Instead it is giving me a "c" as a result. If I change the table array to be A1:B4 it is giving me a "b".

If I take the original formula: =VLOOKUP(D3,A:B,2) and insert 1 empty row to A1, then it is giving me a "b" instead of a "c". If I enter another empty row before the table_array it changes back to giving me a "c".

I really don't know what is happening here.

Can someone please help?

Thank you very much.

Take care,

Silverback

 

[Norie]

If you omit the 4 argument VLOOKUP will find the closest match in the first column if it's sorted in ascending order.

Your first column isn't sorted in ascending order.

PS If I use =VLOOKUP(D2, A1:B4, 2) the formula returns 'b' and if I sort column A the formula returns 'c'.

 

[Silverback1992]

To add: normally I never highlight enter columns for no reason, I also sort the values ascending if doing vlookup approx. The reason for the example looking like this is that is the way my colleague had the issue in his workbook.

 

[Silverback1992]

If you omit the 4 argument VLOOKUP will find the closest match in the first column if it's sorted in ascending order.

Your first column isn't sorted in ascending order.

PS If I use =VLOOKUP(D2, A1:B4, 2) the formula returns 'b' and if I sort column A the formula returns 'c'.

Hi Norie,

Thank you. Can we say that if I don't follow the rules to do vlookup approx match. then my formula goes ape****? Or there's a deeper background for vlookup approx match that would explain why it calculates like that if I don't sort?

Take care,

Silverback