VLOOKUP with Match

Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

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.

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.

VLOOKUP with Match

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:

Vlookup & Match Formula

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.

–>> 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

Vlookup & Match Formula 1

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.

You can download this VLookup with Match Excel Template here – VLookup with Match Excel Template

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.

Vlookup & Match Formula examples 1

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.

Vlookup & Match Formula examples 1-2

The output is shown below:

Vlookup & Match Formula examples 1-3

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

Vlookup & Match Formula examples 1-4

The output is shown below:

Vlookup & Match Formula examples 1-5

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.

Vlookup & Match Formula examples 1-6

The output is shown below:

Vlookup & Match Formula examples 1-7

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

Vlookup & Match Formula examples 1-8

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

Vlookup & Match Formula examples 1-9

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

examples 1-10

The output is shown below:

examples 1-11

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.

examples 1-12

The output is shown below:

examples 1-13

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

examples 1-14

Things to Remember

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: –

Reader Interactions

Comments

  1. Rajesh says

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *