VLOOKUP Function
The VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers.
In simple words, a user may use the VLOOKUP formula to search specific information (like employee ID) in an Excel database (table in Excel worksheet) and find information associated (employee’s salary) with it.
The “V” in VLOOKUP stands for vertical. The function looks for a search value in the first column (lookup column) of the specified range and returns a match from the same row of another column (return column).
The VLOOKUP excel function works for numerical, textual, and logical values. For example, an organization uses VLOOKUP to retrieve monthly revenue of multiple products based on the product code (unique identifier).
You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VLOOKUP Function in Excel (wallstreetmojo.com)
The Syntax of the VLOOKUP Function
The syntax of the function is stated as follows:
The function accepts four arguments–“lookup_value,” “table_array,” “col_index_num,” and “range_lookup.” The first three are mandatory arguments while the last one is optional.
- Lookup_value: Required, represents the value that we want to look up for in the first column of a table or dataset.
- Table_array: Required, represents the dataset or data array that is to be searched.
- Col_indexnum: Required, represents the integer specifying the column number of the table_array that we want to return a value from
- Range_lookup: Optional, represents or defines what the function should return in case it does not find an exact match to the lookup_value. This argument can be set to ‘FALSE; or ‘TRUE,’ where ‘TRUE’ indicating an approximate match (i.e., use the closest match below the lookup_value in case the exact match is not found), and ‘FALSE’ indicating an exact match (i.e., it returns an error in case the exact match is not found). ‘TRUE’ can also be substituted for ‘1’ and ‘FALSE’ for ‘0’.
How to Use VLOOKUP in Excel?
Let us go through a few examples of VLOOKUP in excel. They will be beneficial for both the beginners and the advanced Excel users.
Example #1
The following table shows the prices of various types of dresses. We want to find the price of the trouser, shirt, and dress.
Time needed: 2 minutes.
The steps to use the VLOOKUP excel function are listed as follows:
- Organize the data in the left to right format because the function works in this order. Such an arrangement (shown in the succeeding image) makes it easy to use VLOOKUP.
- Place the cursor where the formula is to be entered. Since the price of the trouser is to be found, we place the cursor in cell F2, as shown in the succeeding image.
- Enter the formula “=VLOOKUP(E2,B1:C9,2,FALSE).” To find the exact value, it is essential to supply correct arguments to the function.
In the following pointers (step 3a to step 3d), all the arguments of the VLOOKUP function with respect to the current example are explained one by one.
Step 3(a): Lookup_value–This is the first and the main argument of the function. It specifies the value to be searched in the table. So, the “lookup_value” is E2 (trouser).
Step 3(b): Table_array–This refers to the table range in which the lookup value is to be searched. So, the lookup value is to be searched in the range B1:C9 of the lookup table.
Step 3(c): Col_index_num–This is the index number of the column. It specifies the column from which the value is to be returned. The word “trouser” is present in column B (or column 2).
So, this argument is 2, referring to the second column of the table array.
Note: The leftmost column of the table array is counted as 1.
Step 3(d): Range_lookup–This is the Boolean value “true” or “false.” The values are explained as follows:
• True–It is used for an approximate or a close match. The data is matched with the argument approximately. For instance, if the data contains more than one word, the first word is looked up.
• False– It is used for an exact match. All the words in the data are matched and a corresponding value is returned.
Hence, we enter “false” because we want the formula to return an exact match. The complete formula is shown in the succeeding image.
Note: If the “range_lookup” argument is omitted, the default value is “true.” - Press the “Enter” key. All the arguments have been passed correctly. The result is displayed in the following image.
- Drag the formula to obtain the prices of the shirt and dress. The output is shown in the following image.
Example #2
The following table shows the marks of 6 students in different subjects. The serial ID (Sid) number is given in the first column (A).
We want to find the marks corresponding to serial ID numbers 2 and 6.
The steps to apply the VLOOKUP function are listed as follows:
Step 1: Organize the data and place the cursor in cell G2 where the formula is to be entered.
Step 2: Enter the VLOOKUP excel formula in cell G2. For auto-populating the formula, press the “tab” key.
Step 3: Enter the first argument of the function as shown in the succeeding image. The “lookup_value” is cell F2. This is the value we are looking for.
Step 4: Enter the next argument which is the “table_array.” Select A1:D7 as shown in the following image. This is the source table where the serial ID is to be found.
Step 5: Enter the “col_index_num” which is the serial number of the column from which value is to be returned. Since we want the function to return a value from the “marks” column, we type 4.
The serial ID number column (A) is counted as 1.
Step 6: Enter the argument “range_lookup” which consists of two values “true” or “false.” Since the latter value returns an exact match, we type “false.” Close the brackets of the formula.
In most cases, the exact match option (false) is used to avoid confusion. The complete formula is shown in the following image.
Step 7: Press the “Enter” key as the formula is ready to be applied. The output is shown in the following image. Drag the formula of cell G2 to obtain the result in cell G3.
Example #3
The succeeding table shows the names of 6 people along with their gender. The serial numbers are given in column A (Sno).
We want to find the serial number of a given gender.
The formula “=VLOOKUP(E2,A1:C7,1,FALSE)” returns the output “#N/A” error.
We want to find the serial number for the given gender. The “lookup_value” is E2 meaning the value to be looked up (gender) is to the right side of the serial number column (A).
The VLOOKUP function does not work because it looks for values beginning from left to right. In simple words, the lookup column (gender) should always be to the left of the return column (serial number or Sno).
The function searches the leftmost column of the “table_array” and returns a corresponding value from the right-hand side column.
The “#N/A” error is the “value not available” error as shown in the succeeding image.
Note 1: The limitation of the VLOOKUP function in excel is that it works on columns from left to right.
Note 2: To use the VLOOKUP to extract values from the leftVLOOKUP To Extract Values From The LeftVlookup to the left finds the respective values which are in the left column of the reference cell. Index, and match are such formulas which are combined together or we can use conditional formulas in the lookup function to find values to the left.read more, it is used in combination with the IF and CHOOSE functions.
The Characteristics of VLOOKUP Function
- It is not case-sensitive which implies that the uppercase and lowercase alphabets are treated the same.
- It should be used for a given data table when there is an absence of duplicate values. If duplicate matches are found, the function returns only the first one.
- It is often used in financial calculations.
The VLOOKUP Errors in Excel
The VLOOKUP formula returns errors on account of various reasons. There are three types of VLOOKUP errors which are explained as follows:
“#N/A!” error –This occurs if the function is unable to find the “lookup_value” in the given “table_array.”
“#REF!” error – This occurs if the supplied “col_index_ num” argument is greater than the number of columns in the given “table_array.”
“#VALUE!” error – This occurs if the “col_index_ num” argument is not given or is supplied as less than 1.
Frequently Asked Questions
The function can be used to search for text beginning or ending with particular alphabets. For instance, we want to find the name of a person that begins with “eli.” The formula is stated as follows:
“=VLOOKUP(“eli*”,table_array,col_index_num,range_lookup)
Hence, the formula will search the “table_array” for names beginning with “eli.”
In most cases, the “table_array” is locked with the dollar sign ($) like $A$2:$B$10. This prevents the array from changing while copying the formula to different cells.
Usually, the “lookup_value” is supplied as a relative reference like C2. This adjusts the reference as the formula is copied to different cells. It is also possible to lock the column coordinate like $C2.
In the given pointers, all example headings are followed by their respective formulas.
• Example 1–To extract data from a different worksheet “=VLOOKUP(lookup_value,sheetname!table_array,col_index_num,range_lookup)”
• Example 2–To extract data from a different workbook
“=VLOOKUP(lookup_value,[workbookname.xlsx]sheetname!table_array,col_index_num,range_lookup)”
Note: If the workbook or the worksheet name contains spaces or non-alphabetical letters, enclose it within single quotes.
• Example 3–To extract data from a named range “=VLOOKUP(lookup_value,namedrange,col_index_num,range_lookup)”
Note: If the name range pertains to a different workbook, enter the latter’s name before the former’s name.
Recommended Articles
This has been a tutorial to VLOOKUP Excel Function. Here we discuss how to use VLOOKUP formula along with step by step examples. You may learn more about Excel from the following articles –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
Leave a Reply