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.

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

**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 Expand Table Array in Excel

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

**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:** How to Name a Table Array 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:** How to Edit Table Array in Excel

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

## 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.

**Download Practice Workbook**

## 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 will find my article helpful in solving 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**

**<< Go Back to Table Array in Excel | Excel VLOOKUP Function | Excel Functions | Learn Excel**