VLOOKUP with Match

Vlookup formula works only when the table array in the formula does not change, but if there is a new column inserted to 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 match function to actually match the index of the data and return the actual result.

Combine VLOOKUP with Match

The vlookup formula is the most commonly used function that is used to search and return either the same value in the specified column index or the value from a different column index with reference to 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 doesn’t have a dynamic functionality. Especially when you are working on multiple criteria that require you to change the reference column index manually. Thereby this need is fulfilled by using the “MATCH” formula to have a better grip or control to 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 VLOOKUP Function in Excel

Vlookup & Match Formula

Here all the arguments to be entered are mandatory.

  • Lookup_value Here, 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, values in the first column of the table array must be sorted 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

Vlookup & Match Formula 1

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

All the arguments within the syntax are mandatory.

  • Lookup_value – Here, 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 array range for the table is required to 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” Here, 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, the lookup array must be sorted in ascending order.
  • “0-Exact match” – Here, 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 that is greater than or equal to the lookup value. Here the order for the lookup array must be placed 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 putting together.

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

Consider the below data table, which describes the specifications of the given vehicle to be purchased.

Vlookup & Match Formula examples 1

To get the clarity of the combined function for vlookup and match function, let us understand how do 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 $B9 that 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 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 to fix the reference cell even if the formula is being copied to a different cell. In the “Fuel” column, we need to change the column index to “5” as the value from which the data is needed 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 can be seen in the above screenshot, here we are trying to retrieve the column position from the table array. In this case, the column number to be pulled is referred to as cell C8 that is 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 positions of column “Fuel.”

Vlookup & Match Formula examples 1-8

Now here, the column to 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 – Now, the Match formula will be used 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 given table array. This column position will serve the purpose as an input to the column index argument in the vlookup function. Which, in turn, will help vlookup to identify the value to be returned from the resultant column index number?

Similarly, we have applied vlookup with match formula for the “Fuel” column as well.

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

  • VLOOKUP can be applied to lookup values only in 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 “$”, this will maintain the fixed table array range when applying the lookup formula to other cells, or else the reference cells for table array range 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, or else the function will return the “#N/A” error value.
  • Before applying an approximate match “TRUE” or “1” in the last argument, always remember to sort the table array in ascending order.
  • The match function only returns the position of the value in the vlookup table array and does not return the value.
  • In case of Match Function is unable to 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 has been a guide to VLOOKUP with Match Function. Here we learn how to create a flexible formula using VLOOKUP Match in excel along with a practical example and downloadable excel sheet. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

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