When we have a vast dataset in Excel, it’s a bit hard to look at and find specific data from the dataset. But Excel is awesome, it has many built-in functions to find our desired data from a dataset no matter what the size of the dataset is. In this article, We will discuss 5 easy ways to create a lookup table in Excel for finding specific data from the dataset.
What Is a Lookup Table in Excel?
If we have a large dataset and we want to find specific data from the dataset, it’s quite hazardous. But Excel has some functions which can help us to resolve the problem. We can create a separate table in the worksheet to find desired data from the dataset. Of course, we have to use some functions of Excel in the table. This sort of table is usually known as Lookup Table in Excel.
We will discuss here 5 easy ways to create a lookup table in Excel. Though we will use different functions in each way, they will do an almost similar task. For example, suppose we have a dataset containing some Product ID, Delivery Status and Price of those products. We will show the way to find specific data from the dataset by creating Lookup Table.
1. Applying the LOOKUP Function to Create a Lookup Table in Excel
We can use the LOOKUP Function of Excel to create the lookup table. For example, if we want to find the Price of a Product ID from the dataset, we can use the LOOKUP function to find that value. Follow the steps given below for the procedures.
- Firstly, write the Product ID in Cell F5.
- Secondly, select Cell G5 where we want the Price to appear.
- Then, write the following formula in that cell.
=LOOKUP(F5,B5:B11,D5:D11)- Finally, press Enter.
- Consecutively, we will see the Price of the Product ID.
In the formula, we used the LOOKUP function with arguments,
- Cell F5 contains the value to look up.
- B5:B11 is the range where that value should be found.
- Â D5:D11 is the range where the corresponding result value is stored.
Read More:Â How to Create Table from Another Table in Excel
2. Inserting Excel VLOOKUP Function to Make Lookup Table
Instead of the LOOKUP function, we can also use the VLOOKUP function of Excel to create the lookup table. For the procedures, follow the steps given below.
- First, write the Product ID in Cell F5 whose Price we want to find.
- Then, select Cell G5 and write the following formula in the cell.
=VLOOKUP(F5,B5:D11,3,FALSE)- Lastly, hit Enter.
- We will see the result in Cell G5 instantly.
In the formula, we used the VLOOKUP function with arguments,
- Cell F5 is the look-up value.
- B5:D11 is the range of data where the lookup value can be found.
- 3 is the column number from where the result will be picked.
- FALSE means the look-up value should be matched exactly.
3. Combining INDEX & MATCH Functions for a Lookup Table in Excel
The combination of the INDEX function and the MATCH function can be used to create the lookup table. Please follow the following steps for the procedures.
- First, write the Product ID in Cell F5 whose Price we will find.
- Secondly, write the following formula in Cell G5.
=INDEX(D5:D11,MATCH(F5,B5:B11,0))- Lastly, hit Enter.
- Finally, we will see the desired Price in Cell G5.
In this formula,
- The MATCH function takes D5:D11 as an argument which is the range from where the function will find the desired value.
- And MATCH(F5,B5:B11,0) This part gives the row number as an argument of the MATCH function.
- In the MATCH function, F5 is the look-up value in the range B5:B11 and 0 means the match should be exact.
4. Generating Lookup Table with the HLOOKUP Function
Another way to create the lookup table is by using the HLOOKUP function. Let’s follow the steps given below for the procedure.
- First, in Cell F5 write the name of the column from where we will pick the desired data.
- Then, in Cell G5 write the formula given below.
=HLOOKUP(F5,B4:D11,5,0)- Consecutively, press Enter.
- Finally, we will see the Product ID of the 5th row(as we used 5 as an argument) of the selected column.
In the formula, we used the HLOOKUP function whose arguments are:
- Cell F5 contains the value to look for.
- B4:D11 is the table array where the function will look for the value.
- 5 denotes the desired value in the 5th row of the column where the lookup for value is found.
- 0 denotes the match should be exact.
Read More: How to Create Table from Another Table with Criteria in Excel
5. Using the XLOOKUP Function to Create a Lookup Table
The XLOOKUP Function is an excellent choice to create a lookup table in Excel. Follow the given steps for creating a lookup table with the XLOOKUP function.
- First, write the Product ID in Cell F5 whose Price we want to find.
- Then, write the following formula in Cell G5.
=XLOOKUP(F5,B5:B11,D5:D11)- Besides, press Enter.
- Finally, we will see our desired data(Price) in Cell G5.
In the formula of the XLOOKUP function we used the following cells as arguments,
- Cell F5 as the value to look for.
- B5:B11 is the range from where the function will find the value.
- D5:D11 is the range from where the function will find the matched output.
Download Practice Workbook
You can download the practice workbook from here to exercise.
Conclusion
Lookup table is a very useful tool to find data from a large dataset, it saves both time and labor. Here, we have discussed 5 easy ways to create a lookup table in Excel. If you face any problem following the procedures, please let us know by commenting.
Related Articles
- How to Mirror Table on Another Sheet in ExcelÂ
- How to Create Table from Multiple Sheets in Excel
- How to Make 3D Table in Excel
- How to Make a Conversion Table in Excel
- How to Make a Decision Table in ExcelÂ
- How to Create a League Table in ExcelÂ
- How to Make a Table Bigger in ExcelÂ
<< Go Back to Make a Table | Excel Table | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
