## What Is VLOOKUP With MATCH In Google Sheets?

The **VLOOKUP **with** MATCH **in Google Sheets** **is a **VLOOKUP **formula, with the **MATCH() **supplied as its dynamic** index** argument. The formula helps overcome the limitation of the **VLOOKUP()**, which is it does not work once we insert or remove one or more columns to or from the lookup range.

Users can apply the **VLOOKUP **with** MATCH **formula in Google Sheets to perform a two-way lookup by matching row and column-wise.

For example, the source dataset holds a list of employees, and their designation and employee ID data.

We should update the designation of the employee cited in cell E2 and display the outcome in cell F2, with the evaluation based on the source dataset.

Then, we can utilize the **VLOOKUP()** containing the **MATCH() **in the target cell to fetch the required data, according to **VLOOKUP with MATCH In Google Sheets explained **earlier.

Individually, the **VLOOKUP **and **MATCH** functions in Googles Sheets work the same way as the Excel **VLOOKUP **function and Excel **MATCH** function.

The **MATCH() **searches for an exact match for the phrase “**Designation**” in the range A1:C1, containing the column names of the source dataset. Since the match is found in cell B1, the function returns **2**, which is the relative position of the search value in the search range.

Next, the **VLOOKUP()** looks for an exact match for the value “**Scott Cook**” in the first column of the lookup range A2:C11. Since it locates a match in cell A6, the function returns the value in the cell where row 6 and column 2 (**MATCH()** output) meet, which is the cell B6 value, **Specialist**.

The last arguments in the **MATCH() **and **VLOOKUP()** indicate that the two functions aim to find the exact matches for the corresponding search values.

Furthermore, the formula **VLOOKUP with MATCH in Google Sheets returns** the correct output when the search value in **MATCH()** is the same phrase as what is used in the source dataset column headings. For instance, we might use the term **Designation **in the source dataset and the term **Desig** in the table where we must update the concerned employee’s designation. In this case, the **MATCH()**’ search phrase and the column heading in the source dataset do not match, leading to the formula returning an error value.

##### Table of contents

###### Key Takeaways

- The
**VLOOKUP**with**MATCH**in Google Sheets**MATCH()**to supply a dynamic**index**argument value to the**VLOOKUP**function. It ensures the**VLOOKUP**function returns an error-free output even when we add or remove columns to or from the lookup range. - The
**VLOOKUP**with**MATCH**formula in Google Sheets helps when we must look up a value using the**VLOOKUP()**in a dynamic lookup range. - We can utilize the
**VLOOKUP**with**MATCH**function in Google Sheets as an individual formula. However, implementing the formula with other inbuilt functions, such as**IF**and**IFERROR**,

### Problem With VLOOKUP

The problems with the **VLOOKUP()** are as follows:

- The
**index**argument in the**VLOOKUP()**is static or hard-coded. In other words, it is typically a fixed number. Then, in the case when the**VLOOKUP()**output should be the return value from another column, we will have to update the**index**argument accordingly. - Adding or deleting columns to or from the lookup range makes the
**VLOOKUP()**return an incorrect or error value. The reason is that the position of the column from where the function must fetch the return value changes. And since the**index**argument value is not dynamic, the function will refer to a column different from the intended one.

### VLOOKUP MATCH Formula

The **VLOOKUP-MATCH **formula in Google Sheets syntax is the following:

Where,

**search_key**: The data point to look for in the first column of the search range.**range**: The maximum and minimum bounds of the cell range to search.**MATCH()**: The function output is the**index**argument value. It is the index of the column in the search range holding the return value, with the index being a positive integer. The**MATCH()**arguments are as follows:**column_heading_search_key**: The value we aim to look for in the column headings of the source dataset.**column_headings_range**: The 1-D array of the column headings of the source dataset we aim to search.

Please note that if the supplied range has a height and width of more than 1, the **MATCH()** output will be the **#N/A! **error.

**search_type**: The value indicates the way we aim to search.**1**: It is the default value. The**MATCH()**considers the search range values to be in ascending order, and its output is the largest value below or equal to the**column_heading_search_key**.**0**: The**MATCH()**finds an exact match. In this case, the range need not be necessarily sorted.**-1**: The**MATCH()**considers that the search range values are in descending order, and its output is the smallest value more than or equal to the**column_heading_search_key**.**is_sorted**: The value indicates if the**VLOOKUP()**must find an exact or approximate match.

**FALSE**or**0**: It indicates an exact match search, and it is the recommended value.**TRUE**or**1**: It indicates an approximate match search, and it is the default value if we omit the**is_sorted**argument.

Please note that in the case of an approximate match, we must sort our search key range in ascending order. Otherwise, we may get an incorrect return value.

Furthermore, we must provide all the argument values when **applying VLOOKUP with MATCH in Google Sheets**, except for the last ones in the two functions being optional.

### How To Use VLOOKUP With MATCH In Google Sheets?

The steps to use the **VLOOKUP with MATCH **formula in Google Sheets are as follows:

- Click the cell where we aim to show the result.
- Type
**=VLOOKUP(**in the cell. [Alternatively, type**=V**or**=VL**and click the function name**VLOOKUP**from the suggestions to choose it.] - Enter the first two arguments, separated by a comma. Next, enter a comma and type
**MATCH(**. Otherwise, type**M**or**MA**and click the function name**MATCH**from the suggestions to choose it. Next, update the**MATCH()**argument values, separated by commas, and close the bracket. Finally, enter a comma and the last argument of**VLOOKUP()**to supply the**VLOOKUP With MATCH In Google Sheets arguments**, and close the bracket. - Press
**Enter**to secure the formula output.

### Examples

The following illustrations explain the practical methods of **applying VLOOKUP With MATCH in Google Sheets**.

#### Example #1

The source dataset contains a list of tech companies and their market cap data.

The requirement is to find the market cap value for the specified tech company in cell E1, based on the source dataset, and showcase the result in cell E2.

Then, considering the concept of **VLOOKUP with MATCH in Google Sheets** **explained **previously, we can apply the **VLOOKUP-MATCH()** in the target cell to get the desired outcome.

**Step 1****: **Choose the target cell E2 and enter the **VLOOKUP()**.

*=VLOOKUP(*

Once we enter the function name, we will see it listed as a suggestion. Click the function name to view its arguments list.

Next, enter the first argument value followed by a comma.

*=VLOOKUP(E1,*

Next, enter the second argument value and a comma.

*=VLOOKUP(E1,A2:B11,*

The third argument value is the **MATCH()**. So, on entering the function name, we will see the function listed as a suggestion. Click it to view its arguments list.

*=VLOOKUP(E1,A2:B11,MATCH(*

Enter the **MATCH()**’s arguments, separated by commas and close the bracket, as depicted below.

*=VLOOKUP(E1,A2:B11,MATCH(D2,A1:B1,0)*

Next, enter a comma and the **VLOOKUP()**’s last argument value to supply all the **VLOOKUP with MATCH in Google Sheets arguments**.

*=VLOOKUP(E1,A2:B11,MATCH(D2,A1:B1,0),0*

Finally, close the bracket to complete the expression.

*=VLOOKUP(E1,A2:B11,MATCH(D2,A1:B1,0),0)*

**Step 2****: **Press **Enter** to view the value that **VLOOKUP with MATCH in Google Sheets returns**.

First, the **MATCH()** searches the value **Market Cap ($)** for an exact match in the range A1:B1. It then returns the value’s relative place in the specified range, **2**, as the output.

Next, the **VLOOKUP()** searches the value **Amazon** for an exact match in the first column of the search range A2:B11. It finds the exact match in cell A6. Next, it considers the **MATCH()** output value of **2** as the required column index value. So, the **VLOOKUP()** returns the value in the cell where row 6 and column 2 meet, which is the cell B6 value of **$1.931 T**, as the required output.

#### Example #2

We have a list of item codes and their quarterly inventory level data.

The task is to display the quarterly inventory data for the item code, cited in cell A11, in the order of the quarters specified in the second table. Assume the range B11:E11 is the target cells.

**Step 1****: **Select cell B11, enter the following expression, and press **Enter**.

*=VLOOKUP($A$11,$A$2:$E$7,MATCH(B10,$A$1:$E$1,0),0)*

Next, utilizing the fill handle, execute the formula in the remaining target cells.

We shall see the cell E11 formula logic to know how the formula works.

First, the **MATCH() **looks for an exact match of the value **Q3 – Inventory Level (Cartons)** in the range A1:E1, which it finds in cell D1. Thus, the function returns **4**, which is the search value’s relative position in the search range.

Next, the **VLOOKUP() **looks for an exact match of the value **JVJ_004** in the range A2:E7, which it finds in cell A5. Next, it takes the **MATCH()** output value of **4** as the required column index value. So, the **VLOOKUP()** returns the value in the cell where row 5 and column 4 intersect, which is the cell D5 value **1580**, as the required output.

Please note that we use absolute reference for specific cell references, as they should be the same in all the target cells’ formulas. Just like the absolute reference in Excel, It makes applying the formula in all the remaining target cells easy once we enter the formula in the first target cell.

On the other hand, we could have applied **VLOOKUP()** in the target cells. However, we would have had to enter the formula in each target cell individually, as the **index **argument value would be static.

Since we supply the **MATCH()** as the **index** argument to the **VLOOKUP()**, the argument value is dynamic, helping us copy the formula in the required cells without editing.

#### Example #3

The source dataset shows the Teams A and B employees’ salaries.

The aim is to update the salary of the employee, cited in cell L2, based on the team and month values specified in cells L3:L4. We must fetch the required data according to the source dataset and show the outcome in cell L5.

Furthermore, if the **VLOOKUP()** returns an error value, an error message should be displayed on executing the formula in the target cell.

**Step 1****: **Select cell L5, enter the following formula, and press **Enter**.

*=IFERROR(VLOOKUP(L2,IF(L3=”Team A”,A4:D8,F4:I8),IF(L3=”Team A”,MATCH(L4,A3:D3,0),MATCH(L4,F3:I3,0)),0),”Invalid Input Or Data Not Available.”)*

The **VLOOKUP() **has the **IF()**, that works similar to the Excel **IF **function, in the **range **and **index **argument values. The two **IF** functions check if the specified team is **Team A**. If the condition holds in the first **IF()**, it returns the TRUE value, which is the range A4:D8 in the first dataset. However, since the condition is false, the function returns the FALSE value, which is the range F4:I8 in the second dataset.

On the other hand, assume the condition is true in the second **IF()**. Then, its output is the **MATCH() **that finds an exact match for the **Mar **month to get its relative position in the range A3:D3 in the first dataset. However, in this case, the condition is false. So, the function output is the **MATCH() **that finds an exact match for the **Mar **month to get its relative position in the range F3:I3 in the second dataset, which is **4**.

Thus, based on the two **IF()**s, we get the required **VLOOKUP()**’s **range **and **index **argument values. So, now the **VLOOKUP() **looks for the employee name **Gladys Hubbard** in the first column of the search range F4:I8, which in this case is F7. Next, the **MATCH()** returns the required column index, which is **4**. Thus, the **VLOOKUP()** returns the value in the cell at the intersection of row 7 and column 4 of the second dataset, which is the cell I7 value of **$30,100**.

Finally, the **IFERROR()**, that follows the same logic as the Excel **IFERROR **function, checks if the **VLOOKUP()** output is an error value. Since the **VLOOKUP()** output is not an error value, the **IFERROR()** returns the **VLOOKUP()** return value as the output, **$30,100**.

### Important Things To Note

- The
**VLOOKUP()**and**MATCH()**in the formula**VLOOKUP**with**MATCH**in Google Sheets - The two functions in the
**VLOOKUP-MATCH**formula in Google Sheets return the**#N/A!**error if they fail to identify a match for the specified lookup value. - We can use the Wildcard characters in the two functions in the
**VLOOKUP-MATCH**formula in Google Sheets for returning values based on partial matches.

### Frequently Asked Questions (**FAQs)**

**1. What is the main advantage of using VLOOKUP with Match in Google Sheets?**

The main advantage of using **VLOOKUP** with **MATCH** in Google Sheets is that we can supply the **index **argument value as a dynamic value to the **VLOOKUP()**. For that, we specify the **MATCH() **as the **index **argument value.

Thus, if we add or delete columns from the search range, the **index **argument value adjusts accordingly, leading to the **VLOOKUP() **returning the appropriate error-free return value.

For example, the following source dataset contains invoice numbers, dates, and the order delivery status.

Further, we update the order delivery status for the invoice number, cited in cell E2, based on the source dataset using the **VLOOKUP()** in the target cell F2.

However, assume we insert a new column to show the order quantity data for the invoice numbers between the invoice date and order delivery status data in the source dataset.

In this case, the **VLOOKUP()** output is incorrect since all the inputs to the function remain the same, with the **index** argument value being static. So, it leads to the function output being the return value from the revised column 3.

We can overcome the issue by using the **MATCH()** as the **index **argument value in the **VLOOKUP()**.**Step 1: **Select the target cell G2, enter the **VLOOKUP()** containing the **MATCH()**, and press **Enter**.*=VLOOKUP(F2,A2:D11,MATCH(G1,A1:D1,0),0)*

The **MATCH()** looks for an exact match for the phrase **Order Delivery Status** in the range A1:D1, which is in cell D1. So, the function returns **4** as the specified value’s relative position in the given search range.

Next, the **VLOOKUP()** looks for an exact match of the invoice number, cited in cell F2, in the search range A2:D11, which is in cell A7. So, the function returns the value in the cell at the intersection of row 7 and column 4, which is the cell D7 value, **Pending**.

**2. When to use VLOOKUP with MATCH in Google Sheets?**

We can use **VLOOKUP **with** MATCH **in Google Sheets when we know that we shall be adding or removing columns of data to or from the concerned lookup range. So, the supplied **index **argument value should be dynamic, which the formula ensures.

**3. Does VLOOKUP with MATCH in Google Sheets work the same as that in Excel?**

The **VLOOKUP** with **MATCH** in Google Sheets does work the same as that in Excel.

### Download Template

This article must be helpful to understand **VLOOKUP With MATCH In Google Sheets**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to What Is VLOOKUP with MATCH in Google Sheets. We explain how to use the formula in Googles Sheets with examples & points to remember. You can learn more from the following articles –

## Leave a Reply