LOOKUP Table in Excel | Top 3 Methods to Create Lookup Table in Excel

Lookup tables in excel are a named tables which are used with vlookup function to find any data, when we have a large amount of data and we do not know where to look we can select the table and give it a name and while using the vlookup function instead of giving the reference we can type the name of the table as a reference to look up the value, such table is known as lookup table in excel.

Lookup Table in Excel – Table of Contents

Create a Lookup Table Using VLOOKUP Function
Use LOOKUP Function to Create a LOOKUP Table in Excel
Use INDEX + MATCH Function to Create a LOOKUP Table in Excel

How to Create a Lookup Table in Excel?

Lookup functions are lifesavers in excel. Based on the available value or lookup value we can fetch the other data associated with it in the different data table. In excel VLOOKUP is the most commonly used lookup function.

In this article, we will discuss some of the important lookup function in excel and also how to create a lookup table in excel. Important lookup functions are VLOOKUP & HLOOKUP, V stands for Vertical Lookup and H stands for Horizontal Lookup. We have the function called LOOKUP as well to look for the data in the table.

Using these lookup functions we can fetch the available data’s other information form different worksheet as well as from different workbooks.

You can download this Create LOOKUP Table Excel Template here – Create LOOKUP Table Excel Template

#1 – Create a Lookup Table Using VLOOKUP Function

As I told VLOOKUP is the traditional lookup function all most all the users use regularly. We will show you how to look for values using this lookup function.

Lookup Value is nothing but the available value. Based on this value we are trying to fetch the data from the other table.
Table Array is simply the main table where all the information resides.
Col Index Num is the nothing but from which column of the table array we want the data. We need to mention the column number here.
Range Lookup is nothing but whether you are looking for an exact match or an approximate match. If you are looking for the exact match then FALSE or 0 is the argument, if you are looking for the approximate match then TRUE or 1 is the argument.

Example of VLOOKUP Function: Assume below is the data you have of product sales and their sales amount.

Now in the cell D2 you one product id and using this product id you have to fetch the sales value using VLOOKUP.

Step 1: Apply the VLOOKUP function and open the formula first.

Step 2: First argument is LOOKUP Value. Lookup value is our base or available value. So select the cell D2 as the reference.

Step 3: Next is the table array, this is nothing but our main table where all the data resides. So select the table array as A2 to B11.

Step 4: Now press the F4 function key to make it as an absolute excel reference. It will insert the dollar symbol to the selected cell.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects) 4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

Step 5: Next argument is column index number, from the selected table from which column actually you are looking for the data. In this case, we have selected two columns and we need the data from the 2^nd column, so mention 2 as the argument.

Step 6: Now the final argument is range lookup i.e. type of lookup. Since we are looking at exact match select FALSE or enter zero as the argument.

Step 7: Close the bracket and hit the enter key. We should have the sales value for the product id Prd 5.

Step 8: What if we want the sales data for the product if Prd6. Off course we can directly enter but this is not the right approach to do. Rather we can create the drop-down list and allow the user to select from the drop-down list. Press ALT + A + V + V in the cell D2, this is the shortcut key which is the shortcut key to create data validation in excel.

Step 9: Select LIST from Allow: drop down.

Step 10: In the SOURCE: select the Product ID list from A2 to A11.

Step 11: Click on OK. We have all the list of products in the cell D2 now.

#2 – Use LOOKUP Function to Create a LOOKUP Table in Excel

Instead of VLOOKUP, we can also use the LOOKUP function as an alternative. Let’s look at the formula of the LOOKUP function.

Lookup Value is base value or available value.
Lookup Vector is nothing but lookup value column in the main table.
Result Vector is nothing but requires a column in the main table.

Let’s apply the formula to understand the logic of the LOOKUP function.

Step 1: Open lookup function now.

Step 2: Lookup value is Product Id so select D2 cell.

Step 3: Lookup vector is nothing but Product Id column in the main table. So select A1 to A11 as the range.

Step 4: Next up results vector, this is nothing but from which column we need the data to be fetched. In this case from B1 to B11, we want the data to be fetched.

Step 5: Close the bracket and hot enter to close the formula. We should have sales value for the selected product id.

Step 6: Change the Product ID to see a different result.

#3 – Use INDEX + MATCH Function

VLOOKUP function can fetch the data from left to right but with the help of INDEX Function and MATCH formula, we can fetch data from anywhere to create a LOOKUP Excel Table

Step 1: Open INDEX formula first.

Step 2: For the first argument select result column in the main table.

Step 3: In order to get the row number we need to apply the MATCH function. Refer below image for MATCH function.

Step 4: Close the bracket and close the formula. We will have results.

Things to Remember

Lookup should be the same as in the main table in excel.
VLOOKUP works from left to right not from right to left.
In LOOKUP function we just need to select the result column, need not mention the column index number, unlike VLOOKUP.

Excel

Financial Functions Excel

Lookup Functions Excel

Excel Charts

Others

Certification Courses

Excel VBA All in One Bundle

Excel Data Analysis Course

VBA Macros Course