Lookup Table In Excel

Article byJeevan A Y
Edited bySheeba M
Reviewed byDheeraj Vaidya, CFA, FRM

What Is LOOKUP Table In Excel?

LOOKUP table in Excel is a named table used with the VLOOKUP function to find any data. When we have a large amount of data and do not know where to look, we can select the table and name it. While using the VLOOKUP function, instead of providing the reference, we can type the table’s name as a reference to look up the value known as an Excel Lookup Table.

For example, we can use the LOOKUP() or the VLOOKUP() functions to look for the data in the table, as shown below.

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)

Key Takeaways

  • The LOOKUP Table in Excel helps users find the lookup_value using the LOOKUP function.
  • In Excel, VLOOKUP is the most commonly used LOOKUP function. Another important lookup function is HLOOKUP. Therefore, V stands for vertical lookup in VLOOKUP, and H stands for horizontal lookup in HLOOKUP.
  • The LOOKUP function helps us select the table from left to right, variable range by variable range, whereas in the VLOOKUP function, we can select the entire dataset, which also considers from left to right.
  • For the range_lookup arguments, if we are looking for the exact match, then FALSE or 0 is the input, and if we are looking for the approximate match, then TRUE or 1 is the input.

How To Create A Lookup Table In Excel?

We can fetch the available data and other information from different worksheets and workbooks using these LOOKUP functions, namely,

  1. Create a Lookup Table Using VLOOKUP function.
  2. Use LOOKUP Function to Create a Lookup Table in Excel.
  3. Use INDEX + MATCH Function.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

#1 – Create a Lookup Table using VLOOKUP Function

As we said, 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 most users use regularly.

The syntax of the VLOOKUP function is,

VLOOKUP Formula

The arguments of the VLOOKUP function are,

  • lookup_value is the available value based on which we are trying to fetch the data from the other table.
  • table_array is the main table where all the information resides.
  • col_index_num is the column of the table_array from which we want the data. So, we must mention the column number here.
  • range_lookup helps choose whether we are looking for an exact or an approximate match.
Example of VLOOKUP Function:

Assume below is the data you have of product sales and their sales amount.

VLOOKUP Function Step 1

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

VLOOKUP Function Step 2

The steps to use the VLOOKUP function are as follows:

  1. We must apply the VLOOKUP function and open the formula first.


    VLOOKUP Function Step 3

  2. The first argument is the lookup_value. It is our base or available value. So, we must select cell D2 as the reference.


    VLOOKUP Function Step 4

  3. Next is the table_array. It 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 reference. It will insert the dollar symbol into the selected cell.


    VLOOKUP Function Step 6

  5. The next argument is the column index number from the selected table from which column we are looking for the data. In this case, we have chosen two columns, and we need the data from the second column, so we must mention 2 as the argument.


    VLOOKUP Function Step 7

  6. The final argument is range_lookup, i.e., type of lookup. Since we are looking at an exact match, we must select FALSE or insert zero as the argument.


    VLOOKUP Function Step 8

  7. Close the bracket and press the “Enter” key. We should have the sales value for the product ID Prd5.


    VLOOKUP Function Step 9

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


    VLOOKUP Function Step 10

  9. Select the “LIST” from the “Allow:” drop-down.


    VLOOKUP Function Step 11

  10. In the “SOURCE:” select the Product ID list from A2 to A11, and click “OK”.


    VLOOKUP Function Step 12

    Finally, we have the list of products in cell D2, as shown below.

    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.

The syntax of the LOOKUP function is,

LOOKUP Formula

The arguments of the LOOKUP function are,

  • 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.
Example of LOOKUP Function:

The steps to use the LOOKUP function are,

Step 1: We must open the LOOKUP function now.

LOOKUP Table Step 1

Step 2: The lookup_value is the product ID, so select the D2 cell.

LOOKUP Table Step 2

Step 3: The lookup_vector is nothing but the product ID column in the main table. So, select A1 to A11 as the range.

LOOKUP Table Step 3

Step 4: Next is the results vector. It 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 brought.

LOOKUP Table Step 4

Step 5: Close the bracket, and press the “Enter” key 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

The 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 bring data from anywhere to create a LOOKUP Excel table.

The steps to use the INDEX and the MATCH are,

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: Select the result column in the main table for the first argument.

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

Step 3: To get the row number, we need to apply the MATCH function. Refer to the 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, as shown below.

Use INDEX + MATCH Step 4

Important Things To Note

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

Frequently Asked Questions (FAQs)

1. Where is the VLOOKUP function found in Excel?

The VLOOKUP function is found as follows:

First, choose an empty cell → select the “Formulas” tab → go to the “Function Library” group → click the “Lookup & Reference” option drop-down → select the “VLOOKUP” function, as shown below.

LOOKUP Table in Excel - FAQ 1

Similarly, we can insert the LOOKUP function, as we can see the function available above the VLOOKUP function.

2. Where is the HLOOKUP function found in Excel?

The HLOOKUP function is found as follows:

First, choose an empty cell → select the “Formulas” tab → go to the “Function Library” group → click the “Lookup & Reference” option drop-down → select the “HLOOKUP” function, as shown below.

LOOKUP Table in Excel - FAQ 2

3. Why is the LOOKUP Table in Excel not working?

A few reasons the LOOKUP Table in Excel may not work are,

The table_array is modified, and the Lookup table did not get updated automatically. In such scenarios, we can refresh the table to display the updated data.
The dataset linked to the LOOKUP formulas is deleted. So, there is no data to look up or display.

Download Template

This article must help understand Lookup Table in Excel with its formulas and examples. You can download the template here to use it instantly.

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

This article is a guide to LOOKUP Table in Excel. Here we create LOOKUP Table using VLOOKUP(), Index()+Match(), LOOKUP(), examples, downloadable excel template. You may also learn more about Excel from the following articles: –