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:

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:

# 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]).**

Parameter | Description |
---|---|

lookup_value | This is the value that the VLOOKUP function searches for in the leftmost column of the table. |

table_array | This 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_num | This 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_lookup | This 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**.

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 3^{rd}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_value | This is the value that the MATCH function searches for in a range of cells. This parameter is required. |

lookup_array | This 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_type | This 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_type | Description |
---|---|

1 | Finds 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. |

0 | Find 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 omitted | Finds 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.**

The formula returns the value 3. This is because the value **Feb** is in the 3^{rd} 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:

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:

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 **TV**s in **June**.

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

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.

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.

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.

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.

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:

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.

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.

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