Excel中基于多条件匹配跨表转移数据的Index/Match函数使用问询

Got it, let's break down how to pull those p1-p7 values from Table1 into Table2 using a multi-condition match with INDEX/MATCH (or XLOOKUP for newer Excel versions). The key here is to create a unique composite key from Table1's A/B/C/D columns that matches the string in Table2's X column.

Step 1: Understand the Composite Match Logic #

Table2's X column should correspond to the combined values of Table1's A, B, C, D columns. To avoid accidental matches (e.g., "App" + "leRed" vs "Apple" + "Red"), we'll add a separator like | between each column value when creating our match key.

Step 2: INDEX/MATCH Formula for Multi-Condition Lookup #

For the first target cell in Table2 (say, cell B2 where you want p1 data), use this formula:

=IFERROR(INDEX(Table1[ p1 ], MATCH(Table2[@X], Table1[A] & "|" & Table1[B] & "|" & Table1[C] & "|" & Table1[D], 0)), "")

Let's break this down:

• Table1[A] & "|" & Table1[B] & ...: Creates a composite string for each row in Table1 that mirrors the format of Table2's X column.
• MATCH(Table2[@X], ..., 0): Finds the position of the matching composite key in Table1.
• INDEX(Table1[ p1 ], ...): Pulls the corresponding p1 value from that position.
• IFERROR(..., ""): Returns a blank instead of an error if no match is found.

Notes for Older Excel Versions:

If you're using Excel 2019 or earlier, this formula needs to be entered as an array formula—press Ctrl + Shift + Enter instead of just Enter after typing it. Excel will wrap it in curly braces {} automatically (don't type them manually).

Step 3: Copy the Formula Across p1-p7 Columns #

Once you have the formula working for p1, simply drag the fill handle (the small square at the bottom-right of the cell) across to p7. Excel will automatically update the Table1[ p1 ] reference to Table1[ p2 ], Table1[ p3 ], etc.

Alternative: XLOOKUP (Excel 365/2021+) #

If you have access to XLOOKUP, the formula is cleaner and doesn't require array entry:

=XLOOKUP(Table2[@X], Table1[A]&"|"&Table1[B]&"|"&Table1[C]&"|"&Table1[D], Table1[ p1 ], "")

XLOOKUP handles the match and index in one step, and it's more intuitive for multi-condition scenarios.

Example with Sample Data #

Suppose:

• Table1 has a row where A="Apple", B="Red", C="Small", D="Fresh" → composite key is Apple|Red|Small|Fresh
• Table2's X column has Apple|Red|Small|Fresh in row 2
• The formula in Table2's p1 cell will pull the p1 value from that Table1 row.

Troubleshooting Tips #

• Ensure consistent formatting: Make sure the separator (like |) is used in Table2's X column exactly as in your formula. If X doesn't use a separator, remove it from the formula (but be cautious of false matches).
• Check for extra spaces: Use TRIM() around each column reference if there might be leading/trailing spaces (e.g., TRIM(Table1[A]) & "|" & TRIM(Table1[B])).
• Case sensitivity: By default, Excel matches text case-insensitively. If you need case-sensitive matches, use EXACT() in the MATCH function (though this requires an array formula).

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