Table of Contents
- VLOOKUP function
- Using the IF Condition
- Using a Wildcard Character
- Frequently Asked Questions
In simple words, the VLOOKUP function works like an index page in a book where we look at the topic to locate its page number.
With the world producing more data each day, lookups across databases of all sizes are now commonplace. Lookup functions are how you handle this demand in Excel.
Other lookups in the same family include HLOOKUP (horizontal lookup), LOOKUP, and XLOOKUP.
This article focuses on VLOOKUP, which stands for ‘vertical lookup.’ We’ll walk you through how to use VLOOKUP and what it can and can’t do in this piece.
The VLOOKUP function searches for a defined value across columns.
The function takes a lookup value, table reference, and column index as mandatory parameters. The last parameter is optional, which specifies the return value. It could either return an exact match or an appropriate match.
Let’s look at the syntax for VLOOKUP and understand it with an example.
=VLOOKUP ( lookup_value,table_array,col_index_num,[range_lookup])
|lookup_value||This is the value we are searching for in our VLOOKUP.|
|table_array||This argument takes the cells’ start and end values the function has to search. The array that it looks in.|
|col_index_num||The value in this argument represents the column’s index. This is means how many columns across do we want our VLOOKUP to look.|
|range _lookup||The final argument accepts two values, one is TRUE, and the other is FALSE. TRUE returns the closest match. FALSE returns an exact match.|
In the example above, we have an employee table containing First_Name, Last_Name, Age, Designation, and Salary.
The table covers cells A2 to F6. This is what we refer to as table_range. We are using VLOOKUP here to return the employee’s salary based on the First_Name of the employee.
Imagine the same situation in a real-world environment where you need to pull this information, but your table has 5,000 rows instead of 5. You can quickly see just how helpful VLOOKUP could be in this instance.
Now, let’s take a look at the formula used to find the salary of an employee whose First_Name is Paul, VLOOKUP(B9, B2:F6, 5, FALSE).
Cell B9 contains the lookup_value ‘Paul’. We search for the lookup_value ‘Paul’ in the table spread across the range, B2 to F6. We set the column number (column_index_num) as 5 because it is in the 5th column from where the lookup_value is referenced.
The last parameter is optional. We choose FALSE as we would like it to return an exact match based. Were we to use TRUE instead, Excel would return the closest match it could find if it couldn’t find an exact match.
The formula works like this:
VLOOKUP(B9,..,..,..) – takes the value in B9, which is Paul.
VLOOKUP(B9, B2:F6,..,..) – searches for ‘Paul’ in the cells from B2 to F6. It finds it at column B in the 5th row.
VLOOKUP(B9, B2:F6, 5,..) – From column B in the 5th row, it moves 5 columns right because 5 is our column index.
VLOOKUP(B9, B2:F6, 5, FALSE) – Excel pulls the result from the 5th row of the 5th column (the salary column). It returns the value of 30,000, which is Paul’s salary.
Using the IF condition
You can combine the VLOOKUP function with an IF function to return an output message like ‘FOUND,’ ‘NOT FOUND,’ ‘AVAILABLE,’ or ‘NOT AVAILABLE.’
Let’s take an example to understand combining an IF condition with a VLOOKUP function.
=IF(VLOOKUP(9, A2:F6, 2, FALSE)=“Jessica”,“Found”,”Not Found”)
Here we have nested our VLOOKUP inside an IF function. The IF function performs a logical test. This means that it tests for something based on criteria you set and returns one output if TRUE and another output if FALSE.
In this example, our logical test is whether the result of our VLOOKUP matches the output “Jessica.” Note, if you are looking to search for or return text values in Excel, you must put the text inside quote marks (“TEXT GOES HERE”).
If our logical test finds Jessica in our VLOOKUP, it returns “Found”. If it doesn’t, it will produce “Not Found.”
You can use the IF condition to handle errors in Excel or simply tidy up the outputs of functions. For example, if you perform a long list of lookups using VLOOKUP and many of the results are #N/A, you can use IF to display a more desirable output. Likewise, IFERROR is an excellent function to use in this instance!
Using a Wildcard Character
Excel has a way of handling exceptions. These are called wildcards. You can use wildcards in a VLOOKUP. For example, ‘*’ is a wildcard that you could add to the lookup_value. Let’s take a look at an example.
We have a list of employees and the details of them in the table. The first name and the last name have been put together in the Name column and if we search for details about an employee using their full name, then we will get the result.
Look at the example below, if we give just a part of the name, the VLOOKUP function cannot find it in the table_array and returns a #N/A error.
This is where a wildcard character comes to the rescue. The wildcard character “*” when used in the VLOOKUP function looks for a value that starts with the name ‘Jessica’ that you have mentioned in cell B8.
Combining the lookup value with our wildcard asterisk gives you the result without any error.
Let’s understand how it works. The lookup_value is ‘Jessica’, which is B8.
When you give the formula as =VLOOKUP(B8, B2:E6,3, FALSE), the VLOOKUP formula looks at cell B8 and reads the name ‘Jessica’.
It searches in the range B2:E6 and doesn’t find a match because the table has the name ‘Jessica Williams’ and the formula returns as #N/A.
Now, suppose we include the wildcard character next to the cell reference as in the formula =VLOOKUP(B8&”*”, B2:E6, 3, FALSE). In that case, we get the result that we are looking for because Excel is looking for results similar, but not an exact match, to ‘Jessica.’
Our VLOOKUP function matches the lookup_value ‘Jessica’ with ‘Jessica Williams’ and takes the next parameter ‘3’, the 3rd column from our lookup_value.
It returns the designation of Jessica as Secretary.
Nested VLOOKUP Inside IFNA function
Nesting a VLOOKUP function inside an IFNA function gives us the flexibility to perform sequential lookups. For example, if the first VLOOKUP function returns an error, a #N/A, the second VLOOKUP function catches it.
Similarly, by nesting multiple VLOOKUPS inside the IFERROR function, the formula allows for sequential lookups.
You can set up your formula so that if the first VLOOKUP produces an error message, you can run a second VLOOKUP. In theory, you can keep nesting VLOOKUPs within your formula to account for multiple errors.
If this sounds confusing, don’t worry, it can be a bit strange at first. Let’s consider the example below to bring this to life.
In this example, we look for an employee whose Emp_ID is 12. By looking at the table, you can see that this ID doesn’t exist. In this instance, we can use ISNA to return a different value.
The formula to return a message that “Employee record not found” is,
=IF(ISNA(VLOOKUP(12,A2:F6,2,FALSE))=TRUE,”Employee record not found”,VLOOKUP(12,A2:F6,2,FALSE)).
Frequently Asked Questions
1. How do you do a VLOOKUP across two spreadsheets?
There are multiple ways to execute a VLOOKUP.
- Looking up data on the same worksheet and in the same workbook.
- Looking up data across different worksheets in the same workbook.
- Looking up data across different workbooks.
When VLOOKUP is used across different worksheets in the same workbook, the syntax is:
VLOOKUP(lookup_value, worksheet_name!cell_range, col_index, ramge_lookup])
When VLOOKUP is used across different workbooks, the syntax is:
VLOOKUP(lookup_value, [book1.xlsx]worksheet_name!cell_range, col_index, range_lookup])
2. What are the limitations of using a VLOOKUP?
The main limitations of the VLOOKUP function are:
- VLOOKUP can only look up values to the right on the lookup_value. i.e. the lookup only works left to right. You’ll need XLOOKUP, or INDEX MATCH for a right to left lookup.
- VLOOKUP can only perform a vertical lookup across columns. It cannot search across rows. For a horizontal lookup, you’ll need HLOOKUP or XLOOKUP.
- A VLOOKUP function does not automatically update when a formula is added.
- By default, the VLOOKUP function does not find an exact match. It returns a #N/A. To return an approximate match using VLOOKUP, you must set the final parameter as TRUE or 1.
In this article, we learnt how the VLOOKUP function works with the help of a few examples. If you use VLOOKUP to search large databases, we recommend putting your data into a table and ensuring that your lookup column is furthest to the left.
For example, in the table above, the first column in a table is employee ID, and the remaining columns are their names, designation, and details about them. If the query is to get the employee’s joining date, given the employee ID, VLOOKUP works well as the column joining date is to the right of employee ID.
When the table is dynamic and more columns get inserted, VLOOKUP doesn’t change the formula automatically. In such a situation, you can use the INDEX MATCH function. To learn more about the other lookup functions available, go here!