vlookup with approximate

[lezawang]

Hi
I use vlookup to find the grade of each student (last argument is TRUE). My question why the grade table "has to" be sorted from smallest to the largest otherwise vlookup wont work. It wont work if the table was like this


[TABLE="class: grid, width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]90[/TD]
[TD="width: 64"]a[/TD]
[/TR]
[TR]
[TD="align: right"]70[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD="align: right"]60[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]f[/TD]
[/TR]
</tbody>[/TABLE]

Below, it will work because the grade table sorted from smallest to the largest

[TABLE="class: grid, width: 576"]
<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]name[/TD]
[TD="width: 64"]test[/TD]
[TD="width: 64"]test[/TD]
[TD="width: 64"]test[/TD]
[TD="width: 64"]average[/TD]
[TD="width: 64"]grade[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]john1[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]28[/TD]
[TD]f[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]alex1[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]73[/TD]
[TD]b[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]mary1[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]37[/TD]
[TD]f[/TD]
[TD][/TD]
[TD="align: right"]50[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]david1[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]93[/TD]
[TD]a[/TD]
[TD][/TD]
[TD="align: right"]60[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]sarah1[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]67[/TD]
[TD]c[/TD]
[TD][/TD]
[TD="align: right"]70[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]john2[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]73[/TD]
[TD]b[/TD]
[TD][/TD]
[TD="align: right"]90[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]alex2[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]31[/TD]
[TD]f[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

A lot of these questions on functions and how they work can be found with a simple Google search of the function.
See here: https://www.extendoffice.com/documents/excel/2443-excel-vlookup-exact-approximate-match.html#a2

Note the second and third items:

[FONT=&quot]2. The approximate match returns the next largest value that is less than your specific lookup value.[/FONT]
[FONT=&quot]3. To use the vlookup function to get an approximate match value, your first column in the table must be sorted in ascending order, otherwise it will return a wrong result.[/FONT]

I believe it needs to be that way because it looks for the first value that is larger than the one you are looking up, then goes to the previous record (the row just above it).

 

[Scott T]

Vlookup approximate match splits the data and looks at the middle value. It uses this to pick which half of the data to look in to find the lookup value.

for example

Book1
	B	C	D	E
25	700		100	A
26			300	B
27			200	C
28			900	D
29			99	E
30			300	F
31			700	G
32
33			C
Sheet1

Cell Formulas
Range	Formula
D33	=VLOOKUP(B25,D25:E31,2)

Vlookup will compare 700 to 900 and conclude that the lookup values must be in the top half of values since 700 is less then 900. If you were looking up 1,000 it would look at the bottom half since it is greater then 900.

Exact match vlookup does not need to be sorted because it works differently.