Syntax:
VLOOKUP(Crit, Range, n [, Sorted])
Description:
This function searches in the first column of Range for the first occurrence 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 only search vertically. To search horizontally, use HLOOKUP instead.
Crit is the value for which the search is performed (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 that you use to specify whether the values in the first column of Range are sorted:
If Sorted is TRUE or omitted, values in the first column of Range have to be sorted in ascending order.
If Sorted is FALSE, the order of the values in the first column is irrelevant.
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 Crit, the next smaller value is automatically taken.
If no smaller value exists because Crit is smaller than the smallest value available, the function returns the #N/A error value.
Example:
You sell flowers at different unit prices – depending on the amount of flowers the customer buys. To do so, you have created a table with a discount scale:
You can now 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, you enter the name of the flower type (i.e., "roses", "carnations" or "tulips").
For Range, you enter the cell range that contains the names of the flower types in the first column and the prices to the right of them – A2:D4 here.
For n, you enter the number of pieces plus one, as the first column (n=1) contains the names of the flowers rather than the prices.
Examples:
VLOOKUP("Roses", A2:D4, 2, FALSE) returns the unit price when purchasing one rose, i.e., €5.50.
VLOOKUP("Roses", A2:D4, 3, FALSE) returns the unit price when purchasing two roses, i.e., €5.10.
VLOOKUP("Roses", A2:D4, 4, FALSE) returns the unit price when purchasing three roses, i.e., €4.95.
VLOOKUP("Carnations", A2:D4, 4, FALSE) returns the unit price when purchasing three carnations, i.e., €4.50.
VLOOKUP("Carnations", A2:D4, 5, FALSE) returns the #REF error value because n is greater than the column number of Range.
VLOOKUP("Petunias", A2:D4, 4, FALSE) returns the #N/A error value because "Petunias" does not appear in the first column of Range.
See also: