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 is a formula where we use the 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, makes it quite effective.
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 are case-insensitive.
- 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)
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.
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.
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