Table of Contents
What is VLOOKUP in Excel?
Excel has a plethora of functions for completing various complex tasks. We know we can use Excel for tasks like calculating addition, text wrapping, data filtering and more.
In this segment, we will delve deeper into the VLOOKUP formula. One of the most famous and commonly used functions is VLOOKUP in Excel. The V in VLOOKUP stands for Vertical.
The VLOOKUP function in Excel has both pros and cons. But the best part is it is very easy to use. In fact, it has made the lives of so many users better by automating the process of searching in an Excel file.
But it also has certain limitations. Unlike some other functions like Match, Index, or XLookup, the VLOOKUP function should have the VLOOKUP value in the first column of the entire table. This requirement makes it hard to use the VLOOKUP function with multiple criteria.
Also, the default matching behavior of the VLOOKUP function in Excel makes it easy to get incorrect results. To use the VLOOKUP efficiently and avoid these errors, one has to master the basics of VLOOKUP in Excel. And this blog is going to help you with that as we have covered the A-Z of VLOOKUP. Read on…
How to use VLOOKUP in Excel?
Two basic things to know before you use the VLOOKUP function are the Syntax and the Arguments of the function. Here are they –
Syntax: =VLOOKUP (lookup_value, table_array, column_index_number, [range_lookup])
Arguments:
- Lookup_value: This is the value you want to look up for. It should be the first column in the table.
- Table_array: This is the collection of data in which the search is going to take place. It is usually the entire table.
- Column_index_number: This is the column in the table from which you want to retrieve the data.
- Range_lookup: This is an optional value. TRUE indicates an approximate match of the value, and FALSE indicates an exact match of the value. TRUE is the default value.
Let’s now take a quick look at an example.
The Smile company is known for selling high-quality smiley balls. In the Smile company, Excel is used to manage the sales and order data. A customer whose order number was “1004” was not very happy with the order.
To cheer him up, the customer retention team plans to send him an email. But the list of customers is too large (in the below example, it is just 10 for the ease of understanding). So the team decides to use VLOOKUP to find the customer’s email. Here is the table.
- Here the Order Number is known to us. The column we need to search for is B. So we need to select all the contents in the B column (B3:B12) to find where the number 1004 is present.
- The value we require is ‘email,’ which is present in column G (column index number is 6).
- The order number must be the exact number that we type. So the range lookup should be false.
Hence the argument that the team should use to find the email is
=VLOOKUP(1004,B3:B12,6,FALSE)
You will obtain the result as gunther@smile.com.
When will you use the VLOOKUP match in Excel?
The VLOOKUP match in Excel can be used in multiple scenarios. Let’s have a look at each of them.
Exact Match
Exact Match is used when you know that the exact value (the one you are planning to use to find another corresponding value) is present in the table. This is pretty similar to what we saw in the Smile company example. But for the sake of understanding better, let’s go through another example.
Consider the employee database of the Smile company. We need to find the salary of the employee with employee code 47.
So we will use the VLOOKUP function to look for the value 47 (Lookup Value) in the first column (Column B) of the below table.
The column index number for salary is 5, and the employee number must be the exact number that we type. Hence Range Lookup should be False. The result of the VLOOKUP function will give you the exact salary of the employee with ID 47.
Also Read
How to use If Formula in Excel
How to apply Round Off Formula in Excel
Appropriate Match
In this type, we set the range lookup value to its default value, which is TRUE. When that happens, the value that is being searched would not be the exact value but rather an appropriate value.
Here is an example-
An online course is designed to allocate grades to students based on their marks. Let’s say the student Alex scored 75 (lookup value) marks and needs to be assigned a grade. The marks column of the entire table (Table array) is searched, but there is no value of 75 in the table.
By setting the Range Lookup value as ‘TRUE,’ you will be able to tell Excel to return the approximate match for 75. The VLOOKUP function in Excel will now find the largest value that is smaller than 75, which is 70 in this example. The column index number (2 in the formula) will return the value from the second column from the same row as 70.
Always Right Match
In this type, the VLOOKUP function in Excel only returns the value that’s on the right side of the selected lookup value column. All the data on the left cannot be processed or searched using it. Here is an example –
We need to find the phone number of an employee using their first name. Here are the corresponding arguments –
=(VLOOKUP(I45,C44:G49,4,FALSE))
Similarly, here are the arguments to look for the salary of the Employee using their First Name.
=(VLOOKUP(I45,C44:G49,5,FALSE))
First Match
If there are duplicates or similar values in the leftmost column of the table, then the value of the topmost item would be returned if the VLOOKUP function is used. Here is an example.
=(VLOOKUP(F56,B55:D61,3,FALSE))
In the above example, the grade of John Kepler will be shown and not John Kelso.
Wildcard Match
By using the VLOOKUP function, you can also perform a search by only knowing the partial data of the lookup value. This is possible by using the exact match in the range lookup and through the following command-
=(VLOOKUP(E69&”*”,B68:C74,2,FALSE))
The first name, “Scarlett,” is returned when the lookup value “Johan*” is used.
Also Read
How to Concatenate in Excel? A beginner’s guide
How to search in MS Excel in simple steps
Other Features of VLOOKUP in Excel
Case-Insensitive
It is also important to know that the VLOOKUP function is not case-sensitive. Here is an example to showcase this property –
The value corresponding to John will be returned, and not ‘JOHN’ as VLOOKUP treats “John, JOHN, JOhn, jOhn, etc.,” as the same.
N/A Error
Running into errors while using VLOOKUP in Excel is inevitable. The error that you will often encounter while using VLOOKUP is #N/A. It simply means “Not Found.” For example, in the below table, Larry does not exist. So the VLOOKUP function will return the value #N/A when the following command is entered –
=(VLOOKUP(I84,C84:G89,5,FALSE))
Why do we use VLOOKUP in Excel?
Primarily we use the VLOOKUP function in Excel to find the value that corresponds to another value or to find a value for which we only have a fraction of data.
But most people use VLOOKUP in Excel to enjoy the following benefits –
- VLOOKUP in MS Excel is very easy to use, especially for beginners.
- It saves a lot of time by replacing the process of manually searching the table for a value.
- Even if you only partially remember a value, you can still use VLOOKUP to find the corresponding value.
- The search in MS Excel can be made easier and more efficient by using many sub-functions like match and exact.
- You can easily know if a value is present or not, as the function would return an error if the value is not found.
- Since all the effort and search are made by the program, there won’t be any room for human errors.
- It is not case-sensitive. So you need not be too careful while searching.
- One can use VLOOKUP to merge data from two tables with ease.
- The complex nested IF function in Excel can be replaced by the VLOOKUP function.
How to do VLOOKUP in Excel with two Spreadsheets?
Let’s say we need to copy data from one spreadsheet to another using the VLOOKUP function in Excel. The syntax for this is very similar to that of the normal VLOOKUP. Only the arguments change a little.
Syntax:
=VLOOKUP(lookup_value, Sheet!range, col_index_num, [range_lookup])
Arguments
- Lookup_value: This is the value that is present in the first column of the sheet in which you are going to move the files.
- Sheet!range: This is the entire table array of the spreadsheet which you are going to copy; you need to add the sheet name in the prefix of the table array range.
- Col_index_num: This is the number of the column that has the data which you want to move.
- Range_lookup: If you want to proceed with the exact match, then select False, or if you want an approximate Match select True.
Example
Table 1 is the summary that has sales data of different colored smiley balls sold in a year, month-wise. Table 2 shows the sales details of all the smiley balls that were sold in January color-wise.
Now here is the formula to move the values from the Jan spreadsheet to Summary Spreadsheet –
=(VLOOKUP(A2,Jan!$A$11:$B$2,2,FALSE))
3. We can use a similar formula to perform the same for the rest of the months.
Also Read
How to convert text to number in Excel
A guidebook to handling Percentage in Excel
How to Compare two Columns in Excel using VLOOKUP?
Let’s say there was an exam taken by students from three different groups. You have the list of students who have taken the exam from Group A in the first column of the sheet.
The third column of the sheet shows the names of the students from all the groups who have passed the exam. Now you want to compare these two lists to find out which students from group A have passed the exams. Here is the formula to do that –
=(VLOOKUP(A2,$C$2:$C$8,1,FALSE))
Since there is a bunch of #N/A errors here, you can replace them with a blank cell. To do that, you need to use the function IFNA or IFERROR.
=IFNA(VLOOKUP(A2,$C$2:$C$8,1,FALSE),””)
You can also replace the space with any string like “Students Not Qualified.”
You can notice that there are a lot of gaps in the result column. When comparing both the columns, the value that’s not found is replaced with an empty cell. If you want to remove all those empty cells and get the list of values without gaps, then you can use the filter function or use the filter option in the formula.
=FILTER(A2:A18, IFNA(VLOOKUP(A2:A18, C2:C8, 1, FALSE), “”)<>””)
In this formula, the lookup value is group A. The Lookup function compares each value in group A with that of the list in Passed and returns an array of values. If it finds a missing value, then it returns a #N/A error. This error is replaced with empty strings using the IFNA function. Then the value is passed to the FILTER function, which removes all the blank spaces from the column.
To find the missing values in the column
If you want to find the list of students who didn’t pass the test, then you can use the above formula with an IF and ISNA function.
1. Put in the core formula for the search similar to that of the last case.
2. Next, include the ISNA function in the above formula to check the output for #N/A Errors. If there is an error, the ISNA yields True. If there is no error, it returns False.
3. Now, let’s use the IF function in the above formula. If the value returned by ISNA is True, then the IF function can return that value from the first column. If it’s False (Match Found), then it will return an empty string.
=IF(ISNA(VLOOKUP(A2,$C$2:$C$8,1,FALSE)),A2,””)
Again, you can use the FILTER function to remove the gaps in the result columns.
Conclusion
The VLOOKUP function in Microsoft Excel comes with an array of benefits. The user-friendliness of this function, in particular, has made it the most used feature of Excel to do a quick search. It has been extensively used to find, compare, and even join multiple spreadsheets and workbooks.
EasySheet is a tech blog where you can find incredible Excel hacks, tips, and tricks. Our mission is to help beginners understand Excel with EasySheet and get answers to all of their questions using EasySheet.
We are the best source of information for anyone in need of assistance with Microsoft Office products, such as MS Word, Excel, PowerPoint, Publisher, and more.
