Excel同工作簿双工作表基于多列匹配SIZE PERCENTAGES数值的方法咨询

Hey there! Based on your scenario where you need to pull the SIZE PERCENTAGES value from Worksheet B into Worksheet A using six matching columns (Line, Sub-Line, Category, Sub-Category, Occasion, Size Pack), here are two reliable methods tailored to different Excel versions:

Method 1: XLOOKUP (Excel 365 / Excel 2021 or newer) #

XLOOKUP makes multi-criteria matching straightforward by combining your six conditions into a single lookup key. For cell J11 in Worksheet A (as in your example), use this formula:

=XLOOKUP(A11&B11&C11&D11&E11&F11, WorksheetB!A:A&WorksheetB!B:B&WorksheetB!C:C&WorksheetB!D:D&WorksheetB!E:E&WorksheetB!F:F, WorksheetB!G:G, "No match")

• Breakdown:
  • A11&B11&C11&D11&E11&F11: Combines the six criteria from Worksheet A into a unique text string
  • WorksheetB!A:A&...&WorksheetB!F:F: Creates the same unique key for every row in Worksheet B
  • WorksheetB!G:G: The column containing the SIZE PERCENTAGES values you want to pull
  • "No match": Optional fallback text if no matching criteria are found (adjust as needed)

Method 2: INDEX + MATCH (Compatible with older Excel versions) #

If you're using an older Excel version that doesn't support XLOOKUP, use the INDEX+MATCH array formula. For J11 in Worksheet A:

=INDEX(WorksheetB!G:G, MATCH(1, (WorksheetB!A:A=A11)*(WorksheetB!B:B=B11)*(WorksheetB!C:C=C11)*(WorksheetB!D:D=D11)*(WorksheetB!E:E=E11)*(WorksheetB!F:F=F11), 0))

• Important Note: For pre-Excel 365 versions, you need to enter this as an array formula by pressing Ctrl + Shift + Enter instead of just Enter. Newer versions handle it automatically.
• Breakdown:
  • The (WorksheetB!A:A=A11)*... part checks each condition and returns 1 for rows where all six criteria match, 0 otherwise
  • MATCH(1, ..., 0) finds the first row where all conditions are true
  • INDEX(WorksheetB!G:G, ...) pulls the corresponding SIZE PERCENTAGES value from that row

Key Tips to Avoid Matching Issues #

• Ensure data formats are identical across both worksheets for all six criteria columns (e.g., no extra spaces in text, consistent number formatting)
• Narrow down your cell ranges (e.g., use WorksheetB!A2:A1000 instead of A:A) to make the formula run faster, especially with large datasets
• Confirm there are no duplicate six-criteria combinations in Worksheet B—both methods will return the first match they find

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