Excel跨工作表批量提取需求：基于表头字符串匹配提取对应行多结果的公式咨询

Got it, let's tackle this problem head-on. You need to pull all values from Sheet1's row 2 where the header in row 1 contains a specific keyword (like HouseNumber or HouseStreet), then list them vertically in Sheet2 under the corresponding header. The nested INDEX+MATCH approach only grabs the first match, so here are two reliable formula-based solutions:

1. Dynamic Array Formula (Excel 365/2021+) #

This is the cleanest approach if you're on a modern Excel version that supports dynamic arrays. It automatically spills results down without needing to drag formulas.

For extracting HouseNumber values to Sheet2 column A: #

In Sheet2!A2, enter this formula:

=TOCOL(FILTER(Sheet1!$2:$2, ISNUMBER(SEARCH("HouseNumber", Sheet1!$1:$1))))

For extracting HouseStreet values to Sheet2 column B: #

In Sheet2!B2, enter this formula:

=TOCOL(FILTER(Sheet1!$2:$2, ISNUMBER(SEARCH("HouseStreet", Sheet1!$1:$1))))

How it works:

• SEARCH("HouseNumber", Sheet1!$1:$1): Checks every cell in Sheet1's header row (row 1) for the keyword. Returns a position number if found, an error if not.
• ISNUMBER(...): Converts those results to TRUE (match found) or FALSE (no match).
• FILTER(Sheet1!$2:$2, ...): Pulls only the cells from Sheet1's row 2 that correspond to headers with your keyword.
• TOCOL(...): Converts the horizontal filtered results into a vertical list, which automatically spills down into the rows below A2/B2.

2. Compatibility Formula (Older Excel Versions: 2019 or Earlier) #

If you don't have dynamic array support, use this array formula combo with INDEX+SMALL to manually pull all matches.

For extracting HouseNumber values to Sheet2 column A: #

In Sheet2!A2, enter this formula, then press Ctrl+Shift+Enter (to trigger array formula mode), then drag it down until you see empty cells:

=IFERROR(INDEX(Sheet1!$2:$2, SMALL(IF(ISNUMBER(SEARCH("HouseNumber", Sheet1!$1:$1)), COLUMN(Sheet1!$1:$1)), ROW(A1))), "")

For extracting HouseStreet values to Sheet2 column B: #

In Sheet2!B2, enter this formula, press Ctrl+Shift+Enter, then drag down:

=IFERROR(INDEX(Sheet1!$2:$2, SMALL(IF(ISNUMBER(SEARCH("HouseStreet", Sheet1!$1:$1)), COLUMN(Sheet1!$1:$1)), ROW(A1))), "")

How it works:

• IF(ISNUMBER(SEARCH(...)), COLUMN(...)): Creates a list of column numbers where the header matches your keyword; returns FALSE for non-matching columns.
• SMALL(..., ROW(A1)): Grabs the 1st, 2nd, 3rd, etc., column number from that list as you drag the formula down (ROW(A1) becomes ROW(A2), ROW(A3), etc.).
• INDEX(Sheet1!$2:$2, ...): Pulls the value from Sheet1's row 2 at the specified column number.
• IFERROR(...): Returns an empty cell once there are no more matches, so you don't get #NUM! errors.

Example Verification #

Using your sample data:

• Sheet1 row 1: HouseNumber.1, HouseStreet.1, HouseNumber.2, HouseStreet.2

• Sheet1 row 2: 123, 1st Ave, 456, 2nd Ave

• Sheet2!A2 formula will return 123 in A2 and 456 in A3

• Sheet2!B2 formula will return 1st Ave in B2 and 2nd Ave in B3

This exactly matches the outcome you're looking for!

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