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

90	a
70	b
60	c
50	d
0	f

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

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

name	test	test	test	average	grade
john1	18	30	35	28	f
alex1	65	78	76	73	b		0	f
mary1	18	79	15	37	f		50	d
david1	90	90	99	93	a		60	c
sarah1	61	94	45	67	c		70	b
john2	20	100	100	73	b		90	a
alex2	30	26	37	31	f

<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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.