How to Use VLOOKUP and MATCH Functions to Perform a Two-Way Lookup

In this blog post, we will look at how to use the VLOOKUP and MATCH functions to perform a two-way lookup, sometimes referred to as a matrix lookup. This blog includes a quick refresher on the VLOOKUP and MATCH functions, but I’m going to assume that you are aware of both these functions.

If you are not familiar with these functions, please see my VLOOKUP post here, and this post on the MATCH function here.

If you want to work along with this blog post, click the download button below to get the example spreadsheet used in this blog post.

What is a Two-Way LOOKUP?

You may be familiar with the VLOOKUP, HLOOKUP, and the combination of INDEX and MATCH functions in a formula to carry out a one-way lookup so that you can find data in a worksheet.

VLOOKUP, HLOOKUP, and the combination of INDEX and MATCH functions are extremely useful for carrying out lookups. However, these methods have one thing in common; they are one-way lookups. You can look up a value in either a column or a row, but not both at the same time.

The combination of VLOOKUP and MATCH enables you to look up values in both a row and a column at the same time. Imagine you have a table of monthly sales figures for electronic products. The column headings represent the months of the year, and in column A of each row, you have the electronic product type, like in the image below:

image-1-dataset

By inputting a product type and the month, and then carrying out a two-way lookup, you can obtain the relevant sales figures. See the image below:

Example VLOOKUP and MATCH Two-Way Lookup

 

VLOOKUP Function Refresher

The VLOOKUP function is one of the most commonly asked-about functions. The VLOOKUP function finds the row where a specific value is in the leftmost column within a range of cells. Then, the VLOOKUP function extracts a value from a cell on the same row but in a different column to the right.

VLOOKUP Function Syntax

The syntax of the VLOOKUP function is VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]).

ParameterDescription
lookup_valueThis is the value that the VLOOKUP function searches for in the leftmost column of the table.
table_arrayThis is the range of cells that the VLOOKUP function uses. The first column of the selected range of cells will be the one in which the lookup_value will be looked up.
col_index_numThis is the number of the column to the right of the leftmost column that you want to extract the data from. The numbering starts at 1, being the leftmost column.
range_lookupThis is an optional logical value. The default value is 1 (TRUE). This means that it will search for the closest numerical or alphabetical value to your lookup_value if it can`t find your exact search. The 0 value (FALSE) will specifically search for your lookup_value, and it will provide the #N/A value if it can`t find it.

The goal of the VLOOKUP function is to search the leftmost column (col_index_num value 1) of the table_array for a lookup_value and return the value in the corresponding number of columns to the right, as specified by the col_index_num.

For example, in our dataset of electronic product sales figures. We can return the sales figures for Laptops in March by using the following formula:

=VLOOKUP(C11,$A$1:$G$7,3,0)

We insert the formula above in to cell D3 and input the value Laptops into cell C11.

VLOOKUP Function Refresher

The formula returns the value $54,155, which is the sales value for Laptops in February. Looks like laptops still sell well.

Let’s break this formula down to see what is happening:

  • lookup_value – the lookup_value parameter is a reference to cell C11 where we input the product we want the sales figures for, in this instance, Laptops.
  • lookup_table – the lookup_table parameter is the range of cells A1:G7 that contains all the sales figures, the column heading, and lookup values. We use absolute cell references in this example, that is why there is a dollar ($) sign in front of the row and column references.
  • col_index_num – the col_index_num parameters is the value 3. This is because we want to return the sales for February, which is the 3rd column in our lookup_table.
  • range_lookupș the range_lookup parameter is the value 0 because we only want to return an exact match to the lookup_value.

MATCH Function Refresher

The MATCH function doesn’t return a value from a range of cells; it returns the relative row or column number of a lookup value in the range of cells.

MATCH Function Syntax

The syntax of the MATCH function is MATCH(lookup_value, lookup_array, [match_type]).

Parameter Description
look_valueThis is the value that the MATCH function searches for in a range of cells. This parameter is required.
lookup_arrayThis is the range of cells that the MATCH function searches for the lookup_value in . The range of cells can be a single row, a column of multiple rows, or a single column. This parameter is required.
match_typeThis parameter is a number, either -1, 0, or 1. The number is used to specify the match type. This parameter is optional; if it is omitted, the value defaults to 1.

The following table describes the match_type value options in detail.

match_typeDescription
1Finds the smallest value that is greater than or equal to the lookup_value . For this to work correctly, the values in the lookup_array must be arranged in descending order: Z, ….C, B, A, …..4, 3, 2, 1 etc.
0Find the first value in the lookup_array that exactly matches the lookup_value . The values in the lookup_array don't need to be sorted.
or omittedFinds the largest value that is less than or equal to the lookup_value . For this to work correctly, the values in the lookup_array must be arranged in ascending order: 1, 2, 3, 4, …., A, B, C,….Z etc.

In this MATCH example, we are going to return the relative column number of the month of February in our range of cells. To do this, we use the following formula.

=MATCH(C11,$A$1:$G$1,0)

We insert the formula in cell D11 and input the value Feb into cell C11.

MATCH Function Refresher

The formula returns the value 3. This is because the value Feb is in the 3rd column across in our range of cells.

Okay, let’s break the formula down.

  • lookup_value – the lookup_value parameter is again a reference to cell C11 where we input the month we are interested in. In this instance, the value Feb.
  • lookup_array – the lookup_array parameter is the range of cells A1:G1 that contain the months of the year headings. Strictly speaking, for this example, we don’t need to include cell A1 as it doesn’t contain a month value. The reason for its inclusion will become clear when we combine the VLOOKUP and MATCH functions together.
  • match_type – the match_type parameter is 0. We are only interested in an exact match, so we use match_type 0.

One thing to note, if we had more than one column with the lookup_value of Feb, the formula would have returned the column position of the first one found moving from left to right.

To learn more about the VLOOKUP function, checkout my blog post, by clicking here.

Putting VLOOKUP and MATCH Together

Now that we’ve had a look at these functions individually, let’s put them together. One of the problems of the VLOOKUP function is that if we want to change the column that we return the value from, we have to change our formula. For instance, in our example, if we want to return the sales figures for April, we need to change the col_index_num_parameter to 5, as shown in the following image:

Manually updating col_index_num parameter

It works, but not very dynamic!

This is why we combine the VLOOKUP and MATCH functions. We are going to use the MATCH function to return the col_index_parameter value of the VLOOKUP function.

Going back to our example, this time, we are going to input two values. The product into cell C11 and the month in cell D11. To achieve this, we use the following formula:

=VLOOKUP(C11,$A$1:$G$7,MATCH(D11,$A$1:$G$1,0),0)

We insert the formula into cell E11, the value Laptops into cell C11, and value Feb into cell D11, as shown below:

VLOOKUP and MATCH Formula

The formula returns the value $54,155, the same as the value in the VLOOKUP function only example. So what you might be thinking? Well, if we now type TV in cell C11 and the value June in cell D11, we get the sales figures for TVs in June.

VLOOKUP and MATCH Formula Example 2

The formula returns the value $35,143. And there was no need to change any formulas.

So what is happening in this formula?

Remember that the VLOOKUP function returns a value from the column identified by the col_index_num parameter. The lookup_array parameter of the MATCH function needs to span the same columns as the table_array parameter of the VLOOKUP function. In our example, this is columns A to G.

VLOOKUP and MATCH Function Arrays Spanning the Same Rows

In our earlier MATCH example, the lookup_array parameter started in cell A1, even though this cell didn’t contain a month value. This is because it is the first column in the table_array parameter of the VLOOKUP function, or col_index_num 1.

So, when you type a different month into cell D11, a different col_index_num value is provided for the VLOOKUP function.

If you’ve downloaded the example spreadsheet to go with this post, try some different combinations yourself.

Named Ranges

I say this in many of my posts, but, where possible, I recommend using named ranges. Let’s have a quick look at our example using named ranges.

First, let’s create 4 named ranges.

The first one is going to be the Product we are looking for. To create the named range Product, select cell C11; click in the Name Box, type Product, and press enter.

Product Named Range

Our second one is going to be the Month we are looking for. To create the named range Month, select cell D11; click in the Name Box, type Month, and press enter.

Month Named Range

The third one is going to be our entire table, and we call this Sales Figures. To create the named range SaleFigures, select the range of cells A1:G7; click in the Name Box, type SaleFigures, and press enter.

Sales Figures Named Range

Finally, out fourth one is going to be our Months, and we call this Months. To create the named range Months, select the range of cells A1:G1; click in the Name Box, type Months, and press enter.

Months Named Range

Okay, now that all our named ranges have been created, we add the named ranges to our formula:

=VLOOKUP(Product,SalesFigures,MATCH(Month,Months,0),0)

Like before, we insert the formula into cell E11, the value Laptops into cell C11, and the value Feb into cell D11, as shown below:

VLOOKUP and MATCH Formula Using NAmed Ranges

Like before, the formula returns the value $54,155. However, the formula is more readable. Plus, if you add additional rows and columns to your table of sales figures, you shouldn’t need to change the formulas; you will only have to change the relevant named ranges, which is very handy if you have several formulas referencing the same named ranges.

For more information on named ranges, check out my post by clicking here.

Common Errors

The MATCH and VLOOKUP functions will return the #N/A error message if the lookup_value for either function doesn’t exist in your range of cells.

For information on how to handle the #N/A error message, check out my post on how to handle Excel error messages by clicking here.

Some Things to Remember

The MATCH and VLOOKUP function don’t differentiate between uppercase and lowercase letters when searching for the lookup_value. For example, to the VLOOKUP and MATCH functions, “today,” “TODAY,” and “Today” are the same.

Limitations and Alternatives

One of the problems with VLOOKUP function is that you can only look up values to the right of the first column in a range of cells. Most of the time, this won’t be a problem. However, imagine you have a table of employee details, and you want to look up an employee’s name (the lookup_value). However, the employee names column isn’t the first column in your table of data, and the value you want to return is in a column to the left of the employee’s name as shown below.

VLOOKUP Limitiations

Remember that the VLOOKUP function can only return a value in a column to the right of the column containing the lookup_value. This is where using a formula with a combination of the functions, INDEX MATCH MATCH comes in. While it is a slightly more complicated formula, it is a more flexible two-way lookup formula than using VLOOKUP MATCH.

You can learn how to use the INDEX MATCH MATCH formula by clicking here to read my blog post.

Wrap-up

That’s it for this post. Hopefully, you’ve learned how to use the VLOOKUP and MATCH functions together to carry out a two-way lookup. The ability to carry out a two-way lookup can be useful when you need the flexibility to return values that intersect different rows and columns but don’t constantly want to change formulas.

That’s it for this post.  If you’d like a copy of the spreadsheet used in this post, please sign up for our newsletter.

For tools and resources to help you learn Excel, check out my resources page.

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit