VLOOKUP Function & Formula in Excel
In our daily work, we often encounter the situation of finding data based on the original data and conditions provided, such as finding cell phone numbers based on name, finding employee names and positions based on HRID, etc.
When we talk about the lookup function in excel workbook, most people will first think of using the VLOOKUP function. VLOOKUP function is the most commonly used lookup function in excel, even if you don’t use VLOOKUP function, you must have heard of it. How do we apply the VLOOKUP function in excel? What can it help us to achieve? Can we do lookups across worksheets? Today we will discuss the applications and usages of VLOOKUP function in Microsoft Excel Spreadsheet.
Table of Contents
VLOOKUP Function Introduction
VLOOKUP is a vertical lookup function that defines the direction of the lookup in the vertical direction. It can only lookup data by rows of the workbook, not by columns. If you need to find data by column, you need to use the HLOOKUP function, which is a horizontal lookup function with the similar arguments and usage as the VLOOKUP function.
VLOOKUP function is widely used in our daily life, it can be used to check the data, quickly import data between multiple worksheets and so on.
VLOOKUP Function Syntax and Arguments Introduction
The syntax of VLOOKUP function
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Argument | Description | Data Type |
lookup_value | Value to find | A numeric value, reference or text string |
table_array | Lookup area | Data table |
index_num | Returns the number of columns in the lookup area | Integer (positive) |
range_lookup | Exact match / Approximate match | False (0, exact match) True (1 or empty, approximate match) |
- lookup_value is the value to be looked up, which appears in the first column of the data table. lookup_value can be a numeric value, a cell or area reference, a text string. When this parameter is omitted, it means that look up is performed with 0 value.
- table_array is the data table to look for. Enter a cell or range reference or table name.
- col_index_num is the data column number of the table_array. col_index_num is 1, the value of the first column of the table_array is returned, col_index_num is 2, the value of the second column of the table_array is returned. If col_index_num is less than 1, VLOOKUP returns the error value #VALUE!, if col_index_num is greater than the number of columns of table_array, VLOOKUP returns the error value #REF!.
- range_lookup is a logical value that specifies whether the VLOOKUP function will look for an exact match or an approximate match. If range_lookup is TRUE or 1, VLOOKUP will look for an approximate match, and if it does not find an exact match, it will return the maximum value less than lookup_value. In the following sections we will explain exact match and approximate match with concrete examples.
Steps of Using VLOOKUP Function in Excel Worksheet
A quick way of using VLOOKUP
Step 1: Just type VLOOKUP function in a blank cell where you want load your result. Excel will prompt you to enter arguments in appropriate places.
A traditional way of using VLOOKUP
Step 1: In the worksheet ribbon, click Formulas tab-> Lookup & Reference.
Step 2: In the dropdown list, select VLOOKUP function.
Step 3: VLOOKUP formular builder appears on the right side of your worksheet.
Step4: Enter the details of arguments. You can type lookup value or table_array manually, or you can also drag and drop cells to select ranges.
Step 5: Click Done or press the Enter key to get the result.
VLOOKUP Formula in formula bar:
Entered Arguments in Formula Builder:
VLOOKUP Exact Match
When range_lookup is false, the VLOOKUP formula performs an exact match lookup. It means that only if the lookup value is found in the first column of the data table, the value of the corresponding column is returned, and if the lookup value does not appear in the first column, the error value #N/A is returned.
Example:
Enter “=VLOOKUP” in E2, enter details of arguments according to the lookup value and the table. In this example, we want to find the correct level for score 50, so 50 is the lookup value. The data table is A2:B11 which records the correspondence between grades and levels. Level is recorded in the second column of data table, so column index number is 2. At last, we want to get an exact match result, so enter “0” or “false” in the last argument.
Argument | Value |
lookup_value | D2 |
table_array | A2:B11 |
index_num | 2 |
range_lookup | 0 |
In this example, lookup value appears in the first column of data table. If it doesn’t appear, an error value #N/A will be returned.
VLOOKUP Approximate Match
When range_lookup is true, the VLOOKUP function performs an approximate match lookup. Meaning that if the lookup value is not found in the first column of the data table, it returns the value of the corresponding column of the maximum value of all values less than the lookup value. Let’s look at the following example for a concrete analysis.
At the end of the previous paragraph “VLOOKUP Exact Match”, we got an ERROR because score “55” does not appear in the first column of the data table. Now let’s change the range_lookup value to 1 (approximate match) and see the difference in the result.
This time level “F” is returned in level value. See the example below:
Referring to the rules of the VLOOKUP function, if no lookup value is found, the function will find the value closest to the lookup value. And the closest value should be a value less than the lookup value, and it is also the largest of all values less than the lookup value. In this example, the largest value less than 55 is 50, so VLOOKUP returns the corresponding level to 50.
Argument | Value |
lookup_value | D2 |
table_array | A2:B11 |
index_num | 2 |
range_lookup | 1 |
You can also assume that the passing line for an F level is 50 and for an E level is 60, and if you fail to reach 60, you will only be F.
VLOOKUP with Absolute Range Reference
We know that in the Excel workbook, enter a formula in a cell, if the formula is dragged to other cells, cell references or range references used in the formula arguments will be automatically updated, so that we can directly drag and drop the formula into other cells to copy the formula without having to manually update the arguments.
See example below:
In this case, we have entered a VLOOKUP formula in H2, and we can drag the formula directly to H3 to reuse the formula, while the lookup value and lookup range get updated due to moving the formula. The VLOOKUP formula is dragged down from H2 to H3, the lookup value is updated from G2 to G3 and lookup range is updated from A2:B7 to A3:B8 accordingly.
In this example, along with the movement of the formula, the lookup value HRID=5 still appears in the first column even though the lookup range has been moved down one row, and we can get the correct employee name by this formula. However, in some cases, this considerate automatic update can cause errors in real cases, see the following example.
In this case, HRID=1 does not appear in the first column of data table A3:B8, so the lookup value does not exist and the formula returns error #N/A. In fact, in most VLOOKUP formulas, if the lookup range is fixed, we should lock the range by adding $ in front of the index numbers of the rows and columns, so that no matter where we copy this formula to later, the locked range will not be changed.
Add $ before row and column indexes to lock both row and column. The lookup range is $A$2:$E$7. Even copy this formula to H3, the range is still $A$2:$E$7.
Sometimes we just need to lock the row or column by adding $ before the index number of the row or column, so that the locked row or column will not be changed by the movement of the formula. For more examples you can refer to the section “VLOOKUP returns multiple column values”.
VLOOKUP with Duplicate Lookup Value
VLOOKUP has an important feature, when looking for duplicate values, the first duplicate value will be matched by default.
Look at the following example. There are two “Cindy” in the second column. When we use VLOOKUP to find the cell phone number corresponding to “Cindy”, the phone number of the first Cindy will be returned.
We can add more helper information to return unique matching values. For more details and explanations, you can refer to the section “VLOOKUP Two-conditional Lookup”.
VLOOKUP Lookup the Largest/Smallest value
Based on the property that when VLOOKUP encounters a duplicate lookup value, it only returns the value that matches the first lookup value, we can use VLOOKUP function to find the maximum or minimum value of the data. First of all, we need to sort the results of the column.
See the example below. Fill in the highest score in the exam.
Sort the results in descending order. Check on “Expand the selection” when soring result column, so that sorting will be applied to all three columns.
Then use VLOOKUP formula to return the score from the first Math.
VLOOKUP Reverse Lookup
Refer to above example we can see that VLOOKUP can only find data to the left of the lookup value in the data table. If you want to find the data to the right of the lookup value, it is called a reverse lookup. A reverse lookup needs the help of IF function.
The above example is a very typical example, usually we find the name by looking up the HRID, this is finding the HRID by name.
In the formula, the table_array is a IF formula, IF({1,0},B2:B6,A2:A6) will create a new two-column lookup table with the name first and the HRID second. This allows us to do reverse lookups.
Argument | Value |
lookup_value | F2 |
table_array | IF({1,0},B2:B6,A2:A6) |
index_num | 2 |
range_lookup | 0 |
VLOOKUP to Return Multiple Values
A one-to-many lookup returns multiple results by looking for a unique lookup value. VLOOKUP can implement a one-to-many lookup with the help of creating a helper column.
In this example, it is clear that there are two people in our table who belong to department “PA”. If we use the normal VLOOKUP formula “=VLOOKUP(G2,B2:E7,3,0)” to return the matching people in department “PA”, only one value “Ada” will be returned, because VLOOKUP will stop searching when it encounters the first “PA” in the data table.
To output all values corresponding to the lookup value, we need to create a new helper column. See details in below steps.
Step 1: First we insert a blank column on the left side of the data table, then enter =(C2=$G$2)+A1 in cell A2. Here A1 is 0, C2=$G$2 is a logical test, if it is true, it returns value 1, and if it is false, it returns value 0. In A2, the logical test is false, so the result is equal to “0+0=0”.
Step 2: Drag the handle down to copy the formula to A3:A7, so that for every PA department encountered, it will be increased by 1.
Step 3:In H2 enter the formula =VLOOKUP(ROW(A1),$A$2:$E$7,4,0).
In this formula, the lookup value is ROW (A1). ROW (A1) returns the cell reference A1’s row index number, which returns a value of 1. In fact, in the helper column, the first number “1” and the number “2” both correspond to the PA department (because the counter only increases by 1 when it encounters PA), what we want to do is to query the first two numbers “1” and “2”, so here we need the help of the Excel ROW function.
Step 4:Drag the handle down to fill out the form below. You can stop filling out the form when an error value appears, which means the search is complete.
VLOOKUP to Return Multiple Column Values
When we want to return multiple columns of data based on the lookup value, we can enter the VLOOKUP function once in each column. Although this can also be effective, it is not a quick way to do it.
Please see the following example. We want to know the “Name” and “Title” information based on the HRID provided. Although we can enter two VLOOKUP formulas with different column index numbers, this approach is not convenient if we want to get multiple column values from the original data table.
In above screenshot, you can see that we build the following VLOOKUP formula:
=VLOOKUP($F2,$A$2:$D$7,COLUMN(C1),0)
Using this formula, we can drag the handle directly down to fill the “Name” and “Title” information in the form we want (G2:H3).
The steps are as follows:
Step 1: In cell G2 enter the formula =VLOOKUP().
Step 2: Enter cell $F2 in the lookup value and lock column F with ($) because the horizontal fill needs to keep column F unchanged. This step ensures that if you drag this formula to fill H2, column F remains unchanged and the lookup value remains $F2 (the column is locked, the row remains unchanged).
Step 3: Enter $A$2:$D$7 in table_array. Add ($) in front of the column and row indexes to lock this range. Therefore, this range remains the same no matter where the user copies the formula.
Step 4: Enter COLUMN(C1) in col_index_num. COLUMN(C1) returns cell C1’s column number, which returns a value of 3. If drag this formula to H2, COLUMN(C1) is changed to COLUMN(D1), which returns a value of 4, so that VLOOKUP will returns a value in the fourth column of data table. The fourth column is “Title”.
Step 5: Enter “0” in range_lookup to perform an exact match.
Step 6: Drag the handle down to fill the form G2:H3.
There is another way to return multiple column values for multiple items at the same time based on the same lookup value.
See the following example.
Step1: Select range H2:I2.
Step2: In the formula bar enter the VLOOKUP formula =VLOOKUP(G2,A2:E7,{3,5},FALSE).
Please refer to the third argument col_index_num, different from usual, we only enter one column number in this argument, this time we enter an array containing two column numbers, so in step #1 we choose the range H2:I2 (one row and two columns) to save the returned result.
Step3: As this is an array formula, press Control+Shift+Enter to load result.
VLOOKUP Two-Conditional Lookup
This situation applies to tables with duplicate lookup values, because the lookup value is not unique and VLOOKUP may get the wrong result, so we need to add a condition as the lookup value.
See the following example. There are two conditions “PA” and “Lead”. “PA” and “Lead” are both duplicate in the table. We need to get correct person’s name based on the two conditions.
In this example, the formula is =VLOOKUP(F2&G2,IF({1,0},B2:B7&D2:D7,C2:C7),2,0).
To solve this problem, we can combine the two conditions “PA” and “Lead” into “PALead” and combine the content in the “Department” column and “Title” column into one column, then we can lookup “PALead” in this column.
In addition to the new column, we should also add the “Name” column (where we want to get the values) to the right of the new column. This will give us a new table with two columns, the first with the “Department” and “Title”, and the second with “Name”.
To create this new data table, we need the help of Excel’s IF function. The formula IF({1,0},B2:B7&D2:D7,C2:C7) can create a table according to our requirements.
IF({1,0},B2:B7&D2:D7,C2:C7) returns the following array:
{"RCLead","Nova";"PALead","Ada";"DCEngineer","Cindy";"ENEngineer","Kris";"VBProject Manager","Steven";"PAProject Manager","Calvin"}
In this example, we enter F2&G2 in lookup value. We concentrate F2 and G2 to get a new condition “PALead”.
After completing above steps, enter “2” in col_index_num to return a value in “Name” column, and enter “0” in range_lookup to perform an exact match. As this is an array formula, so we should press “Control+Shift+Enter” to get a value.
VLOOKUP with Wildcards
VLOOKUP can perform the search function normally if the lookup value contains wildcards. A wildcard is a special statement containing mainly asterisk (*) and question mark (?), wildcards are often used to fuzzy search for files. It can be used in place of one or more real characters when searching for a folder; wildcards are often used in place of one or more real characters when the real characters are not known or when you are too lazy to type the full name.
- (*) – An asterisk stands for one or more characters
- (?) – A question mark stands for single one character
See the example below.
In this example, only one letter “S” is shown in cell F2. We cannot look up this letter in the “Name” list because the spelling of the name is incomplete. We can determine that the first character inside the name is “S”. If we want to find a suitable title for this person, we can set the lookup value to F2&”*”, and the symbol (&) will combine the letter “S” in cell F2 with the wildcard character (*), so the lookup value is “S*”.
“S*” represents a string that starts with the letter “S”. Therefore, when searching the list of names, “Steven” will be locked and the corresponding value “Project Manager” will be returned.
VLOOKUP Range Lookup by Approximate Match
A range lookup is a range that corresponds to a result, such as a grade based on a range of scores like “50 to 60”. In order to perform a range lookup, we need to use the VLOOKUP approximate match function.
First we need to make sure that the first column of the table is sorted in ascending order. The lookup value must fall in one of the data ranges in the first column.
We then use VLOOKUP to do a regular lookup, where we need to set the fourth parameter to 1, which means that VLOOKUP performs an approximate match.
The following example is copied from “VLOOKUP Approximate Match”. We have added a column in front of the original table to explain the individual score segments. You can refer to “VLOOKUP Approximate Matching” for more details.
You just need to remember that when VLOOKUP performs an approximate match, VLOOKUP will take the value which is the largest value among all values less than the actual lookup value as the lookup value.
VLOOKUP Across Different Worksheets or Workbooks
Sometimes, the lookup value and the data table (containing the return value) are not in the same worksheet, or even in the same workbook. If you encounter a situation where the lookup value and the data table are stored in different worksheets, or in different workbooks, how does VLOOKUP handle this situation.
Lookup Value and Lookup Range in Same Worksheet
The simplest case is when the lookup value and the data table are in the same worksheet. This usually happens when there are not many lookup values, or when you are looking for a value temporarily. A common example is finding a value that corresponds to a value in the current data table. For example, finding an employee name or employee phone number based on an HRID.
See the following example.
Formula: =VLOOKUP(G2,$A$2:$E$6,3,0)
In this example, you can just operate the VLOOKUP function as usual. In the same worksheet, select the lookup value and lookup range, determine the column where the return value is located, and then select exact match or approximate match according to your needs, and you have completed a VLOOKUP lookup.
Arguments
Argument | Value | Description |
lookup_value | G2 | HRID=3 |
table_array | $A$2:$E$6 | Locked range $A$2:$E$6 |
index_num | 3 | Return value in “Name” column |
range_lookup | 0 | Exact match |
Lookup Value and Lookup Range in Different Worksheets
Usually, the lookup value and lookup range (return value) may not be in the same worksheet. For example, in our daily work, due to user access rights settings, some information is saved in worksheet1, which can be opened by everyone, while some information is saved in worksheet2, which is only available to people with manager privileges. Only people who have access rights to both worksheets can open both worksheets for queries. At this point, when a manager wants to do an association of the two worksheets, they can use the VLOOKUP function to do a cross-sheet association.
Sheet1 – A table with department, title, and cell phone information missing.
Sheet2 – Employee Personal Information
Sheet3 – Employee Function Information
To fill in the missing information in the first table, we need to use the name as a key to connect the tables in the other worksheets.
The steps are as follows.
Step 1: In sheet1->B2, type “=VLOOKUP()”. A hint floats under “=VLOOKUP”.
Step 2: Click A2 to fill in the lookup_value.
Step 3: Since department information is saved in sheet3, so we go to sheet3 and select the lookup range A2:C7.
Step 4: You will find that once you switch to sheet3, “Sheet3!” will be automatically filled into table_array.
Step 5: Select lookup range A2:C7 to complete table_array.
Step 6: Add ($) to the formula to lock this range. Once ($) is entered before the column index, Excel will return to sheet1->B2.
Step 7: Fill in col_index_num “2”. Department is the second list of lookup range.
Step 8: Enter “0” or “False”/ “1” or “True” to perform an exact match or an approximate match lookup.
Step 9: Press the Enter key to come up with a value.
Step 10: Drag the handle down to fill the list. Because the lookup range is locked by ($), if you copy this formula to other cells, the lookup range will not be changed.
Step 11: Copy the formula from B2 to C2. The lookup value is changed to B2. Others are not changed.
Step 12: In the formula bar, update B2 to A2, keeping the query value unchanged. Update col_index_num to “3” because the title is saved in the third list of lookup range.
Step 13: Press the Enter key to come up with a value. Drag the handle down to fill the list.
Note: In this case, the department and title are saved in the same worksheet and they are adjacent to each other, we can create just one VLOOKUP formula to populate both lists at the same time. More details can be found in the section “VLOOKUP Returns Multiple Column Values”.
The formula is =VLOOKUP($A2,Sheet3!$A$2:$C$7,COLUMN(B2),0). Drag this handle to fill B2:C7.
Step 14: Repeat above steps to fill Cell Phone values.
Lookup Value and Lookup Range in Different Workbooks
We can see from the above example that when the lookup value and the lookup range are in two different Excel worksheets, the name of the worksheet where the lookup range is located will be added in front of the range reference, such as “Sheet2!$B$2:$C$7”. Therefore, the VLOOKUP function will go to the correct worksheet to look up the lookup value.
This time, we copy the data table from sheet1 to workbook 1->sheet A, then copy the table from sheet2 to workbook 2->sheet B, and then copy the table from sheet3 to workbook 3->sheet C. Now, the lookup values and lookup ranges are in three different workbooks rather than three different worksheets.
Workbook1->Sheet A
Workbook2->Sheet B
Workbook3->Sheet C
Now that workbook 1 lists a table with employee names, departments and cell phone numbers, we want to populate the other employee information based on the first column of employee names. The other information is saved in workbook 2 and workbook 3 respectively. Since the name is the only value that appears in these three tables, it can be associated with all three workbooks.
The steps are as follows.
Step 1: In workbook1->sheet2->B2, type “=VLOOKUP()”. A hint floats under “=VLOOKUP”.
Step 2: Click A2 to fill in the lookup_value.
Step 3: Since department information is saved in workbook3->sheet C, so we go to orkbook3->sheet C to select the lookup range A2:C7.
Step 4: Go to workbook3->sheet C. You will find that once you switch to sheet C.
‘[Workbook3]Sheet C’! will be automatically filled into table_array. Select the lookup range A2:C7 to complete the filling of table_array. the range is automatically locked.
Step 5: Return to workbook1->Sheet A. Fill in col_index_num “2”. Department is the second list of lookup range.
Step 6: Enter “0” or “False”/ “1” or “True” to perform an exact match or an approximate match lookup.
Step 7: Press the Enter key to come up with a value.
Step 8: Drag the handle down to fill the list. Because the lookup range is locked by ($), if you copy this formula to other cells, the lookup range will not be changed.
Step 9: Copy the formula from B2 to C2. The lookup value is changed to B2. Others are not changed. Update B2 to A2, keeping the lookup value unchanged. Update col_index_num to “3” because the title is saved in the third list of lookup range.
Note:
In this case, the department and title are saved in the same worksheet and they are adjacent to each other, we can create just one VLOOKUP formula to populate both lists at the same time. More details can be found in the section “VLOOKUP Returns Multiple Column Values”.
The formula is =VLOOKUP($A2,'[Workbook3]Sheet C’!$A$2:$C$7,COLUMN(B1),0). Drag this handle to fill B2:C7.
Step 10: Repeat above steps to fill Cell Phone values.
VLOOKUP Error Value Handling
VLOOKUP returns error #N/A
Failure reason 1:
The lookup value doesn’t appear in the lookup range.
Resolution:
Check lookup value and make sure it lists in the first column of data table.
Failure reason 2:
The lookup value doesn’t list in the first column.
Resolution:
Check lookup value and make sure it lists in the first column of data table.
Failure reason 3:
Lookup range is not locked. So lookup value cannot be found in moved lookup range.
Resolution:
Add $ to lock lookup range. Make sure lookup value exists in the first column of data table.
VLOOKUP returns error #REF!
Failure reason 1:
The lookup range cannot be found. For example, delete Sheet2, which holds the lookup range, so the range reference cannot be found.
Sheet2 is not deleted.
Sheet2 is deleted.
Resolution:
Check lookup range to ensure values are saved in the entered range reference.
Failure reason 2:
If the number of columns entered in the third argument is greater than the actual number of columns in the second argument lookup range.
Resolution:
Check the third argument “col_index_num”.
VLOOKUP returns error #VALUE
Failure reason 1:
If the argument “col_index_num” contains text or is less than 0, then error #VALUE is returned. Normally, we do not accidentally write a negative value or text to a column index, but if the column index is a value passed by another formula, it is possible to generate this error.
Resolution:
Check the third argument “col_index_num”.
VLOOKUP returns error #NAME
Failure reason 1:
The lookup value format is incorrect.
“Alice” is a text, if we enter “Alice” in the lookup value, rather than using the cell reference G2, the text or string should be quoted in double quotes, as shown below.
Resolution:
Check format for all arguments.
VLOOKUP Eliminate the Error Value/ No Error Value Returned by VLOOKUP
In VLOOKUP formulas, if you cannot find the lookup value in the provided data table, then the formula will return an error value such as #N/A, which doesn’t seem appropriate because displaying the error in the table will make the table look unprofessional. So, how to eliminate this error value or make it show 0 or empty when it returns and detects an error, we will show you a convenient way to eliminate the error.
The VLOOKUP formula returns an error because HRID “7” doesn’t appear in the first column. To convert the error value to empty or “0” value, we can add the IFERROR function in front of the VLOOKUP function.
Use formula =IFERROR(VLOOKUP(G3,$A$2:$E$7,2,0),”0″) to set error value to “0”.
Use formula =IFERROR(VLOOKUP(G3,$A$2:$E$7,2,0),””) to set error value to empty.
Summary
- There are four arguments in parentheses. The last parameter, range_lookup, is a logical value, we often enter a “0” or “False” for exact match.
- Lookup value is a very important argument, it can be a value, text string, or range reference. We often use the range reference. There are three special points to remind.
- Lookup value cell format and lookup range cell format should be consistent.
- Sometimes we need to add $ to lock lookup range.
- We can use “&” to connect the contents of a number of cells as the parameters of the search.
- The value of lookup_value must be in the first column of table_array.
- The VLOOKUP function returns the first value in the lookup range that matches the lookup value if the lookup value is duplicate.