**VLOOKUP** is a powerful function in Google Sheets for finding data in large tables. It is widely used by businesses and individuals to simplify data management and analysis tasks. The dynamic **VLOOKUP** is an advanced version of the standard **VLOOKUP**, offering even more versatility and flexibility to users. In this article, we will explore the benefits of using Google Sheets dynamic range **VLOOKUP** and learn how to implement it in your next project.

## A Sample of Practice Spreadsheet

You can copy our spreadsheet that we’ve used to prepare this article.

## Problem with Regular VLOOKUP Function in Google Sheets and Why is Having a Dynamic Range Necessary?

The regular **VLOOKUP** function in Google Sheets has two main limitations: it can only search for values in the first column of the lookup table, and it cannot automatically adjust the lookup range when data is added or deleted from the table.

**Syntax:**

`VLOOKUP(search_key, range, index, [is_sorted])`

- “
**Search key**,” the first argument, defines the value to look for in the first column of the given range. - “
**range**“, the second argument, defines the table or range of cells to search within. - The third argument, “
**index**“, indicates the column number within the range to return the matching value. - The final and fourth argument is “
**is_sorted**” which determines whether the first column of the range is sorted in ascending order. If the first column is sorted, set “**is_sorted**” to**TRUE**, otherwise set it to**FALSE**.

Check the following images for example.

We have a database of expenses made by employees and we want to determine the amount spent by **Amanda**. A simple** VLOOKUP** can easily retrieve this information, but the date of the expenses is not included in the dataset. Adding a **date** column could alter the results of the** VLOOKUP**.

Having a dynamic range with **VLOOKUP** overcomes these limitations, as it allows for searching in any column and automatically adjusts the lookup range. This results in more flexible and efficient data lookups.

As an example, we used the same dataset. But in this instance, including a new date column has no impact on the outcome. Instead, it changed the range for columns **E** to **F**. Observing the formula makes it simple to see the shift.

Therefore, it is clear that** VLOOKUP** dynamic range is quite beneficial in google sheets when used professionally.

## 2 Simple Approaches to Apply Dynamic Range in VLOOKUP in Google Sheets

Dynamic range in **VLOOKUP** is a valuable feature in Google Sheets that helps ensure the accuracy and efficiency of your data analysis. With dynamic range, the **VLOOKUP** formula automatically adjusts to match the size of the data range.

This article will explore two simple approaches to applying dynamic range in **VLOOKUP** in Google Sheets.

### 1. Implementing VLOOKUP Field Values as Array in Same Worksheet

Assume that we have a financial database that showcases the employee expenses incurred during their business trips.

#### I. For Range Field

Consider that we want to determine the type of **expense** and **department** by using the **employee** name as the lookup value and using the dynamic **VLOOKUP** feature.

**Steps:**

- Firstly, we will select cell
**I6**.

- Secondly, we will use the following formula to find the expense type by the employee.

`=VLOOKUP(H6,{B:B,E:E,D:D},2,0)`

- The curly braces
**{}**in the**VLOOKUP**formula allow you to select multiple columns in any order, unlike the normal**VLOOKUP**using parentheses**()**which follows a sequential order. - For example, in the formula, columns
**B**,**E**, and**D**are selected using curly braces in an order that can benefit us. - The difference between using curly braces and parentheses in
**VLOOKUP**can impact the results of the calculation, so it’s important to understand when to use each. - Finally, press
**ENTER**to see the result.

- The formula will return the value in the
**second**column (column index**2**) of the matching row that contains the lookup value in column**E**.

We can also get the department just by changing the column index from **2** to **3**.

`=VLOOKUP(I5,{B:B,E:E,D:D},3,0)`

By adjusting the* index number*, we can specify which column in the matching row to return the value from. For instance, we used the same formula to retrieve the **department** name.

#### II. For Index Field (Getting Multiple Results with VLOOKUP Dynamically)

We will use the same dataset, but this time we will retrieve all the relevant information in one go. For instance, we will search by the **employee** name and retrieve the type of **expense**, the **amount** of the expense, and the name of the **department**.

**Steps:**

- Once again we will select cell
**I6**first. - Now, we will use the following formula.

`=ARRAYFORMULA(VLOOKUP(H6,$B$6:$F$14,{4,5,3},0))`

- Finally, pressing the
**ENTER**key will display all the relevant results.

**Formula Breakdown:**

- The first argument for the
**VLOOKUP**function is “**H6**” as a*search key*. - Second argument for the
**VLOOKUP**function is “**$B$6:$F$14**“, which refers to the data range being searched. - The third argument is “
**{4,5,3}**“, which tells the formula to return the values in columns**4**,**2**, and**5**for the matching row. - The final argument for the
**VLOOKUP**function is “**0**“, which means that an*exact match*must be found in the data range for the formula to return a result. - Finally,
**ARRAYFORMULA**enables the formula to return a result over a range of cells.

The use of **ARRAYFORMULA** in dynamic **VLOOKUP** has the benefit of allowing you to return multiple results from multiple columns at once. This saves time and effort compared to having to use multiple individual **VLOOKUP** formulas.

### 2. Using Functions to Create Dynamic Range from Different Worksheets

We will use two new datasets. One is about supplies and their quantities and costs and another is about toy store products. These data are in a different sheet or spreadsheet. The following methods will help us understand how to access it.

Consider that we will use the product supply data in the Datasheet to illustrate these methods.

#### I. Using INDIRECT Function for VLOOKUP Range

**The INDIRECT function** combined with **VLOOKUP** allows for dynamic referencing of data, making it easy to update **VLOOKUP** formulas as data changes. This saves time and effort by eliminating the need to manually adjust formulas every time the source data changes.

**Syntax:**

`INDIRECT(cell_reference_as_string, [is_A1_notation])`

Assume that we’ll use the **INDIRECT** function to find the **total cost** of products supplied by **Suppliers C**, **E**, and **B**. The data is on a separate sheet called “**Data**” and will be used as a cell reference.

**Steps:**

- At first, we will select cell
**C6**. - Then, we will apply the following formula.

`=VLOOKUP(B8,INDIRECT("'"&B$4&"'!"&"B4:F13"),5)`

**Formula Breakdown:**

- The
**VLOOKUP**function searches for a value (**B8**) in the first column of a specified range table array. - Generally, the
**INDIRECT**function creates a dynamic cell reference to a specific sheet and range, in this case, the “**Data**” sheet and**B4:F13**range. - The
**5**in the**VLOOKUP**formula indicates which column from the specified range will be returned as a result. - The formula returns the value from the fifth column of the specified range where the value in
**B8**is found in the first column.

- Finally, press
**ENTER**to see the**total cost**of the product supplied by**Supplier C**.

- The formula allows us to search for data outside the current sheet. It helps to determine the
**total cost**for Suppliers**E**and**B**with ease.

It is evident that instead of manually copying data from one sheet to another, we can simply use the **INDIRECT** and **VLOOKUP** formulas to reference the data and automatically retrieve it.

#### II. Using FILTER Function

Using the **VLOOKUP** function, we can not only search for data but also filter it by combining it with **the FILTER function**. Let’s take an example where we want to find the **total cost** of **Product Q** supplied by **Supplier C**.

**Syntax:**

`FILTER(range, condition1, [condition2, ...])`

The **FILTER** function allows us to easily find specific products from **Supplier C**, which supplies both **Product Q** and **Product Z**.

**Steps:**

- Firstly, We will select cell
**C6**.

- Secondly, we will use the following formula to filter data.

`=VLOOKUP(C5,FILTER(Data!B:F,Data!C:C=E5),5,0)`

- In the end, press
**ENTER**to see the total cost.

**Formula Breakdown:**

**C5**is the lookup value to be searched for in the data range.**FILTER(Data!B:F,Data!C:C=E5)**returns a filtered data range from the “**Data**” sheet, where the values in**column C**match the value in cell**E5**.**5**is the column number of the data range to return the result.**0**indicates an*exact match*is required for the lookup value.

- The above image shows us that we used the
**FILTER**function along with**VLOOKUP**to filter data and find the**total cost**of**Product Q**supplied by**Supplier C**, which is**$2000**.

The combination of **FILTER** and **VLOOKUP** allows us to quickly and efficiently find specific information from large data sets.

By using this combination, we provided multiple examples such as, we find the** total cost** for **Product Y** supplied by **Supplier B**, and can easily extend the search for other suppliers and products.

#### III. Using MATCH Function for Dynamic Column Number

The use of **the MATCH function** as a column index in the **VLOOKUP** dynamic range offers two benefits: improved accuracy and flexibility in data retrieval.

**Syntax:**

`MATCH(search_key, range, [search_type])`

It allows us to select the exact column we want to retrieve data from, eliminating the need for manual updates to our formulas when the structure of our data changes.

Assume that we are going to find the** cost per unit** for the product supplied by **supplier C**.

**Steps:**

- Once again, we will select cell
**E5**.

- Now, we will use the following formula

`=VLOOKUP(C5,Data!B:F,MATCH(D5,Data!$B$3:$3,0),0)`

- Finally, press
**ENTER**to see the result.

**Formula Breakdown:**

- Firstly, the
**VLOOKUP**formula takes the value in cell**E5**as the lookup value. - Secondly, it references the data range in the “
**Data**” sheet from column**B**to column**F**. - Next, it uses the
**MATCH**function to find the column index where the header value in cell**D5**is found in the range**Data!$B$3:$3**. - Finally, the
**VLOOKUP**formula returns the value in the same row as the lookup value and in the column found by the**MATCH**function, with an*exact match*(**0**) setting.

The output is **$20** which means that the cost per unit for the product supplied by **Supplier C** is** $20**.

By combining the power of **FILTER** and **VLOOKUP**, we are able to effortlessly uncover valuable information from our data. The formula helps us find the** cost per unit** for a specific supplier, like** Supplier E** which is **$18**.

#### IV. Using IMPORTRANGE Function

**The IMPORTRANGE function** in Google Sheets allows us to easily retrieve data from another spreadsheet.

**Syntax:**

`IMPORTRANGE(spreadsheet_url, range_string)`

In this example, we have a dataset of a toy store’s products and we want to find out how many are in **stock** using the **product ID**. The link to the dataset is as follows.

**Steps:**

- First, we will select cell
**E5**. - Second, we’ll input the formula:

`=VLOOKUP(C5,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1elXrfifeRObk2-uBDN7x0rifBsSwmNAHoqyGgBQFuR0","Data!B5:F14"),5,0)`

**C5**is the search key for the**product ID**we want to look up.**IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1elXrfifeRObk2-uBDN7x0rifBsSwmNAHoqyGgBQFuR0″,”Data!B5:F14”)**retrieves the data from another spreadsheet.**5**is the*index number*, representing the column we want to retrieve the data.**0**specifies an*exact match*, rather than an approximate match.- After inputting the formula, press
**ENTER**and then, the cell will show the**stock**count for the specified**product ID**.

And with that, we have successfully used the VLOOKUP and IMPORTRANGE functions to find the stock count of our toy store products.

## Conclusion

The use of dynamic range in the Google Sheets **VLOOKUP** function allows for a flexible and efficient way to search for specific data within a large dataset. It eliminates the need for manual updates of ranges, ensuring accuracy and saving time in the process. The dynamic range can be applied in different ways, such as using the **INDEX** and **MATCH** functions or the **ARRAYFORMULA** function, depending on the specific requirement. Check **OfficeWheel** for more relevant content.