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 too 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

## Download Practice Workbook

You can download the practice workbook from here to exercise.

## What is 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**.

## 5 Easy Ways to Create a 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. Apply LOOKUP Function to Create 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 (8 Methods)**

### 2. Insert Excel VLOOKUP Function to Produce 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.

**Read More:** **How to Create Excel Table with Row and Column Headers**

### 3. Apply INDEX & MATCH Functions for Creating Lookup Table

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

**Read More:** **How to Create a Table with Existing Data in Excel**

**Similar Readings**

**How to Make 3D Table in Excel (2 Suitable Ways)****Make a Decision Table in Excel (with Easy Steps)****How to Make a School Time Table in Excel (with Easy Steps)****Create a Descriptive Statistics Table in Excel****How to Create a League Table in Excel (4 Easy Methods)**

### 4. Generate Lookup Table Using 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. Utilize XLOOKUP Function to Create Lookup Table

**The XLOOKUP Function **is an excellent choice to create lookup table in **Excel**. Follow the given steps for creating 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 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.

**Read More:** **How to Create Table from Data Model in Excel (With Easy Steps)**

## 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 Create a Table in Excel with Multiple Columns****How to Create a Table Without Data in Excel (2 Easy Methods)****Create a Table in Excel Based on Cell Value (4 Easy Methods)****How to Make a Table in Excel with Lines (with Easy Steps)****How to Make a Table Bigger in Excel (2 Useful Methods)****Excel VBA to Create Table from Range (6 Examples)**