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:** **Use Formula in an Excel Table Effectively (With 4 Examples)**

**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**

**How to Refresh All Pivot Tables with VBA (4 Ways)****Pivot Table Field Name Is Not Valid: 9 Causes and Corrections****How to Update Pivot Table Range (5 Suitable Methods)****Excel Table Formatting: Problems and Fixes You Need to Know****Does TABLE Function Exist in Excel?**

**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:** **How to Provide Table Reference in Another Sheet in Excel**

## 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)****Pivot Table Custom Grouping: With 3 Criteria****How to Group Columns in Excel Pivot Table (2 Methods)****Pivot Table is Not Picking up Data in Excel (5 Reasons)****[Fixed!] Pivot Table Field Name Already Exists (2 Quick Methods)****How to Lookup a Table and Return Values in Excel (3 Simple Ways)**