From a large dataset whenever you want to fetch or look up a particular value then you will need to know how to lookup the table (dataset) in Excel. In this article, I’m going to explain with examples how you can use Excel lookup table.

To make the explanation understandable, I’m going to use a dataset of sales information for a particular region. The dataset contains 3 columns. These are *SalesPerson**, Region, *and

**.**

*Sales***Table of Contents**hide

## Download to Practice

## What is Lookup Table?

From a large dataset when we fetch any value associated with the dataset then we treat that dataset as a **lookup table**. Here, we can use the total range of the dataset, or we can name the range which is ultimately considered as a table.

In short **lookup tables** in Excel is named table which we use with the **LOOKUP**, **VLOOKUP**,** HLOOKUP**, **XLOOKUP**,** INDEX & MATCH** types of lookup functions to find any data associated with the range of dataset or the table.

When we use any table name then instead of giving a reference, we can use the table name to **lookup **any value. That’s why such a type of table is known as a **lookup table**.

## 8 Ways to Lookup a Table in Excel

### 1. Using Excel LOOKUP Array to Lookup a Table

You can use **the LOOKUP ****function** to **lookup table** in Excel. There are two ways to do it depending on your dataset and need.

Here, I’m going to show you the **Array form** of using the **LOOKUP **function.

**Array form: **When you have a table or something like that in Excel you can use the array form because an array is a collection of values in rows and columns that you want to search. It looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the array.

To start with, select any cell to place your resultant value.

➤ I selected the **G4** cell.

⏩ In cell **G4**, type the following formula.

`=LOOKUP(F4,B4:D12)`

Here, in the **LOOKUP **function, I selected the cell **F4** as **lookup_value **and selected the range **B4:D12 **as **array**.

Now, the **LOOKUP **function will look for the corresponding values of the **lookup_value **and will return a value from the same position in the **last row or column** of the array associated with the **lookup_value**.

*You can use array form of LOOKUP when the values that you want to match are in the first row or column of the array.*

*Always remember to place the values in ascending order in an array while using the*

**LOOKUP**function.⏩ Press the **ENTER** key and you will get the **Sales **information of the **lookup** value **Adam Smith**.

**2. Using LOOKUP Vector to Lookup in a Table**

Now, I’m going to show you the use of **vector form **of the **LOOKUP **function to **lookup table** in Excel.

**Vector form** is used to search one row or one column for a value. If you want to specify the range that contains the values that you want to match, then you can use the vector form.

To start with, select any cell to place your resultant value.

➤ I selected the **G4** cell.

⏩ In cell **G4**, type the following formula.

`=LOOKUP(F4,B4:B12,D4:D12)`

Here, in the **LOOKUP **function, I selected the cell **F4** as **lookup_value **and selected the range **B4:B12 **as **lookup_vector **then selected the range **D4:D12** as **result_vector**.

Now, the **LOOKUP **function will look for the corresponding values of the **lookup_value **and will return a value from the **result_vector**.

⏩ Press the **ENTER** key and you will get the **Sales **information of the **lookup** value, **Adam Smith**.

**3. Lookup Excel Table with Range**

If you want, you can **lookup **values from another table or range of datasets.

Here, I’m going to use an external **lookup table** to fetch the value. To demonstrate the procedure, I’m using the dataset given below. In the dataset, I want to look up the corresponding **Grade **depending on the **Sales **value. Where I predefined the **Grade **with the **Sales** range.

To start with, select any cell to place your resultant value.

➤ I selected the **E4** cell.

⏩ In cell **E4**, type the following formula.

`=LOOKUP(D4,$G$3:$H$7)`

Here, in the **LOOKUP **function, I selected the cell **D4** as **lookup_value **and selected the cell range **$G$3:$H$7 **as **lookup_vector **(*I used absolute reference to use the formula later for the rest of the cells*).

Now, the **LOOKUP **function will look for the exact or closest value of the **D4 **cell in the **lookup table**.

⏩ Press the **ENTER** key and you will get the corresponding **Grade **based on the **Sales** value.

Here, the **Grade** is **C **for the value **$4567**. In my **lookup table**, there is no grade for the exact **$4567 **value, yet the **LOOKUP **function assigned a grade because it searches for the nearest value. The **lookup** will start to look for the value **$4567** and till **$5000** and its grade as **C**, that is why it will show **Grade C**.

⏩ Now, you use the **Fill Handle **to **AutoFill **the formula for the rest of the cells.

**4. Lookup Excel Table with Named Range**

Earlier I used the **range of lookup table** now, I’ll show you how you can use **the named lookup table**.

To start with, select the **lookup table **range to name the table.

➤ I named the **lookup table** range **Ratings**.

Now, select any cell to place your resultant value.

➤ I selected the **E4** cell.

⏩ In cell **E4**, type the following formula.

`=LOOKUP(D4,Ratings)`

** **

Here, in the **LOOKUP **function, I selected the cell **D4** as **lookup_value **and used **lookup table** name **Ratings **as **lookup_vector**.

Now, the **LOOKUP **function will look for the exact or closest value of the **D4 **cell in the **lookup table**.

⏩ Press the **ENTER** key and you will get the corresponding **Grade **based on the **Sales** value.

⏩ Now, you use the **Fill Handle **to **AutoFill **the formula for the rest of the cells.

**5. Using VLOOKUP to Lookup Table**

By using **the VLOOKUP ****function** you can fetch values from the **lookup** **table**. The **VLOOKUP** function is used to lookup data in a table organized vertically.

To start with, select any cell to place your resultant value.

➤ I selected the **G4** cell.

⏩ In cell **G4**, type the following formula.

`=VLOOKUP(F4,B4:D12,3,FALSE)`

Here, in the **VLOOKUP** function, I selected the **F4 **cell as **lookup_value**, **B4:D12 **as **table_array**,** 3 **as **col_index_num**,** FALSE** as **range_lookup **where (**FALSE **means **Exact Match)**.

Now, the **VLOOKUP** function will return the **Sales **of **F4** cell from the table range as it is the **3rd **column.

⏩ Press the **ENTER** key and you will get the **sales **information for the **SalesPerson “Rachel Ross”**.

**6. Using HLOOKUP to Lookup Table**

You can use **the HLOOKUP**** function** when you want to find values located in a row across the top of a** lookup table** of data, also you want to look down a specified number of rows.

The **HLOOKUP **function searches for a value in the top row of a table or an array of values then it returns a value in the same column from a row you specify in the table or array.

To demonstrate to you the procedure, I’m going to use the dataset given below.

To start with, select any cell to place your resultant value.

➤ I selected the **G4** cell.

⏩ In cell **G4**, type the following formula.

`=HLOOKUP("Sales",B3:D12,3,FALSE)`

You also can use the following formula both will give you the same result.

`=HLOOKUP(F4,B3:D12,3,FALSE)`

Here, in the **HLOOKUP** function, I used **“Sales” **as **lookup_value**, **B4:D12 **as **table_array**,** 3 **as **row_index_num**,** FALSE** as **range_lookup **where **FALSE **means **Exact Match**.

Now, the **HLOOKUP** function will return the **Sales **of **row 3**.

⏩ Press the **ENTER** key and you will get the **sales **information of **3rd Row**.

Instead of typing the first-row value you also can use the cell reference.

To start with, select any cell to place your resultant value.

➤ I selected the **G5** cell.

⏩ In cell **G5**, type the following formula.

`=HLOOKUP(F5,B3:D12,3,FALSE)`

You also can use the following formula both will give you the same result.

`=HLOOKUP(“Region”,B3:D12,3,FALSE)`

Here, in the **HLOOKUP** function, I selected the **F5** cell as **lookup_value**, **B4:D12 **as **table_array**,** 3 **as **row_index_num**.

Now, the **HLOOKUP** function will return the **Region **of **row 3 **(according to the dataset, not the sheet).

⏩ Press the **ENTER** key and you will get the **Region **information of **3rd Row**.

**7. Using XLOOKUP to Lookup Table**

**The XLOOKUP**** function** is the improved version to lookup value from a **lookup table**. This function works in any direction. This function is available on *Microsoft 365*

The **XLOOKUP** function searches a range or an array and then returns the item associated to the first match, it finds the **lookup value** regardless of which side the return column is on. It can return an array with multiple items. If no match exists, then **XLOOKUP** can return the closest (approximate) match.

To begin with, select any cell to place your resultant value.

➤ I selected the **G4** cell.

⏩ In cell **G4**, type the following formula.

`=XLOOKUP(F4,B4:B12,D4:D12,"Not Found",0)`

Here, in the **XLOOKUP **function, I selected the **F4 **cell as **lookup_value**, **B4:B12** as **lookup_array**,** D4:D12** as **return_array**,** “Not Found” **as **if_not_found**,** 0** as **match_mode**.

Now, the **XLOOKUP** function will return the associated result of the **lookup_value** from the **return_array**.

⏩ Press the **ENTER** key and you will get the **Sales **information for the **SalesPerson** **“Kristin Moran”**.

### 8. Using INDEX-MATCH Formula to Lookup Table

If you want to fetch data from anywhere to create a **lookup table** you can use** the INDEX**** function** and** the MATCH function**.

To begin with, select any cell to place your resultant value.

➤ I selected the **G4** cell.

⏩ In cell **G4**, type the following formula.

`=INDEX(D4:D12,MATCH(F4,B4:B12,0))`

Here, in the **INDEX **function, I selected the cell range **D4:D12** as an **array**,** MATCH(F4,B4:B12,0)** as **row_num**.

Next, in the **MATCH **function, I selected the **F4 **cell as **lookup_value**, **B4:B12 **as **table_array**,** 0** as **match_type **where (**0 **means **Exact Match)**.

**Formula Breakdown**

**➦ MATCH(F4,B4:B12,0)—>** The **MATCH** function searches for a specified item in a range of cells, and it will return the relative position of **lookup_value** (item) in the range.** Output: **2

**➦ INDEX(D4:D12,MATCH(F4,B4:B12,0))—> **returns the value of position **2**.** Output: **3452

⏩ Press the **ENTER** key and you will get the **Sales **information for the **SalesPerson** **“Jim Carry”**.

## Things to Remember

🔺 Always remember to place the values in ascending order in an array while using the **LOOKUP **function. Otherwise, it will show **a #N/A error.**

🔺 The lookup should be the same as in the main table in excel.

## Practice section

I’ve provided a practice sheet in the workbook to practice these explained examples.

**Conclusion**

In this article, I have explained 8 ways to use the Excel lookup table. I also tried to cover, when and why the errors may come frequently while using different lookup functions. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.