Index Match in Array in Google Sheets

Question

I have a spreadsheet 'Database' where there are two columns, 'Countries' (A) and 'Companies'(B). Countries are numbered from 1-10. Companies are labelled with letters.

The spreadsheet tracks the companies that operate in each country. As such, a row that has Country 1 in column A and Company A in column B means that Company A operates in country 1. This is a screenshot from this spreadsheet:

I then have a second spreadsheet called 'Countries by Company' where I would like to list the countries in which a company operates. I would like them to be listed in different rows starting form cell B3 of the 'Countries by Company' sheet. The company name for which the countries should be listed is in cell B1 of the same sheet. I gather this has to be done with an ARRAY version of an INDEX MATCH function but I can't seem to get it to work.

I have put the following formula in cell B3 of the 'Countries by Company' sheet: =INDEX(Database!$A$1:$B$76, MATCH($B$1, Database!$B$1:$B$76, 0), COLUMN(Database!A:A)). However, as expected, it will only return the first country in which the company in cell B1 operates and no other country. Again, I would need all countries in which that company operates listed in different rows from cell B3.

I have set up a sample sheet in here. I thought it might help in case I wasn't too clear.

Thanks for your help!

tehhowch tehhowch · Accepted Answer · 2018-03-26T17:25:45

Using FILTER allows restricting the values on the Database sheet to only those that match a given criteria:

=FILTER(Database!$A:$A, Database!$B:$B=$B$1)

yields every country index for the given company index on the Database sheet. If only the country index values (e.g. 1, 3, 6) are desired, rather than converting those index values to a name or other property that is held in a hypothetical "Country DB" sheet, the above FILTER is sufficient.

If additional information is desired, then VLOOKUP can be used to return the corresponding values for multiple inputs (i.e. an array input):

B3:  =ARRAYFORMULA(VLOOKUP(
         FILTER('Database'!$A:$A, 'Database'!$B:$B=$B$1),  // Searched values
         'Country DB'!$A:$B,                               // Database sheet having more country information (like name, gdp, ...)
         2,                                                // Column of the desired information
         FALSE))

will yield the 2nd column from each corresponding country index row in the "Country DB" sheet:

Index Match in Array in Google Sheets

2 Answers