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 a Lookup Table.
Method 1 – Applying the LOOKUP Function to Create a Lookup Table in Excel
Let’s find the Price of a Product ID from the dataset.
- Write the Product ID in cell F5.
- Select cell G5 where we want the Price to appear.
- Copy the following formula in that cell:
=LOOKUP(F5,B5:B11,D5:D11)- Press Enter. We will see the Price of the product with the Product IDÂ in the lookup cell.
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
Method 2 – Inserting Excel VLOOKUP Function to Make Lookup Table
- Write the Product ID in Cell F5 whose Price we want to find.
- Select Cell G5 and copy the following formula in the cell:
=VLOOKUP(F5,B5:D11,3,FALSE)- Hit Enter.
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.
Method 3 – Combining INDEX & MATCH Functions for a Lookup Table in Excel
- Write the Product ID in Cell F5 whose Price you want to find.
- Input the following formula in Cell G5:
=INDEX(D5:D11,MATCH(F5,B5:B11,0))- Hit Enter.
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.
Method 4 – Generating Lookup Table with the HLOOKUP Function
Let’s find the fifth product’s Product ID:
- In Cell F5, write the name of the column from where we will pick the desired data.
- In Cell G5 write the formula given below.
=HLOOKUP(F5,B4:D11,5,0)- Press Enter.
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
Method 5 – Using the XLOOKUP Function to Create a Lookup Table
- Write the Product ID in Cell F5 whose Price we want to find.
- Input the following formula in Cell G5:
=XLOOKUP(F5,B5:B11,D5:D11)- Press Enter.
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.
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!
