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.


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.


Similar Readings


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.


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

Meraz Al Nahian
Meraz Al Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo