## What Is Lookup Table In Excel?

A lookup table in Excel is a table that contains a lookup value and its return value. And we can create an Excel lookup table using the inbuilt functions

VLOOKUP,LOOKUP, andINDEX-MATCH.Users can utilize an Excel lookup table when the requirement is to locate the data associated with a value in a massive dataset.

For example, the first table in the following image lists students and their overall percentages and grades.

The task is to look up a student’s name in the source dataset and update their grade.

Then, in such a scenario, we can **use a lookup table in Excel **based on the Excel **LOOKUP** function to achieve the required output.

In the above **lookup table in Excel example**, we shall create the required lookup table in the cell range F1:G2.

While the lookup value is in cell F2, we use the Excel **LOOKUP **function in cell G2 to search for the specified lookup value in the lookup vector range A2:A11. And then, the function returns the value from the same row of the result vector range, C2:C11, as the lookup value.

Furthermore, we can change the lookup value in cell F2. And the **LOOKUP()** in cell G2 will automatically return the updated output in the lookup table.

##### Table of contents

###### Key Takeaways

- The lookup table in Excel is a cell range containing a lookup value and a return value based on the specified dataset.
- Users can use the Excel lookup table to check for a value in a large dataset in the same or different worksheet or workbook to obtain the corresponding data.
- We can use the inbuilt functions
**VLOOKUP**,**LOOKUP**, and**INDEX-MATCH**, to define an Excel lookup table. - We can use the abovementioned functions with other inbuilt functions, such as
**IF**, to create lookup tables for more practical results.

### How To Create A Lookup Table In Excel?

We can create or **insert lookup table in Excel **using the following methods:

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

Let us see the steps with a **lookup table in Excel** **example **for each method.

#### #1 – Create A Lookup Table Using VLOOKUP Function

The steps to create an Excel lookup table using the **VLOOKUP()** are as follows:

- Choose a cell range to build a lookup table.
- Update the lookup table column headings.
- Enter the lookup value in the second cell of the first column.
- Choose the second cell in the second column as the target cell to display the output.
- Type
**=VLOOKUP(**in the target cell. [Alternatively, type**=V**or**=VL**and choose the**VLOOKUP**function from the Excel suggestions.] - Enter the Excel
**VLOOKUP**function argument values, separated by commas. And then close the bracket.

The **VLOOKUP() **syntax is as follows:

Where,

**lookup_value**: The value we aim to look up.**table_array**: The range in which the function must search the lookup value and the corresponding return value.**col_index_num**: The number of the column in the**table_array**range containing the required output, the return value.**range_lookup**: The logical value indicating whether the function must find an approximate or exact match as the return value.

While the first three arguments are mandatory, the fourth is optional.

7. Press **Enter** to view the required output in the lookup table.

##### Basic Example

The table below shows a firm’s invoice data.

The task is to **insert lookup table in Excel **cell range G1:H2 to look up the invoice number in the source dataset to determine the invoice status.

Then, here is how to use the **VLOOKUP()** to build a lookup table in the target cell range.

**Step 1: **Choose cell G1 and enter the first column heading in the lookup table. Next, select cell H1 and enter the second column heading in the lookup table.

And then, choose cell G2 and enter the lookup value.

**Step 2: **Choose cell H2 and enter the **VLOOKUP()** to **use a lookup table in Excel** cell range G1:H2 and display the output.

**=VLOOKUP(**

Next, enter the excel cell reference to the given lookup value, followed by a comma.

**=VLOOKUP(G2,**

And then, enter the Excel absolute reference to the lookup range, A2:D11, followed by a comma.

**=VLOOKUP(G2,$A$2:$D$11,**

Next, enter the column number containing the return value, **4**, followed by a comma.

**=VLOOKUP(G2,$A$2:$D$11,4,**

And then, Excel shows the logical values of the last argument, **TRUE** and **FALSE**.

We shall double-click **FALSE** to choose it, as we want an exact match to obtain the required return value.

**=VLOOKUP(G2,$A$2:$D$11,4,FALSE**

Finally, enter the closing bracket.

**=VLOOKUP(G2,$A$2:$D$11,4,FALSE)**

**Step 3: **Press **Enter** to view the **VLOOKUP()** output in cell H2.

Next, we can change the lookup value in cell G2 to **edit lookup table in Excel **cell range G1:H2.

However, a more sophisticated option is to create a drop-down list in cell G2 to enable the user to choose the lookup value from the list rather than changing the value manually.

And for that, we can use the **Excel Data Validation** feature from the **Data **tab.

**Step 4: **Select cell G2 and **Data **tab – **Data Validation **function – **Data Validation **option.

The **Data Validation **window will open.

**Step 2: **Click the **Allow** field drop-down button to set the field as **List**.

Next, update the first column data range of the lookup range where the **VLOOKUP() **must search for the lookup value in the **Source** field.

And click **OK** in the **Data Validation** window to close it and view the drop-down button in cell G2.

Now, we can **edit lookup table in Excel** by changing the lookup value in cell G2 using its drop-down button and list. Accordingly, the **VLOOKUP()** in cell H2 will show the updated return value.

#### #2 – Use LOOKUP Function To Create A LOOKUP Table In Excel

The steps to use **LOOKUP()** to create an Excel lookup table are as follows:

- Select a cell range to display a lookup table.
- Enter the lookup table column headings.
- Update the lookup value in the second cell of the first column of the lookup table.
- Choose the second cell in the second column as the target cell to display the output.
- Type
**=LOOKUP(**in the target cell. [ Alternatively, type**=LO**and choose**LOOKUP**from the Excel suggestions.] - Enter the
**LOOKUP()**argument values, separated by commas. And then close the bracket.

The **LOOKUP() **syntax is as follows:

Where,

**lookup_value**: The value we must look up in the**lookup_vector**.**lookup_vector**: The cell range containing a single row or column in which the function must search the lookup value.**result_vector**: A cell range containing a single row or column. And it is the same size as the**lookup_vector**.

While the first two arguments are mandatory, the third is optional.

7. Press **Enter** to obtain the required output in the lookup table.

##### Basic Example

The following table lists US states and their population statistics.

The requirement is to define an Excel lookup table in the cell range F1:G2 to look up a US state name and display its population.

Then, we can create an Excel lookup table in the target cell range using the **LOOKUP()**.

**Step 1: **Choose cell F1 and enter the heading of the first column in the lookup table. Next, select cell G1 and enter the heading of the second column in the lookup table.

And then, choose cell F2 and enter the required lookup value.

**Step 2: **Choose cell G2 and enter the **LOOKUP()**.

**=LOOKUP(**

Next, enter the cell reference to the given lookup value, followed by a comma.

**=LOOKUP(F2,**

And then, enter the **lookup_vector** range, followed by a comma.

**=LOOKUP(F2,A2:A21,**

Next, enter the **result_vector** range, B2:B21.

Finally, close the bracket.

**Step 3: **Press **Enter** to achieve the required data in cell G2 of the lookup table.

The **LOOKUP **function searches for the lookup value in the **lookup_vector **range A2:A21. And then, it returns the value from the same row of the **result_vector** range, B2:B21, as the lookup value.

Furthermore, if we change the lookup value in cell F2, as shown below:

Then, pressing **Enter** will make the **LOOKUP()** in cell G2 automatically update the return value corresponding to the new lookup value.

#### #3 – Use INDEX + MATCH Function

The steps to build an Excel lookup table using the **INDEX-MATCH **function are as follows:

- Choose a cell range to show a lookup table.
- Enter the lookup table column headings.
- Enter the lookup value in the second cell of the lookup table’s first column.
- Choose the second cell in the second column as the target cell to display the output.
- Enter the appropriate
**INDEX-MATCH function**in the target cell. - Press
**Enter**to obtain the required output in the lookup table.

##### Basic Example

The following table lists sales representatives at a firm and the sales they generated in July 2023.

The task is to define an Excel lookup table in the cell range E1:F2 to look for a sales representative and determine their July 2023 sales figures.

Then, we can build the required lookup table in the target cell range using the **INDEX-MATCH** function.

**Step 1: **Choose cells E1 and F1, and update the column headings in the lookup table individually.

And then, select cell E2 and enter the required lookup value.

**Step 2: **First, we must set the correct data format in the target cell in the lookup table. And for that, select cell B2 and choose **Home **– **Format Painter** to copy the specific cell format.

And then, click cell F2 to apply the source cell data format.

**Step 3: **Choose cell F2 and enter the Excel **INDEX** function.

**=INDEX(**

Next, enter the required array range, followed by a comma.

**=INDEX(B2:B11,**

And then, enter the **MATCH **Excel function as the **row_num** argument value of the **INDEX()**.

**=INDEX(B2:B11,MATCH(**

Next, enter the lookup value cell reference, followed by a comma.

**=INDEX(B2:B11,MATCH(E2,**

And then, enter the lookup array range, followed by a comma.

**=INDEX(B2:B11,MATCH(E2,A2:A11,**

Once we enter the comma, Excel will show the logical value options for the **match_type** argument. And as we want an exact match, we shall double-click the second option.

**=INDEX(B2:B11,MATCH(E2,A2:A11,0**

And then, enter to closing brackets to complete the formula.

**=INDEX(B2:B11,MATCH(E2,A2:A11,0))**

**Step 4: **Press **Enter** to obtain the required return value in the target cell F2 of the lookup table.

First, the **MATCH()** returns the position of the specified lookup value in the given range of A2:A11, **5**. And then, the **INDEX()** returns the value in the cell at the intersection of column B range B2:B11 and row 5, which will be the cell B6 value.

### Important Things To Note

- If we use the
**VLOOKUP()**to define a lookup table in Excel. Then, we must ensure the source data is organized so that the function searches for the lookup value in the first column in the lookup range. And the column containing the return value should be on the right of the first column in the lookup range. Otherwise, the function will return an error value. - The
**lookup_vector**values should follow ascending order when using the**LOOKUP()**. Otherwise, the function output might be incorrect.

### Frequently Asked Questions (FAQs)

**1. How to do an Excel lookup table with IF statement?**

We can do an Excel lookup table with **IF** statement using the following steps, explained with an example.

The table below lists products and their order quantity data.

Consider the requirement to build a lookup table in the cell range E1:F2, showing the order status of the specified product based on its order quantity data.

And the order status should be **PROCEED** if the order quantity equals or exceeds 5000. Otherwise, the order status should be **DECLINE**.

Then, the steps are as follows:**Step 1: **Choose cells E1 and F1, and update the lookup table column headings individually.

Next, choose cell E2 and enter the required lookup value.**Step 2: **Choose cell F2, enter the **IF **statement, and press **Enter**.**=IF(VLOOKUP(E2,A2:B11,2,0)>=5000,”PROCEED”,”DECLINE”)**

First, the **VLOOKUP()** searches the lookup value in the specified range. And then, it returns the value in the same row of the cited column as the lookup value. Next, the **IF()** checks if the **VLOOKUP()** output equals or exceeds 5000.

In this case, the function returns the FALSE value as the **IF** condition does not hold.

Furthermore, we can change the lookup value in cell E2. And the **IF **statement in cell F2 of the lookup table will automatically update the corresponding output.

**2. How to name a lookup table in Excel?**

We can name a lookup table in Excel using the following steps:

1. Choose the lookup table range.

2. Select **Formulas** – **Define Name** to open the **New Name **window.

3. Update the required name in the **Name **field in the **New Name **window.

4. Click **OK** in the **New Name **window to close it and complete the lookup table naming action.

Alternatively, we can choose the lookup table range and enter the required name in the Name Box. The Name Box is on the left of the Insert Function box above the workspace. And then, press **Enter **to complete the lookup table naming action.

**3. Is Excel lookup table from another workbook possible?**

Excel lookup table from another workbook is possible. And the steps are as follows:

1. Open both workbooks. One workbook will be the active workbook, where we would have the lookup value and must display its corresponding return value. And the other one will contain the lookup range.

2. Choose a target cell in the active workbook and enter the **VLOOKUP()**

First, enter ‘**=**’, the function name, and an opening bracket.

And then, enter the lookup value or the cell reference to it, followed by a comma.

Next, go to the other workbook. Hover the mouse on the first cell of the first column in the lookup range. And with the mouse’s left key pressed, drag the mouse across the cell range to choose the required lookup range. Excel will show the chosen cell range, with the file name and sheet name in single quotes and an Exclamation Mark preceding it.

And then, enter a comma.

Next, enter the column number, followed by a comma. And then, enter the logical value indicating the required match type.

Finally, close the bracket.

3. Press **Enter** to execute the **VLOOKUP()**. The function will get executed in the target cell in the active workbook.

### Download Template

This article must be helpful to understand the **Lookup Table In Excel**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to What Is Lookup Table In Excel. We learn to create it using VLOOKUP, LOOKUP, and INDEX plus MATCH functions with examples. You may learn more from the following articles –

## Leave a Reply