Use of Google Sheets INDEX MATCH in Multiple Columns

In Google Sheets, the INDEX MATCH function is the combination of two basic functions: INDEX and MATCH function. Though separately these two functions act like basic ones, together they are more powerful and effective. They can even be used as an alternative to the VLOOKUP function. In this article, we will learn step by step the use of Google Sheets INDEX MATCH in multiple columns.

overview of index match function multiple columns in google sheets


A Sample of Practice Spreadsheet

You can download spreadsheets from here and practice.


Steps to Use INDEX MATCH with Multiple Columns in Google Sheets

When you use the INDEX MATCH function, you have to start with the INDEX function first. The MATCH function will place the argument within the INDEX function. To apply this function for multiple columns you can apply the following syntax:

=INDEX(reference,MATCH(1,(criteria1)*(criteria2)*(criteria3),0))

Here, reference represents the range from where we will get the result, MATCH provides the position of the search key, 1 is used as a fixed search key, criteria1, criteria2,criteria3…. is the condition to match, 0 is set to return the exact value.


Step 1: Create a Dataset

To apply the INDEX MATCH formula for multiple columns you have to develop a dataset that contains some interrelated columns. Here, we develop a dataset that represents some respondent information. The dataset contains three columns: Country, Name and Age.

dataset for index match function multiple columns in google sheets

  • We also create a table containing the same column name to show the final result.

create new table for index match function in google sheets

Read More: [Fixed!] INDEX MATCH Is Not Working in Google Sheets (5 Fixes)


Step 2: Insert Search Keys

For inserting the search key first, you can create a dropdown list which will help you to find your values easily. As it is an optional method, you can directly insert your search key in the newly developed table. Here, we create a dropdown list. So, for inserting the search key,

  • First, select cell B15 where we want to input the search key. Then go to Data and select the Data validation option.

insert dropdown list in google sheets

  • After that, select Dropdown (from a range), then specify the range B5:B11. Finally, press OK to add the dropdown in the cell.

insert dropdown list in google sheets

  • In the same way, insert a dropdown list for other columns.

add search key for index match function in google sheets

  • When you click on the dropdown symbol you can find all the values of the specific column. You can now insert any of them as a search key.

add search key in index match function

insert search keys in google aheets

  • You can also insert them directly from the dataset.

directly insert search key in google sheets

Read More: How to Create Dependent Drop Down List in Google Sheets


Similar Readings


Step 3: Apply INDEX MATCH Function

After selecting search keys for the function now apply the INDEX MATCH function for multiple columns. Here, we insert search keys ‘USA’ and ‘Max’. Now, to look up the age of these search keys,

  • First, select cell D15 to apply the INDEX MATCH formula.

select cell to insert formula

  • At the beginning insert the INDEX function.

apply index function

  • As reference insert the range D5:D11 which represents the Age column of the source dataset.

add reference column in the function

  • After that insert the MATCH function.

apply the match function

  • Now, add 1 as a fixed search key.

insert fixed search key for index match function

  • Then input the criteria or conditions for multiple columns. Here we add B5:B11=B15 and C5:C11=C15. Connect these statements with multiple symbol “*”.

input the criteria in the function

  • In the end, input 0 to get the exact value for the formula.

set zero to get exact result for index match function in google sheets

Read More: INDEX-MATCH with Multiple Criteria in Google Sheets (Easy Guide)


Step 4: Final Output

  • To get the final output press ENTER and you will find the desired value in your selected cell.
=INDEX(D5:D11,MATCH(B5:B11=B15)*(C5:C11=C15),0))

outcome of index match function for multiple columns in google sheets

In this way, you can get any value from any dataset whether it is large or small in size.


Things to Remember

  • You have to apply the INDEX function first.
  • Insert the range carefully.
  • Always use “*” while inserting criteria.

Conclusion

We believe that after completing the article, you have built a clear knowledge of Google Sheets INDEX MATCH multiple columns. To explore more about Google Sheets, you can visit the OfficeWheel website.


Related Articles

Annyca Tabassum

Annyca Tabassum

Hello, I'm Annyca Tabassum. I currently work for the SOFTEKO as a technical writer and content developer. I have completed my graduation and post-graduation from SUST.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo