Match from Multiple Columns in Google Sheets (2 Ways)

Today, we will look at how to perform a match from multiple columns in Google Sheets. This process is useful if you want to extract values based on multiple criteria/column values.

2 Ways to Match from Multiple Columns in Google Sheets

1. Using INDEX MATCH to Match from Multiple Columns

The first method we will use is perhaps the most powerful in our article today, which is the INDEX-MATCH formula.

To show this method, we have the following worksheet:

worksheet for match multiple columns in google sheets

While this is just a part of a larger database, it contains various types of data. From here, we will be matching the Director name and Genre values from the column to retrieve the name of the Movie.

Our formula:


using index match to match multiple columns in google sheets

As you can see, we are combining the data from cells C10 and C11 as our match criteria from the table. The MATCH function within the formula returns the row index for the INDEX function to help retrieve the correct value.

However, we will be presented with an error if the formula is unable to find a correct match. So as a failsafe, we enclose this formula in the IFERROR function.

=IFERROR(INDEX(B3:B8,MATCH(1,(C3:C8=C10)*(D3:D8=C11),0)),"Not Found")

handling error in the index match formula

For an in-depth breakdown of this method, please have a look at our Use of Google Sheets INDEX MATCH in Multiple Columns article.

2. Using VLOOKUP to Match from Multiple Columns

Next up we have the traditional way to lookup values through matches in Google Sheets, and that is by using the VLOOKUP function.

However, compared to INDEX-MATCH, VLOOKUP has some technical limitations:

The function only looks towards the right. VLOOKUP can only extract values with a column index. Since column indexes start from 1 and move to the right, the function does the same.

Needs a special column for multiple columns or criteria. The function has no field for multiple criteria, thus you have to create a separate column (anywhere to the left of the column that is to be extracted).

It is always better to see through an example, and we will go over the process step-by-step.

For our example, we will try to extract the Release Date of a movie based on its name and genre as the two match criteria.

second worksheet to match multiple columns in google sheets

Step 1: Create a “Helper Column” to concatenate the multiple column values into one. You can use any form of delimiter as long as it matches our search key field in our formula. We have used a hyphen (-) delimiter.

creating the helper column

It is very important to make sure that the helper column is on the left of the column whose values we are going to extract.

Step 2: Open the VLOOKUP function at the target and enter the search key. Our search key is the combination of the two criteria that are located in cell C10 and C11 respectively. Make sure the search key matches the format of the helper column.

setting the search key for the vlookup function

Step 3: Input the range. For us, this includes the Helper Column and Release Date columns.

inputting range for vlookup

Step 4: Since the values we are looking to extract are in the Release Date column, the selected range places it in index 2.

Step 5: We will leave the [is_sorted] field as FALSE. Close parentheses and press ENTER.

Our final formula:


using vlookup to match from multiple columns in google sheets


Read More: Alternative to Use VLOOKUP Function in Google Sheets

Special Case: Match from Different Sets of Data in Google Sheets

In the following worksheet, we have two sets of data for Dorm 1 and Dorm 2. Each of these dorms has its residents’ names and their countries of origin.

worksheet with two separate datasets

For our task, we will try to extract the Names by matching them with the asked Country of origin that is in cell C12.

The data to be extracted are in two separate columns and so are our match data. This means that we are unable to use the traditional approaches that we have just discussed in our previous two methods.

For this reason, we will take advantage of the array formula, FILTER, and we will couple it up with the MATCH function.

Our formula:


using filter and match combination to match multiple columns in google sheets

Note: This is a special case and may not work for all situations.

Final Words

That concludes all the ways you can use to match from multiple columns in Google Sheets. We hope that the methods will come in handy for your spreadsheet tasks.

Feel free to leave any queries or advice you might have for us in the comments section below.

Related Articles

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply