Google Sheets INDEX | Google Sheets MATCH | How to Use INDEX and MATCH together
What is a reason to use the INDEX and MATCH functions together? If you do not wish to hard code the attributes of the INDEX function, then a solution is to nest the MATCH function inside the INDEX. A reason you would not want to hard code the attributes is when a Google Sheets spreadsheet is large.
In a large spreadsheet, it would be difficult to count the row and column number, because it takes effort, time, and may be error-prone. So, it would be a good idea to let the MATCH function do this job.
The match function returns a number. This is exactly what the INDEX function needs for its row and column attributes. Use the MATCH function twice inside the INDEX function, one for the row attribute and another for column.
Let's consider an example. Ref to the simple spreadsheet below:
Country Total Exports
Canada 197.3
Mexico 173.5
Japan 49.6
Say you want to extract the Total Exports data for Japan. The INDEX function for this task is:
=INDEX(reference, [row], [column])
=INDEX(A1:B4,4,2)
Because this is a very small spreadsheet, it's easy to hard code the INDEX function attributes. What if the spreadsheet was large and the data for Japan was in a row and column way down and across the spreadsheet? Further, what if you wanted the data for several other countries by simply typing the name of the country into a cell? In this case too, it would not be possible by using the INDEX function alone.
If you, however, nest the match function inside the INDEX function, you can extract data even if the spreadsheet is large, and get the data for another entity, for example, with reference to the spreadsheet above, for another country by simply typing its name. You can accomplish both these tasks, without hard coding the INDEX function attributes.
As an example, let's consider the above spreadsheet again and use the INDEX and MATCH functions together and extract the Total Exports data for Japan.
First, we'll take a look at the format of the MATCH function:
MATCH(search_key, range, [search_type])
Assume that cell E1 has the header, Country, and cell F1 has the header Total Exports. Further assume that cell E2 has the text Japan as the data.
For the row attribute of the INDEX function:
MATCH(E2, A1:A4, 0)
For the column attribute of the INDEX function:
MATCH(F1, A1:B1, 0)
Now, let's nest the above two MATCH functions inside INDEX:
=INDEX(A1:C4, MATCH(E2, A1:A4, 0), MATCH(F1, A1:B1, 0))
Type the above function in cell F2 and press Enter. The INDEX function will return 49.6 as the data for Total Exports for Japan.
Now, you can simply type another country's name in the spreadsheet in cell E2 and get the Total Exports data for it.
In conclusion, if you nest the MATCH function inside the INDEX function you do not have to hard code the INDEX function attributes. This method is especially helpful, as said before,
for large spreadsheets.