Syntax:
VLOOKUP(Crit, Range, n [, Sorted])
Description:
Scans the first column of Range for the first given value of Crit. If found, the function returns the content of the cell located in the same row and n-th column of Range.
Note: VLOOKUP can search vertically only. To search horizontally, use HLOOKUP instead
Crit is the value to be searched for (case insensitive).
Range is the cell range or array to be evaluated. Its first column should contain the values to be scanned. These can be text strings, numbers or logical values.
n is the relative column number in Range from which a value will be returned, if Crit is found.
If n is less than 1, a #VALUE! error value is returned. If n is greater than the total number of columns in Range, a #REF! error value is returned.
Sorted is a logical value determining whether the values in the first column of Range are in sorted order:
TRUE or omitted: Values have to be sorted in ascending order.
FALSE: Values do not have to be sorted.
Important: If you do not explicitly set Sorted to FALSE, these values must be in sorted order!
Note:
If Sorted is set to TRUE and VLOOKUP does not find a value that exactly matches the Crit, the next lower value is automatically taken. If no smaller value exists because Crit is smaller than the smallest value available, the function returns the error value #N/A.
Example:
You sell flowers at different unit prices – depending on the amount of flowers the customer buys. To do this, you have created a table with a discount scale:
Now you can use the VLOOKUP function to determine the price for a specific type of flower depending on the number of pieces.
Use the following arguments:
For Crit, enter the name of the flower type (i.e. "roses", "carnations" or "tulips").
For Range you enter the cell range, which contains the names of the flower types in the first row and the prices below – here A2:D4.
For n you enter the number of pieces plus one, because the first column (n=1) does not contain prices, but the names of the flowers.
Some examples:
VLOOKUP("Roses", A2:D4, 2, FALSE) returns the unit price when purchasing one rose, i.e. 5.50 Euro.
VLOOKUP("Roses", A2:D4, 3, FALSE) returns the unit price when purchasing two roses, i.e. 5.10 Euro.
VLOOKUP("Roses", A2:D4, 4, FALSE) returns the unit price when three roses are purchased, i.e. 4.95 Euro.
VLOOKUP("Carnations", A2:D4, 4, FALSE) returns the unit price when three carnations are purchased, i.e. 4.50 Euro.
VLOOKUP("Carnations", A2:D4, 5, FALSE) returns the error value #REF because n is greater than the row number of Range.
VLOOKUP("Petunias", A2:D4, 4, FALSE) returns the error value #N/A, because "Petunias" does not appear in the first row of Range .
See also: