The VLOOKUP function searches for a value in the first column of a range and returns the corresponding value from the same row and a specified column.
This function can be useful for searching and replacing data from one table to another based on matching values.
Syntax
VLOOKUP(lookup_value, range, column_num, [lookup_method])
Argument |
Description |
Permitted values |
|---|---|---|
lookup_value |
Value to search for in the first column of the range |
Any value (number, text, date, and so forth) |
range |
Range in which the search needs to be carried out. First column of the range must contain the value of the “lookup_value” argument |
Range of cells, for example, A2:D100 |
column_num |
Column number from which the value should be returned First column of the range is numbered 1. |
Integer greater than 0 or a reference to a cell containing a number |
[lookup_method] |
(optional) Boolean value that determines the type of search: approximate (TRUE) or exact (FALSE). By default, TRUE |
TRUE or FALSE |
Example of use
Suppose you have a table with information about items:
|
A |
B |
C |
|---|---|---|---|
1 |
Code |
Item |
Price |
2 |
101 |
Apples |
50 |
3 |
102 |
Pears |
60 |
4 |
103 |
Oranges |
70 |
5 |
104 |
Bananas |
40 |
If you want to find the price of apples (code 101), you can use the VLOOKUP function as follows:
=VLOOKUP(101, A2:C5, 3, FALSE)
Explanation of function argument values
–101: The value you are looking for (item code).
–A2:C5: The range in which the search is performed (includes the columns “Item code”, “Name”, “Price”).
–3: The column number from which to return the value (in this case, “Price”).
–FALSE: Indicates that an exact match needs to be found.
Result
The function will return the value 50, which corresponds to the price of apples.
Notes
–For an exact search, use FALSE or 0 in the fourth argument.
–Use the wildcards “*”, “?”, and “~” for the lookup_value argument only when the lookup_method argument is 0 or FALSE.
–The first column of the range must contain the search values.
–For an exact search, the order of the data is not important, but for an approximate search, sorting in ascending order is required.
–If the lookup_value is not found, the #N/A error is returned.