VLOOKUP formula works only when the table array in the formula does not change. Still, if a new column is inserted into the table or a column is deleted, the formula gives an incorrect result or reflects an error. To make the formula error-free in such dynamic situations, we use the MATCH function to match the data’s index and return the actual result.

##### Table of contents

## Combine VLOOKUP with Match

The VLOOKUP formula is the most commonly used function to search and return either the same value in the specified column index or the value from a different column index concerning the matched value from the first column. The major challenge faced while using VLOOKUP is that the column index to be specified is static and does not have dynamic functionality. Especially when you are working on multiple criteria that require you to change the reference column index manually, this need is fulfilled by using the “MATCH” formula to have a better grip or control of the frequently changing column index in the VLOOKUP formula.

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked

For eg:

Source: VLOOKUP with Match (wallstreetmojo.com)

### VLookup and Match Formula

#### #1 – VLOOKUP Formula

The formula of the VLOOKUP function in Excel:

Here, all the arguments to be entered are mandatory.

**lookup_value****–**Here, a reference cell or text with double quotes should be entered to be identified in the column range.**table_array****–**This argument requires the table range to be entered where the lookup_value should be searched and the data to be retrieved resides in the particular column range.**col_index_num****–**In this argument, the column index number or the count of the column from the reference first column needs to be entered from which the corresponding value needs to be pulled from the same position as the value searched in the first column.**[Range_lookup] –**This argument will give two options.**TRUE**– Approximate match:- The argument can either be entered as “TRUE” or numeric “1”, which returns the approximate match corresponding to the reference column or first column. Moreover, we must sort values in the first column of the table array in ascending order.**FALSE**– Exact match:- Here, the argument to be entered can either be “FALSE” or numeric “0”. This option will only return the exact match of the value corresponding to be identified from the position in the first column range. Failure to search the value from the first column would return a #N/A error message.

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tuto**rials)

**–>>** **If you want to learn Excel and VBA professionally**, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from **Basic Excel to Advanced Excel, Macros, Power Query, and VBA.**

#### #2 – Match Formula

The MATCH function returns the cell position of the value entered for the table array.

All the arguments within the syntax are mandatory.

**lookup_value**– The argument entered can be either the cell reference of the value or a text string with double quotes whose cell position is required to be pulled.**lookup_array**– The table array range must be entered whose value or cell content is desired to be identified.**[match type]**– This argument provides three options, as explained below.**“1-Less than”****–**The argument to be entered is numeric “1,” which will return the value that is less than or equal to the lookup value. And also, we must sort the lookup array in ascending order.**“0-Exact match” –**The argument to be entered should be numeric “0”. This option will return the exact position of the matched lookup value. However, the lookup array can be in any order.**“-1-Greater than” –**The argument to be entered should be numeric “-1”. The third option finds the smallest value greater than or equalGreater Than Or EqualThe “greater than or equal to” is a comparison or logical operator that helps compare two data cells of the same data type.read more to the lookup_value. Here, we must place the order for the lookup array in descending order.

#### #3 – VLOOKUP with MATCH Formula

**=VLOOKUP(lookup_value, table_array, MATCH(lookup_value, lookup_array, [match_type]),[range lookup])**

### How to Use VLOOKUP with Match Formula in Excel?

The example below will help understand the functioning of the VLOOKUP and MATCH formula when put together.

Consider the below data tableData TableA data table in excel is a type of what-if analysis tool that allows you to compare variables and see how they impact the result and overall data. It can be found under the data tab in the what-if analysis section.read more, which describes the specifications of the given vehicle to be purchased.

To clarify the combined function for the VLOOKUP and MATCH function, let us understand how the individual formula operates and then arrive at the VLOOKUP MATCH results when put together.

**Step #1 –** Let us apply the VLOOKUP formula at an individual level to arrive at the result.

The output is shown below:

Here, the lookup_value is referred to as $B9, is model “E,” and the lookup_array is given as the range of the data table with absolute value “$” the column_index is referred to as column “4,” which is the count for column “Type,” and the range lookup is given an exact match.

Thus, the following formula is applied for returning the value for column **“Fuel.”**

The output is shown below:

Here, the lookup_value with absolute string “$” applied for lookup value and lookup_array helps fix the reference cell even if the formula is copied to a different cell. For example, in the “Fuel” column, we need to change the column index to “5” as the value from which the data required to be retrieved changes.

**Step #2 – **Now, let us apply the MATCH formula to retrieve the position for the given lookup_value.

The output is shown below:

As the above screenshot shows, we are trying to retrieve the column position from the table array. In this case, the column number to be pulled is called cell C8, column “Type,” and the lookup_range to be searched is given as the range of column headers, and the match type is given an exact match to be as “0”.

Thus, the below table will give the desired result for the positions of column “Fuel.”

Here, the column searched is given to be cell D8, and the desired column_index is returned to be “5”.

**Step #3** – We will use the MATCH formula within the VLOOKUP function to get the value from the identified column position.

The output is shown below:

In the above formula, the MATCH function is put in place of the column_index parameter of the VLOOKUP function. Here, the MATCH function will identify the lookup_value reference cell **“C8”** and return the column number through the table array. This column position will serve as an input to the column index argument in the VLOOKUP function. Which, in turn, will help VLOOKUP identify the value returned from the resultant column index number?

Similarly, we have also applied VLOOKUP with a MATCH formula for the “Fuel” column.

The output is shown below:

We can thereby apply this combination function for other columns “Type” and “Fuel” as well.

### Things to Remember

- We can apply VLOOKUP to lookup values only on its foremost left-hand side. Any values present to be searched on the right side of the data table will return the #N/A error value.
- The range of table_array entered in the second argument should be absolute cell referenceAbsolute Cell ReferenceAbsolute reference in excel is a type of cell reference in which the cells being referred to do not change, as they did in relative reference. By pressing f4, we can create a formula for absolute referencing.read more “$” because it will maintain the fixed table array range when applying the lookup formula to other cells. Otherwise, the table_array, range reference cells will shift to the next cell reference.
- The value entered in the lookup_value should not be smaller than the smallest value in the first column of the table array. Else, the function will return the #N/A error value.
- Before applying an approximate match “TRUE” or “1” in the last argument, we must always remember to sort the table array in ascending order.
- The MATCH function only returns the position of the value in the vlookup table arrayVlookup Table ArrayWhen we use a reference cell or value to search in a group of columns containing data to be matched and retrieve the output in the VLOOKUP table array or vertical lookup, the range we use is called VLOOKUP table array.read more and does not return the value.
- If the MATCH function cannot identify the position of the lookup_value in the table array, then the formula returns #N/A in the error value.
- VLOOKUP and MATCH functions are case insensitive when matching the lookup_value with the matching text value in the table_array.

### Recommended Articles

This article is a guide to VLOOKUP with MATCH Function. Here, we learn how to create a flexible formula using VLOOKUP MATCH in Excel and a practical example and downloadable Excel sheet. You may learn more about Excel from the following articles: –

- Excel VLOOKUP FormulaExcel VLOOKUP FormulaThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more
- VLOOKUP Errors
- Vlookup to the LeftVlookup To The LeftVlookup to the left finds the respective values which are in the left column of the reference cell. Index, and match are such formulas which are combined together or we can use conditional formulas in the lookup function to find values to the left.read more
- VLookup Function with IFVLookup Function With IFIn Excel, vlookup is a reference function, and IF is a conditional statement. Based on the results of the Vlookup function, they locate a value that meets the criteria and also matches the reference value.read more

Rajesh says

Thanks