Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create a Lookup Table in Excel (5 Easy Ways)

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.


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.

how to create a lookup table in excel


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.

Apply INDEX & MATCH Functions for Creating Lookup Table

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


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.

Generate Lookup Table Using HLOOKUP Function

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.

Utilize XLOOKUP Function to Create Lookup Table

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

Mehedi Hasan Shimul

Mehedi Hasan Shimul

Hi! I am Mehedi Hasan Shimul. As I am an Engineer solving different problems with the help of Excel amuses me. I write Excel related different problem solving articles here. Hope it will help you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo