Zahid Shuvo
While working on big datasets of big companies, educational institutions, and financial organizations like banks, it is required to find particular data. In that case, the VLOOKUP Function is the perfect tool. Today, in this article, we’ll learn four suitable examples of using the VLOOKUP function with an exact match in Excel effectively with appropriate illustrations. We will break down every step of using this function with several examples below. Here is the overview of our today’s dataset.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Introduction to VLOOKUP Function
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]
Arguments
| Arguments | Required/Optional | Explanation |
|---|---|---|
| Lookup_value: | Required | It specifies the value from the first column that we want to look up. |
| Table_array | Required | It is the table array where Excel search for data. |
| Col_index_num | Required | It is an integer number that represents the column number from the left. |
| [range_lookup] | Optional | It defines whether the search should return an exact match or not |
- If there is no exact match on the data array, we have to use TRUE for an approximate match.
true=approximate match
- For an exact match, we have to use FALSE in the formula.
false=exact match
- But if you don’t use TRUE or FALSE in the formula, It takes TRUE in the formula and returns an approximate match.
- The VLOOKUP Function is the perfect tool to find out any particular data from Excel’s data table. It returns an approximate or exact match value based on the criterion added to the formula “True” or ” False”. The process of using the VLOOKUP Function is discussed step by step with a few real-life examples.
Available In
The VLOOKUP function is available from Excel 2003 to the present version. While writing this article, I used Office 365.
4 Practical Examples to Use VLOOKUP Function Exact Match in Excel
Example 1: Finding Particular Student’s Marks in a Specific Subject
Suppose, we have a dataset of students’ mark sheets for the exam. It contains students’ Names, and their marks in the subjects Physics, Math, and English. Here we will use the VLOOKUP function to find particular students’ marks in a subject.
Let’s say we want to find out if Phoebe Buffays obtained marks in Mathematics. Here is how we do it. If you follow the steps below step by step, you will be able to do it eventually.
Steps:
- First of all, select cell D17, and write down the following formula in that cell.
- Hence, press Enter, as a result, you can find Phoebe Buffay’s marks in Math using the VLOOKUP function with an exact match. The mark in Math is 48.
- Inside the VLOOKUP function, B17 is the lookup_value, B5:E14 is the table_array, 3 is the col_index_num, and FALSE is the [range_lookup].
- The VLOOKUP function will look up the value of B17 in the table array from cell no B5 to E14. And as the column index number is given 3, it will show the corresponding value of cell B17 from column 3.
Example 2: Finding Sales of a Particular Employee
Suppose, we have a dataset of Sales statements of a company. It contains the employee’s Name, ID, Region, and Sales. Here we will use the VLOOKUP function to find particular employees’ sales.
Let’s say we want to find out Chandler Bings’ sales. Here is how we do it. If you follow the steps, you can do it eventually.
Steps:
- To begin with, select D17.
- After that, insert the following formula in that cell.
- Hence, press Enter. As a result, you will be able to find Chandler Bings’ sales, and the sale is $35,600.
Example 3: Searching Particular Price of a Commodity
Suppose, we have a dataset of prices of various products in a daily super shop. It contains the product’s Name, Rack No, Stock, and Price. Here we will use the VLOOKUP function to find a particular product’s price.
Let’s say we want to find out the price of Meat. Here is how we do it. If you follow the steps, you can do it eventually.
Steps:
- At the beginning, select D17.
- Further, write down the formula as,
- Hence, press Enter. As a result, you will be able to find the price of Meat is $168.
Example 4: Finding Address
Suppose, we have a dataset of addresses. It contains the Postcode, State, City, and Address. Here we will use the VLOOKUP function to find the address.
Let’s say we want to find out the Address based on Postcode. Here is how we do it. If you follow the steps, you can do it eventually.
Steps:
- First of all, select D17.
- Then, write down the formula as,
- Afterward, press Enter, As a result, you will be able to find Address. The Address is 289 Creek Drive.
Reason Behind VLOOKUP Function Not Working
Sometimes VLOOKUP function might not work and show #N/A because of the reasons behind it.
- The formula must contain the right Look_Up Here look_up value cell is B17, selecting any other cells except B17 will cause an error and it will show #N/A.
- If the Table array is not selected properly. It has to contain the Lookup value in the table array. The following picture shows #N/A Because the selected range starts from C5 to E14.
- Even if the Col_index_num is not inserted correctly. It might show #N/A.
Conclusion
This whole article covered the thorough process of application of the VLOOKUP Function exact match in Excel to find particular data from a large data table. Additionally, you can practice this Excel problem by downloading from this page. Yet, if you have any queries related to this problem please comment below. Moreover, you can visit www.exceldemy.com if you want to learn more about Excel-related problems.
