Google Sheets中数组形式Index Match函数应用技术求助

Hey there, let's get this sorted for you. You've got a "Database" sheet tracking countries and their operating companies, and you want to reverse that view in the "Countries by Company" sheet using array formulas with INDEX/MATCH. Here's a step-by-step breakdown:

Step 1: Extract Unique Company Names #

First, let's pull all distinct company names into your "Countries by Company" sheet. Assuming you want the company list in column A (with cell A1 as the header "Company"), paste this formula in cell A2:

=UNIQUE(Database!B:B)

This will automatically fetch every unique company from column B of your "Database" sheet—no need to drag it down, it works as an array formula by default.

Step 2: List All Countries for Each Company (Combined into One Cell) #

If you want all countries for a company to show up in a single cell (e.g., "1, 3, 5" for Company A), use this array formula in cell B2 of "Countries by Company" (with B1 as the header "Operating Countries"):

=ARRAYFORMULA(IF(A2:A="",,TEXTJOIN(", ", TRUE, INDEX(Database!A:A, SMALL(IF(Database!B:B=A2:A, ROW(Database!A:A)-ROW(Database!A1)), ROW(INDIRECT("1:"&COUNTA(FILTER(Database!A:A, Database!B:B=A2:A)))))))))

Quick Breakdown of the Formula: #

• ARRAYFORMULA: Makes the formula apply to the entire column automatically, so you don't have to copy it down for every row.
• IF(A2:A="",, ...): Skips empty cells in the company column to avoid unnecessary errors.
• TEXTJOIN(", ", TRUE, ...): Joins all matching countries into a clean string, separated by commas and spaces (the TRUE ignores any empty values).
• INDEX(Database!A:A, SMALL(...)): Uses SMALL to fetch each matching country row in sequential order.
• IF(Database!B:B=A2:A, ROW(Database!A:A)-ROW(Database!A1)): Identifies all rows where the company matches the current cell in column A, returning their relative row numbers (subtracting the header row to adjust for data starting at row 1).
• ROW(INDIRECT("1:"&COUNTA(FILTER(...)))): Generates a sequence of numbers from 1 to the total number of matching countries, so SMALL can pull each one in turn.

Step 3: List Countries in Separate Rows (One Country per Row) #

If you prefer each country to occupy its own row under the company name (e.g., Company A in A2, Country 1 in B2; Company A again in A3, Country 3 in B3), use these two formulas:

1. In cell A2 of "Countries by Company":

   =FLATTEN(REPT(UNIQUE(Database!B:B)&" ", COUNTA(FILTER(Database!A:A, Database!B:B=UNIQUE(Database!B:B)))))
   

   This repeats each company name once for every country it operates in.
2. In cell B2:

   =ARRAYFORMULA(IFERROR(INDEX(Database!A:A, SMALL(IF(Database!B:B=A2:A, ROW(Database!A:A)-ROW(Database!A1)), COUNTIF(A$2:A2, A2)))))
   

   This pulls each matching country in sequence for the repeated company names.

Key Notes #

• Make sure your "Database" sheet has headers in row 1—if your data starts in a different row, adjust the ROW(Database!A1) part to match your actual header row number.
• If you have duplicate country-company pairs in "Database", the UNIQUE function will automatically deduplicate them in the company list. If you want to keep duplicates, remove UNIQUE from the first formula.

内容的提问来源于stack exchange，提问作者franciscofcosta