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.

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

Lookup Table in Excel

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Lookup Table in Excel (wallstreetmojo.com)

Using these lookup functions, we can fetch the available data’s other information from different worksheets 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 functionVLOOKUP Is The Traditional Lookup FunctionThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more 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.

VLOOKUP Function Step 1

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

VLOOKUP Function Step 2

Follow this steps:

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

    VLOOKUP Function Step 3

  2. The first argument is the LOOKUP Value. Lookup value is our base or available value. So select the cell D2 as the reference.

    VLOOKUP Function Step 4

  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.

    VLOOKUP Function Step 5

  4. Now press the F4 function key to make it an absolute excel referenceAbsolute Excel ReferenceAbsolute reference in excel is a type of cell reference in which the cells being referred to do not change, as they did in relative reference. By pressing f4, we can create a formula for absolute referencing.read more. It will insert the dollar symbol to the selected cell.

    VLOOKUP Function Step 6

  5. The 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 2nd column, so mention 2 as the argument.

    VLOOKUP Function Step 7

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

    VLOOKUP Function Step 8

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

    VLOOKUP Function Step 9

  8. What if we want the sales data for the product if Prd6. Of course, we can directly enter, but this is not the right approach to do. Rather we can create the drop-down list in excelDrop-down List In ExcelA drop-down list in excel is a pre-defined list of inputs that allows users to select an option.read more 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 excelCreate Data Validation In ExcelThe data validation in excel helps control the kind of input entered by a user in the worksheet.read more.

    VLOOKUP Function Step 10

  9. Select the LIST from Allow: dropdown.

    VLOOKUP Function Step 11

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

    VLOOKUP Function Step 12

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

    VLOOKUP Function Step 13

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

Instead of VLOOKUP, we can also use the LOOKUP function in excelLOOKUP Function In ExcelThe LOOKUP excel function searches a value in a range (single row or single column) and returns a corresponding match from the same position of another range (single row or single column). The corresponding match is a piece of information associated with the value being searched. read more as an alternative. Let’s look at the formula of the LOOKUP function.

LOOKUP Formula
  • Lookup Value is the base value or available value.
  • Lookup Vector is nothing but a 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 the lookup function now.

LOOKUP Table Step 1

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

LOOKUP Table Step 2

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

LOOKUP Table Step 3

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.

LOOKUP Table Step 4

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

LOOKUP Table Step 5

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

Step 6

#3 – Use INDEX + MATCH Function

VLOOKUP function can fetch the data from left to right, but with the help of the INDEX FunctionINDEX FunctionThe INDEX function in Excel helps extract the value of a cell, which is within a specified array (range) and, at the intersection of the stated row and column numbers.read more and MATCH formula in excelMATCH Formula In ExcelThe MATCH function looks for a specific value and returns its relative position in a given range of cells. The output is the first position found for the given value. Being a lookup and reference function, it works for both an exact and approximate match. For example, if the range A11:A15 consists of the numbers 2, 9, 8, 14, 32, the formula “MATCH(8,A11:A15,0)” returns 3. This is because the number 8 is at the third position. read more, we can fetch data from anywhere to create a LOOKUP Excel Table

Step 1: Open the INDEX formula ExcelINDEX Formula ExcelThe INDEX function in Excel helps extract the value of a cell, which is within a specified array (range) and, at the intersection of the stated row and column numbers.read more first.

Use INDEX + MATCH Step 1 (lookup table in excel)

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

Use INDEX + MATCH Step 2 (lookup table in excel)

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

Use INDEX + MATCH Step 3 (lookup table in excel)

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

Use INDEX + MATCH Step 4

Things to Remember

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

Recommended Articles

This has been a guide to LOOKUP Table in Excel. Here we discuss How to Create a LOOKUP Table in Excel using VLOOKUP, Index, and Match formula along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion