如何用Excel公式实现：输入数字返回对应列标题并多列拼接

Hey there! Let's figure out how to build this Excel formula for your needs. You want a cell where users input a number, and the adjacent cell returns all column headers where that number exists (comma-separated if multiple columns have it), even with a large dataset. Here's how to do it based on your Excel version:

These versions support dynamic array functions like BYCOL and LAMBDA, which make this task clean and efficient.

Assuming your worksheet setup is:

• Column headers are in row 1 (e.g., A1:Z1)
• Your data rows span from row 2 to row 1000 (e.g., A2:Z1000)
• User inputs the target number in cell AA2
• You want the result in cell AB2

Use this formula in AB2:

=TEXTJOIN(", ", TRUE, BYCOL(A1:Z1000, LAMBDA(col, IF(COUNTIF(DROP(col,1), AA2)>0, INDEX(col,1), ""))))

How it works: #

• BYCOL(A1:Z1000, LAMBDA(col, ...)): Loops through every column in your data range. Each column is passed as the col variable for processing.
• DROP(col,1): Removes the first row (the header) from the current column, leaving only your data rows.
• COUNTIF(DROP(col,1), AA2)>0: Checks if the target number (from AA2) exists anywhere in the current data column.
• INDEX(col,1): If the number is found, returns the header for that column; otherwise, returns an empty string.
• TEXTJOIN(", ", TRUE, ...): Takes all non-empty header results and joins them with a comma and space. The TRUE argument automatically ignores empty strings.

If you don't have access to dynamic array functions, use this array formula (you'll need to enter it with Ctrl + Shift + Enter instead of just Enter):

=TEXTJOIN(", ", TRUE, IF(MMULT(--(A2:Z1000=AA2), ROW(A2:A1000)^0)>0, A1:Z1, ""))

How it works: #

• --(A2:Z1000=AA2): Converts every cell in your data range to 1 if it matches the target number, 0 otherwise.
• ROW(A2:A1000)^0: Creates a column of all 1s (any number to the power of 0 is 1) to use for summing matches per column.
• MMULT(..., ...): Calculates the sum of 1s for each column. A sum greater than 0 means the target number exists in that column.
• IF(..., A1:Z1, ""): Returns the column header if the sum is positive, empty string otherwise.
• TEXTJOIN handles the comma-separated join just like the modern version.

• Adjust ranges: Replace A1:Z1000, AA2, and AB2 with your actual worksheet ranges to match your data layout.
• Handle "no matches": If you want to show a friendly message when the number isn't found anywhere, wrap the formula in an IF statement:

  =IF(TEXTJOIN(", ", TRUE, ...)="", "No matching columns found", TEXTJOIN(", ", TRUE, ...))
  

• Performance: For extremely large datasets (10k+ rows), the modern BYCOL version is more efficient than the old array formula, as it processes columns directly without full range array operations.

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