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:

	A	B	C	D	E
1	0	a
2	6845	b		6851	=VLOOKUP(D2,A:B,2)
3	6894	d
4	6851	c

<tbody>
</tbody>

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