寻求公式实现：当指定列表名称匹配目标列任意名称时，将多列合并导入单列

Got it, let's figure out how to solve this exactly as you described. I know you’ve tried mixing QUERY, FILTER, VLOOKUP without luck—let’s break down a straightforward formula that hits your expected result.

Scenario Recap #

• Sheet1 Column A: Contains target names: Jim, John, James
• Sheet2: 5 columns of names, where columns 1, 3, 4 have at least one match to Sheet1’s list
• Goal: Combine all content from matching columns + the full Sheet1 list, then deduplicate into a single column

Google Sheets Solution #

Paste this formula into the cell where you want your final list to start (e.g., Sheet3!A1):

=UNIQUE(FLATTEN(
  FILTER(Sheet1!A:A, Sheet1!A:A<>""), // Include all non-blank names from Sheet1's list
  FILTER(Sheet2!A:E, BYCOL(Sheet2!A:E, LAMBDA(col, COUNTIF(Sheet1!A:A, col) > 0)))
))

How This Works:

1. FILTER(Sheet1!A:A, Sheet1!A:A<>""): Grabs all non-blank names from your target list (avoids empty cells cluttering the result)
2. BYCOL(Sheet2!A:E, LAMBDA(col, COUNTIF(Sheet1!A:A, col) > 0)): Checks each column in Sheet2 to see if it contains any name from Sheet1’s list. Returns TRUE for columns with at least one match.
3. FILTER(Sheet2!A:E, ...): Pulls only the columns from Sheet2 that passed the check above (columns 1, 3, 4 in your example)
4. FLATTEN(...): Converts the filtered multi-column data + Sheet1’s list into a single long column
5. UNIQUE(...): Removes duplicate entries to get your clean final list

For your example, this will output exactly: Jim, John, James, Alex, Ben, Jeremy, Felix, Eric, Evan

Excel Solution (365/2021+) #

If you’re using Excel with dynamic array support, use this formula instead:

=UNIQUE(VSTACK(
  FILTER(Sheet1!A:A, Sheet1!A:A<>""),
  TOCOL(FILTER(Sheet2!A:E, MMULT(--(COUNTIF(Sheet1!A:A, Sheet2!A:E)>0), ROW(INDIRECT("1:"&ROWS(Sheet2!A:A)))^0)>0), 2)
))

Quick Breakdown:

• MMULT(...) replaces Google Sheets’ BYCOL to check if each column has matching names
• TOCOL(..., 2) converts filtered columns to a single column (ignoring blanks)
• VSTACK combines Sheet1’s list with the converted column data before deduplicating with UNIQUE

Notes to Tweak for Your Use Case #

• If your target list or Sheet2 columns have blank rows, the FILTER(..., <> "") parts will skip them—remove that if you need to include blanks
• Adjust the range references (e.g., Sheet2!A:E) to match your actual data columns

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