How to Use VLOOKUP Table Array Based on Cell Value in Excel

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.

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.

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.

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.

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.

Similar Readings

• How to Expand Table Array in Excel
• How to Lock 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.

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.

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.

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