Lookup functions are among the most frequently used functions in Excel. For example, the vlookup function is the third most used function (after the sum and average function). Lookup functions look up a specified element in a range and return the corresponding information. This blog introduces the basics and some advanced applications of the lookup function.
The vlookup (vertical lookup) function looks up the value in a range by rows (downwards) and then returns the corresponding information in the same row (rightwards).
Below is an example of how the vlookup function works.
Assume the following dataset about the payrolls of a company:
A | B | C | D | |
---|---|---|---|---|
1 | Employee_First_Name | Employee_Last_Name | Base_Salary | Bonus |
2 | Aaron | Davis | 37000 | 16000 |
3 | Betty | Browns | 44000 | 21000 |
4 | Carl | Williams | 38000 | 19000 |
5 | Daisy | Johnson | 43000 | 20000 |
6 | Eric | Wilson | 32000 | 20000 |
7 | Francis | Jones | 37000 | 24000 |
To look up the base salary of Daisy, the vlookup function looks downwards along the first column until the text “Daisy” (row 5) is found. It then looks rightwards across the columns to find and return the value in the “Base_Salary” column.
The vlookup function has the following syntax:
Example 1: Basic applications
Assume the following dataset [you may paste it to Excel]:
A | B | C | D | |
---|---|---|---|---|
1 | Employee_First_Name | Employee_Last_Name | Base_Salary | Bonus |
2 | Aaron | Davis | 37000 | 16000 |
3 | Betty | Browns | 44000 | 21000 |
4 | Carl | Williams | 38000 | 19000 |
5 | Daisy | Johnson | 43000 | 20000 |
6 | Eric | Wilson | 32000 | 20000 |
7 | Francis | Jones | 37000 | 24000 |
8 | ||||
9 | Name | Daisy | ||
10 | Base Salary | |||
11 | Bonus |
Cell B9 contains the name of the employee to be looked up, and cells B10 and B11 are for displaying the base salary and bonus of the employee.
In cell B10, the formula = vlookup(B9, A1:D7, 3, FALSE) returns the base salary of the employee whose name is specified in cell B9. Here is the process performed:
Example 2: Basic applications + Absolute References
Assume the following dataset [you may paste it to Excel]:
A | B | C | D | E | |
---|---|---|---|---|---|
1 | Employee_First_Name | Employee_Last_Name | Base_Salary | Bonus | |
2 | Aaron | Davis | 37000 | 16000 | |
3 | Betty | Browns | 44000 | 21000 | |
4 | Carl | Williams | 38000 | 19000 | |
5 | Daisy | Johnson | 43000 | 20000 | |
6 | Eric | Wilson | 32000 | 20000 | |
7 | Francis | Jones | 37000 | 24000 | |
8 | |||||
9 | Return Column | Name | Daisy | Carl | Eric |
10 | 3 | Base Salary | |||
11 | 4 | Bonus |
In this example, a “Return_Column” is added to specify the index of the column to return the result. In cell C10, the formula = vlookup(C9, A1:D7, A10, FALSE) thus finds the base salary of Daisy.
How can we edit the formula such that the formula can be copied to other cells in the same column (e.g., cell C11) and in the same row (e.g., cells D10 through E10)? Here are the steps:
The formula = vlookup(C$9, $A$1:$D$7, $A10, FALSE) in cell C10 can then be applied to cells C11 and D10 through E11 to find the salaries and bonuses of Daisy, Carl, and Eric.
Example 3: Approximate Match
The approximate match of the vlookup function applies when the lookup key is within a range of data rather than taking an exact value in the dataset.
The following dataset shows the maximum incomes for U.S. individual taxpayers above which the corresponding marginal tax rate applies in 2019 (e.g., if the taxpayer earns more than $9,700, the taxpayer is subject to a marginal tax rate of 12%).
A | B | C | D | E | |
---|---|---|---|---|---|
1 | Maximum Income | Tax Rate | Taxpayer Income | $75,000 | |
2 | – | 10% | 37000 | Marginal Tax Rate | |
3 | 9,700 | 12% | 44000 | ||
4 | 39,475 | 22% | 38000 | ||
5 | 84,200 | 24% | 43000 | ||
6 | 160,725 | 32% | 32000 | ||
7 | 204,100 | 35% | 37000 | ||
8 | 510,300 | 37% |
If a taxpayer earns $75,000, his or her marginal tax rate can be found using the approximate match mode of the vlookup function. [i.e., = vlookup(E1, A1:B8, 2, TRUE)]. The approximate match mode finds the range that the datum falls in and returns the corresponding value for the range. Because the income of $75,000 falls within the range between $39,475 and $84,200, the taxpayer’s marginal tax rate is 22%.
The hlookup (horizontal lookup) function is identical to the vlookup function except that it looks up the specified value in a range by columns (rightwards) and then returns the corresponding information in the same column (downwards).
Below is an example of how the hlookup function works.
The dataset is the transpose of the dataset used in the vlookup function.
To look up the base salary of Daisy, the hlookup function looks rightwards along the first row until it finds the text “Daisy” (column E). It then looks downwards across the rows to find and return the value in the “Base_Salary” row.
The hlookup function has the following syntax:
Assume the following dataset [you may paste it to Excel]:
A | B | C | D | E | F | G | |
---|---|---|---|---|---|---|---|
1 | Employee_First_Name | Aaron | Betty | Carl | Daisy | Eric | Francis |
2 | Employee_Last_Name | Davis | Brown | Williams | Johnson | Wilson | Jones |
3 | Base_Salary | 37000 | 44000 | 38000 | 43000 | 32000 | 37000 |
4 | Bonus | 16000 | 21000 | 19000 | 20000 | 20000 | 24000 |
5 | |||||||
6 | Name | Daisy | |||||
7 | Base Salary |
The formula = hlookup(B6, $A$1:$G$4, 3, FALSE) returns base salary of Daisy. Here is the process performed in the hlookup function:
The above lookups are based on a single lookup key (i.e., a single-criterion lookup). While the vlookup and hlookup functions do not deal with multiple lookup keys, the different columns (rows) of the dataset can be joined to create a unique and single lookup key.
Example:
Assume the following dataset [you may paste it to Excel]:
A | B | C | D | |
---|---|---|---|---|
1 | Employee_First_Name | Employee_Last_Name | Base_Salary | Bonus |
2 | Aaron | Davis | 37000 | 16000 |
3 | Aaron | Brown | 44000 | 21000 |
4 | Aaron | Williams | 38000 | 19000 |
5 | Daisy | Johnson | 43000 | 20000 |
6 | Eric | Wilson | 32000 | 20000 |
7 | Francis | Williams | 37000 | 24000 |
8 | ||||
9 | First Name | Aaron | ||
10 | Last Name | Williams | ||
11 | Base Salary |
In the above dataset, three employees have the first name “Aaron” and two have the last name “Williams.” To correctly look up the base salary of the employee Aaron Williams, the data in both columns A and B (multiple criteria) must be used. To perform a multiple-criteria lookup using the vlookup or hlookup function, perform the following steps:
Below are the limitations of the vlookup and hlookup function:
1) The functions only perform single-direction lookups. Consider the following dataset:
A | B | C | D | |
---|---|---|---|---|
1 | Base_Salary | Bonus | Employee_First_Name | Employee_Last_Name |
2 | 37000 | 16000 | Aaron | Davis |
3 | 44000 | 21000 | Betty | Brown |
4 | 38000 | 19000 | Carl | Williams |
5 | 43000 | 20000 | Daisy | Johnson |
6 | 32000 | 20000 | Eric | Wilson |
7 | 37000 | 24000 | Francis | Jones |
The vlookup function only looks up data downwards and rightwards. For example, the formula = vlookup(“Aaron”, A1:D7, 1, FALSE) does not return the base salary of Aaron Davis. Rather, it returns an error. Thus, to use the vlookup and hlookup functions, the lookup key must be located on the leftmost (uppermost) of the dataset.
Also, the two functions cannot search the dataset in reverse order (e.g., bottom-up or from right to left).
2) The functions return errors when the value is not found. Consider the following dataset:
A | B | C | D | |
---|---|---|---|---|
1 | Employee_First_Name | Employee_Last_Name | Base_Salary | Bonus |
2 | Aaron | Davis | 37000 | 16000 |
3 | Betty | Brown | 44000 | 21000 |
4 | Carl | Williams | 38000 | 19000 |
5 | Daisy | Johnson | 43000 | 20000 |
6 | Eric | Wilson | 32000 | 20000 |
7 | Francis | Jones | 37000 | 24000 |
The formula = vlookup(“Hanks”, A1:D7, 3, FALSE) returns an error because the name “Hanks” is not contained in the list of names.
3) An inserted column (row) distorts the result. Consider the following dataset:
If a column named “Employee_ID” is inserted between columns B and C of the dataset, the formula = vlookup(“Aaron”, A1:D7, 4, FALSE), which originally returned Aaron’s bonus, returns his base salary because the column index for bonus has changed to 5.
The xlookup function enhances both the vlookup and hlookup functions while using a simpler syntax:
Example 1:
A | B | C | D | |
---|---|---|---|---|
1 | Employee_First_Name | Employee_Last_Name | Base_Salary | Bonus |
2 | Aaron | Davis | 37000 | 16000 |
3 | Aaron | Brown | 44000 | 21000 |
4 | Aaron | Williams | 38000 | 19000 |
5 | Daisy | Johnson | 43000 | 20000 |
6 | Eric | Wilson | 32000 | 20000 |
7 | Francis | Williams | 37000 | 24000 |
8 | ||||
9 | Name | Daisy | ||
10 | Base Salary | |||
11 | Bonus |
The xlookup function performs the same function as the vlookup function.
In cell B10, the formula = xlookup(B9, A1:A7, C1:C7) looks up the text “Daisy” in the lookup range A1 through A7 (Employee_First_Name column) and returns the corresponding value in the return range C1 through C7 (Base_Salary column).
In cell B11, the formula = xlookup(B9, A1:A7, D1:D7) looks up the text “Daisy” in the Employee_First_Name column and returns the corresponding value in the Bonus column.
Example 2:
A | B | C | D | E | F | G | |
---|---|---|---|---|---|---|---|
1 | Employee_First_Name | Aaron | Betty | Carl | Daisy | Eric | Francis |
2 | Employee_Last_Name | Davis | Brown | Williams | Johnson | Wilson | Jones |
3 | Base_Salary | 37000 | 44000 | 38000 | 43000 | 32000 | 37000 |
4 | Bonus | 16000 | 21000 | 19000 | 20000 | 20000 | 24000 |
5 | |||||||
6 | Name | Daisy | 32000 | 20000 | |||
7 | Base Salary | ||||||
8 | Bonus | ||||||
9 | Name | Daisy | |||||
10 | Base Salary | ||||||
11 | Bonus |
The xlookup function can also replace the hlookup function.
In cell B7, the formula = xlookup(B7, A1:G1, A3:G3) looks up the text “Daisy” in the lookup range A1 through G1 (Employee_First_Name row) and returns the corresponding value in the return range A3 through G3 (Base_Salary row).
In cell B8, the formula = xlookup(B7, A1:G1, A4:G4) looks up the text “Daisy” in Employee_First_Name row and returns the corresponding value in the Bonus row.
Example 3:
A | B | C | D | |
---|---|---|---|---|
1 | Base_Salary | Bonus | Employee_First_Name | Employee_Last_Name |
2 | 37000 | 16000 | Aaron | Davis |
3 | 44000 | 21000 | Betty | Brown |
4 | 38000 | 19000 | Carl | Williams |
5 | 43000 | 20000 | Daisy | Johnson |
6 | 32000 | 20000 | Eric | Wilson |
7 | 37000 | 24000 | Francis | Jones |
8 | ||||
9 | First Name | Daisy | ||
10 | Base Salary | |||
11 | Bonus |
The xlookup function is not constrained to data lookup in a single direction [i.e., the lookup key does not need to be located in the leftmost column (uppermost row)].
The formula = xlookup(B9, C1:C7, A1:A7) still returns the base salary of Daisy. Similarly, the formula = xlookup(B9, C1:C7, B1:B7) returns her bonus.
Example 4:
A | B | C | D | |
---|---|---|---|---|
1 | Employee_First_Name | Employee_Last_Name | Base_Salary | Bonus |
2 | Aaron | Davis | 37000 | 16000 |
3 | Betty | Brown | 44000 | 21000 |
4 | Carl | Williams | 38000 | 19000 |
5 | Daisy | Johnson | 43000 | 20000 |
6 | Eric | Wilson | 32000 | 20000 |
7 | Francis | Jones | 37000 | 24000 |
8 | ||||
Name | Hanks | |||
10 | Base Salary | |||
11 | Bonus |
The [ifnotfound] argument of the xlookup function allows us to specify what to return if the lookup key is not found.
In cell B10, the formula = xlookup(B9, A1:A7, C1:C7, “No Records”) specifies that when the lookup key is not found in the dataset, the function returns the text “No Records” instead of an error.
Example 5:
A | B | C | D | |
---|---|---|---|---|
1 | Employee_First_Name | Employee_Last_Name | Base_Salary | Bonus |
2 | Aaron | Davis | 37000 | 16000 |
3 | Betty | Brown | 44000 | 21000 |
4 | Carl | Williams | 38000 | 19000 |
5 | Daisy | Johnson | 43000 | 20000 |
6 | Eric | Wilson | 32000 | 20000 |
7 | Francis | Jones | 37000 | 24000 |
8 | ||||
Name | Daisy | |||
10 | Base Salary | |||
11 | Bonus |
The insertion of a column (row) does not distort the lookup result. If the formula = xlookup(B9, A1:A7, C1:C7) is originally used to find the base salary of Daisy, inserting a column between columns B and C does not distort the lookup result because the formula is automatically updated to = xlookup(B9, A1:A7, D1:D7).
Example 6:
A | B | C | D | |
---|---|---|---|---|
1 | Employee_First_Name | Employee_Last_Name | Base_Salary | Bonus |
2 | Aaron | Davis | 37000 | 16000 |
3 | Betty | Brown | 44000 | 21000 |
4 | Carl | Williams | 38000 | 19000 |
5 | Daisy | Johnson | 43000 | 20000 |
6 | Eric | Wilson | 32000 | 20000 |
7 | Francis | Jones | 37000 | 24000 |
8 | ||||
Name | Daisy | |||
10 | Base Salary | |||
11 | Bonus |
The xlookup function has 4 modes of matching.
The match_mode argument takes the following numbers:
Value | Match_Mode |
---|---|
0 | Exact match and return an error if not found |
-1 | Exact match and return the next smaller item if not found |
1 | Exact match and return the next larger item if not found |
2 | Approximate match |
In cell B10, the formula = xlookup(B9, A1:A7, C1:C7, “No Record”, 0) returns the base salary of Daisy.
Example 7:
A | B | C | D | |
---|---|---|---|---|
1 | Employee_First_Name | Employee_Last_Name | Base_Salary | Bonus |
2 | Aaron | Davis | 37000 | 16000 |
3 | Betty | Brown | 44000 | 21000 |
4 | Carl | Williams | 38000 | 19000 |
5 | Daisy | Johnson | 43000 | 20000 |
6 | Eric | Wilson | 32000 | 20000 |
7 | Francis | Jones | 37000 | 24000 |
8 | ||||
Name | Aaron | |||
10 | Base Salary | |||
11 | Bonus |
The xlookup function allows data lookup in the reverse order.
The search_mode argument takes the following numbers:
Value | Search_Mode |
---|---|
1 | Search starting from the first item |
-1 | Search starting from the last item |
2 | Search a sorted list in ascending order |
-2 | Search a sorted list in descending order |
In cell B10, the formula = xlookup(B9, A1:A7, C1:C7, “No Record”, 0, 1) returns the base salary of Aaron Davis.
In cell B11, the formula = xlookup(B9, A1:A7, D1:D7, “No Record”, 0, -1) returns the bonus of Aaron Jones but not that of Aaron Davis.
Multiple-criteria lookup
A | B | C | D | |
---|---|---|---|---|
1 | Employee_First_Name | Employee_Last_Name | Base_Salary | Bonus |
2 | Aaron | Davis | 37000 | 16000 |
3 | Betty | Brown | 44000 | 21000 |
4 | Carl | Williams | 38000 | 19000 |
5 | Daisy | Johnson | 43000 | 20000 |
6 | Eric | Wilson | 32000 | 20000 |
7 | Francis | Jones | 37000 | 24000 |
8 | ||||
9 | First Name | Aaron | ||
10 | Last Name | Williams | ||
11 | Base Salary |
The xlookup function does not require a helper column for multiple-criteria lookups.
In cell B11, the formula = xlookup(B9 & B10, A1:A7 & B1:B7, C1:C7) directly finds the lookup key “Aaron Williams” from the lookup range and returns the base salary of the employee with the first name “Aaron” and the last name “Williams.”
At Gleim, we know learning data analytics is vital for future accounting professionals. That is why we are offering this series of data analytics blogs and continually updating all of our review materials for CPA, CMA, and CIA with the necessary information you need to pass your exams!
We’ll continue our weekly blog series. Check back regularly for all exam news and study tips!
Python Lessons
Python Basics
Conditional Statements
Loops
Functions and Modules
Numerical Python (NumPy)
Pandas Basics
Pandas Data Capture and Cleansing
Merging and Grouping
Manipulating Text and Datetime Data
Visualization
Web Scraping
Errors and Exceptions