跨工作簿提取指定匹配数据的最优Excel公式咨询

If you’re using Excel 365 or Excel 2021, the FILTER function is the cleanest and most efficient solution here—it’s dynamic, automatically spills matching results into column A of Workbook B, and requires no manual dragging of formulas.

Formula for Modern Excel: #

Enter this directly in cell A1 of Workbook B:

=FILTER('Workbook A.xlsx'!C:C, 'Workbook A.xlsx'!A:A="San Fran")

• Quick breakdown:
  • 'Workbook A.xlsx'!C:C targets the Ring ID column (column C) in your source workbook.
  • 'Workbook A.xlsx'!A:A="San Fran" filters only rows where the Region value is "San Fran".
• Note: If Workbook A is closed, replace the workbook reference with its full file path, like ='C:\Your\File\Location\[Workbook A.xlsx]Sheet1'!C:C (adjust the sheet name if your data isn’t on Sheet1).

For Older Excel Versions (pre-365/2021): #

Since FILTER isn’t available in older versions, use an array formula combining INDEX, SMALL, and IF to pull matching values one by one:

1. Enter this formula in cell A1 of Workbook B:

=INDEX('Workbook A.xlsx'!C:C, SMALL(IF('Workbook A.xlsx'!A:A="San Fran", ROW('Workbook A.xlsx'!A:A)-MIN(ROW('Workbook A.xlsx'!A:A))+1), ROWS($A$1:A1)))

2. Press Ctrl + Shift + Enter (not just Enter) to confirm it as an array formula.
3. Drag the formula down column A until you see #NUM!—that’s your signal there are no more matching Ring IDs left.

Pro Tips: #

• Adjust column letters if your data isn’t in columns A (Region) and C (Ring ID) in Workbook A.
• To avoid including headers, narrow your ranges to start from row 2 (e.g., 'Workbook A.xlsx'!C2:C1000 instead of C:C).

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