Excel is widely used for data handling and almost everyone nowadays uses Excel heavily. At the same time, it is very user-friendly. Excel has several functions and formulas to make tedious jobs very easy. Like the SUMIF() function, VLOOKUP() is another very useful function that is widely used to ‘look up’ any data from a long table or a range by row. It makes the work a lot easier and saves time.
In the below article, we will learn more about the VLOOKUP Function and how to use it.
What is a VLOOKUP Function?
It is a widely used function in Excel and is used for looking up any required data from a list of large data sets. It supports approximate or exact matches based on one of its arguments being ‘True’ or ‘False’. This function also supports wildcards( ‘*’ and ‘?’) for partial matching.
VLOOKUP() function is the premade function that allows searches across columns. Generally, the VLOOKUP() function is used to find data from a table, a list, or a range. It has two features – Approximate match and Exact match.
Note: User must set the LOOKUP -value in the first column of the selected table as VLOOKUP() always searches in the right of the value and returns the desired output.
What is VLOOKUP Formula
The Formula for Vlookup function is:
VLOOKUP(lookup_value, Table_array, col_index_num,[range lookup])
Arguments of Excel’s VLOOKUP Formula:
Arguments used are defined as:
- lookup_value : This is the lookup value that should be provided by the user and the data you want to look up. This is a mandatory argument.
- table_array : This is the table where the lookup_value and our desired output both exist. VLOOKUP() function uses this table_array argument to find the result. This is a mandatory argument.
- col_index_num : This is the column number of the table_array where VLOOKUP() will search for its output. If it gets the result, it returns the specific value.This is also a mandatory argument.
-
[range_lookup] : The user may or may not provide this according to his or her needs. This argument’s value may be True or False. If
- ‘True’, it means an approximate match
- ‘False’, it searches for an exact match.
Note: If nothing is specified by the user, by default this value is set to be ‘True’ and it searches for an approximate match. This argument is completely optional.
Return Value of Excel’s VLOOKUP Formula
This function searches the corresponding value of the given argument and returns the desired matched value from the table or range provided as an argument.
Things to Remember: The column which holds the data used to lookup must always be to the left.
How to Use the VLOOKUP Function in Excel
The VLOOKUP Function is very easy to use in Excel by following the below mentioned steps:
Step 1: Select a cell
Step 2: Apply Vlookup
Type ‘=VLOOKUP‘ without quotes
Step 3: Double-click the VLOOKUP command
Use the VLOOKUP Function to find the Student names based on their Roll no.
Step 4: Select the cell and Put a comma
Select the cell where the Search value will be entered. G4 is the cell where the result will be displayed. In this case, the student names based on their roll nos. and G3 is selected as the LOOKUP value. Enter a comma.
Step 5: Mark table Range and Put Comma
Now select the table range for the function. This means mark from where to where. The range of the table is marked at table_array (in this the table is selected A1:D9). Put a comma again(,).
Step 6: Type the number of columns, counted from the left
The number 2 is entered as col_index_number. This is the Second column from the left and is the data that is being looked up.
Step 7: Type 1 or 0 and Press Enter
Type True or 1 for Approximate Match and False or 0 for Exact match.
Step 11: Preview Result
Now, you can preview the result. The function returns the #N/A value. This is because there have not been entered any value to the Roll_no.
If we give roll no value of 1015 then the result will be:
The VLOOKUP function has successfully found the Student name which has roll number 1015.
Excel’s VLOOKUP Function Examples
Let us understand the above steps better with the help of examples. There are a few examples of VLOOKUP discussed below.
1. How to Find VLOOKUP with Error in Excel
We have a dataset of students with their Roll Number, Student Name, Age, and Marks obtained. We will now see how to use the VLOOKUP function.
We will use the VLOOKUP function to find the Student’s name on the basis of their Roll number.
1. Select a cell (G3)
2. Type ” =VLOOKUP ” and Double click the VLOOKUP command.
3. Select the cell where search value will be entered (G4) and Type (,)
4. Mark table range (A1:D10) and Type (,)
5. Type the number of the column, counted from the left (2)
6. Type True (1) or False (0) and Press Enter
Formula used : =VLOOKUP(G3,A1:D10,2,1)
This formula is showing an Error because the LOOKUP value G3 does not contain any value.
Now we will look up for value after entering a value in cell G3.
Now the result for the LOOKUP Value is Vineeta Tiwari.
2. How To Find an Exact Match Using VLOOKUP in Excel
Here, we will see an example of the exact match of the VLOOKUP() function.
For finding an exact match in the VLOOKUP Formula, in the place of last argument ” false ” will be typed.
Here in the above image, we have used the VLOOKUP formula to search for the student name with the roll number 1014, and we have found the result Dinesh Shulka.
3. How To Find Approximate Match Using VLOOKUP in Excel
In this case, when an exact match is not found VLOOKUP() function will return the value of the next greater element. The following is an example.
The only difference in finding approximate match with that of an Exact match is instead of False in the argument, ” True” is written.
Here, the 1018 roll number does not exist. So it returns the student name of roll number 1017(i.e. the next greater element in the list).
4. VLOOKUP Example of First Match, Case Insensitive & N/A error in Excel
This function always returns the first matched element. It is at the same time case-insensitive. And if we are looking for an element’s exact match while the element is absent in the table this function will show #N/A. All the three cases are shown in the below figure.
Note: This function always searches rightwards to the lookup_value.
How to Use VLOOKUP for Multiple Criteria?
In order to use VLOOKUP Function for Multiple Criteria, follow these steps:
Step 1: Right- click on the column header and click “Insert”. This will insert a column left of the Students name.
Step 2: Name this column as “Student Name and Age” and enter the formula =C2&”-”&D2 and drag down to the rest of the column.
The concatenated column has been successfully obtained.
Step 3: Now, we enter the formula “=VLOOKUP(B15&”-”C15,B1:E9,4,FALSE)” and look for the marks obtained by Amod Yadav-21.
Result: We will get the following result.
How to Use VLOOKUP Across Multiple Sheets
To use VLOOKUP across multiple sheets in Excel simply follow these steps:
- First, open both the worksheet that has the data you need and the one where you’ll display the search results. Look for a unique identifier that both worksheets share, like Student’s Name or Roll Number, to serve as your search key.
-
Next, in the worksheet intended for the results, type the VLOOKUP formula into the cell where you aim to bring in the data. Within this formula, you need to define
- the search key
- the cell range in the original worksheet where this key can be found,
- the column number from which you wish to pull the data.
- Also, you must indicate the full range of cells containing the data in various sheets, ensuring these elements are properly separated by commas.
- Hit Enter after typing your formula to view the search result in the specified cell. This simplified explanation helps you utilize the VLOOKUP function across multiple worksheets effectively, by establishing a clear step-by-step process without diving into overly technical jargon.
Common Errors in VLOOKUP Function
- #N/A! error – This means that the VLOOKUP function couldn’t find what you were looking for.
- #REF! error – This happens when the formula refers to cells that aren’t there, or when it tries to use a column number that’s too big for the table.
- #VALUE! error – This occurs if the column number isn’t a number or if the “range_lookup” value isn’t either “TRUE” or “FALSE”.
What are the Limitations of VLOOKUP in Excel
Some of the Limitations of VLOOKUP in Excel are:
- VLOOKUP may give wrong answers if the search term’s case doesn’t match the data.
- It only shows the first match and requires data to be sorted from smallest to largest.
- Mistakes or empty cells in the data can also lead to incorrect results.
Also Read:
Conclusion
In the above article we learned what is a VLOOKUP Function in Excel and why it is needed in Excel. We also discussed on its formula, Syntax and how to use Vlookup Function along with its different cases. Using a VLOOKUP Function is quiet easy but makes the other tedious jobs easier.
VLOOKUP Function – FAQs
What is the VLOOKUP function in Excel?
The VLOOKUP function stands for Vertical Lookup. VLOOKUP function is a Powerful feature in excel which allows you to search for specific value in the leftmost column of a table and gives a corresponding value from a specific column.
Can VLOOKUP search for values horizontally?
No, VLOOKUP is designed to search for values vertically in a column. To search for values horizontally, you can use the HLOOKUP function.
What is the range limit for VLOOKUP?
The range limit for VLOOKUP in Excel is approximately 1,048,576 rows, which is the maximum number of rows allowed in an Excel worksheet.
What is the two-way LOOKUP function in Excel?
When you want to make changes in both LOOKUP value and Column index number then, you have to make both the values Dynamic.
And use MACTH formula with LOOKUP function to Extract the exact values.
Syntax : =VLOOKUP (lookup_value,Match(lookup_value, lookup_array, [Match_type]),[range_lookup)
What is the double LOOKUP formula in Excel?
This can be also known as Nested VLOOKUP function .
Syntax : VLOOKUP(VLOOKUP(lookup_value, table_array, Column_index_num,[range]),table_array, column_index, [range])