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.

**Table of Contents**hide

## 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:** **Create Table in Excel Using Shortcut**

## 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.

**Similar Readings**

## 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**

**Create a Table in Excel Based on Cell Value****How to Create a Table with Existing Data in Excel****How to Create a Table Without Data in Excel****How to Create a Table with Merged Cells in Excel****How to Create a Table in Excel with Multiple Columns****How to Make a Table in Excel with Lines****How to Create Table from Another 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 Mirror Table on Another Sheet in Excel****How to Create Table from Multiple Sheets in Excel****How to Make a Table Bigger in Excel****How to Create a Table with Headers Using Excel VBA****How to Add New Row Automatically in an Excel Table**