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