This article will provide you with 4 methods on how to use the **VLOOKUP table array **based on **cell value **in Excel. If we want to extract some particular data or information from the whole dataset, the **VLOOKUP table array **can be a very useful tool in this regard.

To elaborate the methods, we are using a dataset where we stored some data on sports equipment. We have **Product ID**, **Products’ Names**, and **their previous **and **current prices **respectively in the dataset.

**Table of Contents**hide

## Download Practice Workbook

## 5 Ways to Use VLOOKUP Table Array Based on Cell Value in Excel

**1. Using Excel VLOOKUP Function to Create VLOOKUP Table Array Based on Cell Value**

The **VLOOKUP **function is the most common function to use the **VLOOKUP table array**. Suppose we want to see the **Current Prices **of the **Products** which have an **ID number **less than or equal to **3**. The following steps can be helpful for you in this regard.

**Steps:**

- Make
**2**new**columns**for the chosen**Product IDs**and their corresponding**Current Prices**.

- Select cell
**H5**and type the following formula in it.

`=VLOOKUP(G5,$B$5:$E$11,4,FALSE)`

Here, we **look up **the value in **G5 **in the **B5:E11 table_array**. Then, we used **absolute reference** for the **table_array **to avoid **Value not Available Error **(**#N/A**). We want to see the **current price **of the **products **which are in the **4th column**. So we chose **4 **as **col_index_num**. We wanted an **exact match **and so we chose **FALSE **as **[range_lookup]**.

- Hit the
**ENTER**button and you will see the**Current Price**of the**Product**that has the**ID**number**1**in cell**H5**.

- Use the
**Fill Handle**to**AutoFill**the lower cells.

Thus you can see the **Current Prices **of your desired **Products **in a **VLOOKUP table array **by using the Excel **VLOOKUP **function based on **cell value**.

**Read More:** **How to Create a Table Array in Excel (3 Methods)**

**2. Use of Excel INDIRECT Function to Create VLOOKUP Table Array Based on Cell Value**

We can also use the **INDIRECT **function in the **VLOOKUP **function to use the **VLOOKUP table array**. Suppose we want to see the **Current Prices **of the **Products** which have an **ID number less than or equal** to **3**. The following steps can be helpful for you in this regard.

**Steps:**

- Make
**2**new**columns**for the chosen**Product IDs**and their corresponding**Current Prices**.

- Select cell
**H5**and type the following formula in it.

`=VLOOKUP(G5,INDIRECT("B5:E11"),4,FALSE)`

Here, we **look up **the value in **G5 **in the **B5:E11 table_array**. The **INDIRECT **function makes the **range B5:E11 **an **absolute reference** for the **table_array**. We want to see the **current price **of the **products **which are in the **4th column**. So we chose **4 **as **col_index_num**. We wanted an **exact match **and so we chose **FALSE **as **[range_lookup]**.

- Hit the
**ENTER**button and you will see the**Current Price**of the**Product**that has the**ID**number**1**in cell**H5**.

- Use the
**Fill Handle**to**AutoFill**the lower cells.

Thus you can see the **Current Prices **of your desired **Products **in a **VLOOKUP table array **by using Excel **INDIRECT **function in the **VLOOKUP **function based on **cell value**.

**Read More: ****How to Expand Table Array in Excel (5 Simple Ways)**

**3. Utilizing Excel IF Function to Create VLOOKUP Table Array**

We can also use the **IF **function in the **VLOOKUP **function to use the **VLOOKUP table array**. Suppose we want to see the **current prices **of the **products** which have an **ID number **less than or equal to **3**. The following steps can be helpful for you in this regard.

**Steps:**

- Make
**2**new**columns**for the chosen**Product IDs**and their corresponding**Current Prices**.

- Select cell
**H5**and type the following formula in it.

`VLOOKUP(G5,IF(G5<=3,$B$5:$E$11),4,FALSE)`

Here, we **look up **the value in **G5 **in the **B5:E11 table_array**. The **IF **function will keep returning the **range B5:E11 **as **absolute reference** for the **table_array **until the cell value in **column** G exceeds **3**. We want to see the **current price **of the **products **which are in the **4th column**. So we chose **4 **as **col_index_num**. We wanted an **exact match **and so we chose **FALSE **as **[range_lookup]**.

- Hit the
**ENTER**button and you will see the**Current Price**of the**Product**that has the**ID**number**1**in cell**H5**.

- Use the
**Fill Handle**to**AutoFill**the lower cells.

Thus you can see the **Current Prices **of your desired **Products **in a **VLOOKUP table array **by using Excel **IF **function in the **VLOOKUP **function based on **cell value**.

**Read More:** **Excel Table Name: All You Need to Know**

**Similar Readings**

**Refresh All Pivot Tables with VBA (4 Ways)****How to Name a Table Array in Excel (With Easy Steps)****Pivot Table Field Name Is Not Valid: 9 Causes and Corrections****Excel Table Formatting: Problems and Fixes You Need to Know****Use Formula in an Excel Table Effectively (With 4 Examples)**

**4. Creating VLOOKUP Array by Applying Named Range **

We can create a **Named Range** for our dataset and use this reference in the **VLOOKUP **function to use the **VLOOKUP table array**. Suppose we want to see the **Current Prices **of the **products** which have an **ID number **less than or equal to **3**. Let’s discuss the process below.

**Steps:**

- Make
**2**new**columns**for the chosen**Product IDs**and their corresponding**current prices**. - Select the
**range B5:E11**and give it a name. In this case, I chose the name**ProductData**.

- Select cell
**H5**and type the following formula in it.

`=VLOOKUP(G5,ProductData,4,FALSE)`

Here, we **look up **the value in **G5 **in the **ProductData named range **which is the **B5:E11 table_array**. We want to see the **current price **of the **products **which are in the **4th column**. So we chose **4 **as **col_index_num**. We wanted an **exact match **and so we chose **FALSE **as **[range_lookup]**.

- Hit the
**ENTER**button and you will see the**Current Price**of the**Product**that has the**ID**number**1**in cell**H5**.

- Use the
**Fill Handle**to**AutoFill**the lower cells.

Thus you can see the **Current Prices **of your desired **Products **in a **VLOOKUP table array **by using a **named range **based on **cell value**.

**Read More:** **Excel VBA to Create Table from Range (6 Examples)**

**5. Using Table Array of Different Sheets to Apply VLOOKUP**

We can **look up** for a particular value in a different **sheet** using the **VLOOKUP **function to use the **VLOOKUP table array**. Suppose we want to see the **previous prices **of the **products** which have an **ID number **less than or equal to **3**. Let’s discuss the process below.

**Steps:**

- Make
**2**new**columns**for the chosen**Product IDs**and their corresponding**previous prices**in a new**sheet**.

The **lookup array **will come from the **dataset **sheet.

- Select cell
**C5**and type the following formula in it.

`=VLOOKUP(B5,dataset!$B$5:$E$11,3,FALSE)`

Here, we **look up **the value in **B5**, then we go to the **dataset sheet** for the **lookup array **which is the **range B5:E11 **in that **sheet**. We want to see the **previous price **of the **products **which are in the **3rd column**. So we chose **3 **as **col_index_num**. We wanted an **exact match **and so we chose **FALSE **as **[range_lookup]**.

- Hit the
**ENTER**button and you will see the**Previous Price**of the**Product**that has the**ID**number**1**in cell**H5**.

- Use the
**Fill Handle**to**AutoFill**the lower cells.

Thus you can see the **Previous Prices **of your desired **Products **in a **VLOOKUP table array **from a different Excel **sheet **based on **cell value**.

**Read More: ****What Is a Table Array in VLOOKUP? (Explained with Examples)**

## Practice Section

Here I am giving you the dataset that we used to explain these methods so that you can practice these examples on your own.

## Conclusion

The article focuses on some basic methods to use the **VLOOKUP table array **to extract a particular set of data in Excel based on **cell value**. Using a **VLOOKUP table array **is a very easy and useful action in order to spectate particular information in an Excel Dataset. I hope you may find my article helpful to solve your own problems in this regard. If you have any easier methods or ideas or any feedback, please leave them in the comment box. This will help me enrich my upcoming articles.

**Related Articles**

**How to Extend Table in Excel (4 Ways)****Edit Table Array in Excel (2 Suitable Ways)****Pivot Table Custom Grouping: With 3 Criteria****How to Lock Table Array in Excel (2 Effective Ways)****Group Columns in Excel Pivot Table (2 Methods)****How to Find Table Array in Excel (4 Suitable Examples)****Lookup a Table and Return Values in Excel (3 Simple Ways)**