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 you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor 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.

#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.”

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.

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.

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.

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

Things to Remember

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 moreVLOOKUP ErrorsVlookup 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 moreVLookup 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