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

Get FREE Advanced Excel Exercises with Solutions!

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.

vlookup table array based on cell value


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)

vlookup table array based on cell value

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.

vlookup table array based on cell value

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)

vlookup table array based on cell value

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.

vlookup table array based on cell value

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)

vlookup table array based on cell value

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.

vlookup table array based on cell value

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)

vlookup table array based on cell value

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.

vlookup table array based on cell value

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.

vlookup table array based on cell value

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

vlookup table array based on cell value

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

vlookup table array based on cell value


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo