Index Match多匹配与区域引用问题：动态获取代理佣金比例

Absolutely, this is totally doable! The issue with your second formula is that using 'Group Commissions'!C:E directly in the second MATCH looks through the entire 3-column range, returning the first occurrence of your agent name anywhere in those columns—not just on the row that matches your client. Let's fix this by targeting the exact row for your client first.

Step-by-Step Fix #

We'll narrow down the agent lookup to only the row that corresponds to your client, then pull the correct commission rate:

For Excel 365/2021 (no array formula needed):

=INDEX('Group Commissions'!G:I,
       MATCH('Paul'!B11, 'Group Commissions'!A:A, 0),
       MATCH('Paul'!B4,
             INDEX('Group Commissions'!C:E, MATCH('Paul'!B11, 'Group Commissions'!A:A, 0), 0),
             0))

For Older Excel Versions (requires Ctrl+Shift+Enter as array formula):

{=INDEX('Group Commissions'!G:I,
        MATCH('Paul'!B11, 'Group Commissions'!A:A, 0),
        MATCH('Paul'!B4,
              INDEX('Group Commissions'!C:E, MATCH('Paul'!B11, 'Group Commissions'!A:A, 0), 0),
              0))}

How It Works #

1. Find the client's row: The first MATCH('Paul'!B11, 'Group Commissions'!A:A, 0) gets the row number where your client name lives in the Group Commissions sheet.
2. Target the agent columns for that row: INDEX('Group Commissions'!C:E, [client row number], 0) extracts only columns C-E from the client's specific row—so we're only looking for the agent name in the right place.
3. Match the agent in that row: The second MATCH finds which position (1, 2, or 3) the agent occupies in the extracted C-E row.
4. Pull the commission rate: Finally, INDEX('Group Commissions'!G:I, [client row number], [agent column position]) grabs the corresponding value from columns G-I (which align with C-E's agent columns).

Bonus: Cleaner XLOOKUP Alternative (Excel 365+) #

If you have access to XLOOKUP, this simplifies things even more:

=XLOOKUP('Paul'!B4,
         INDEX('Group Commissions'!C:E, XLOOKUP('Paul'!B11, 'Group Commissions'!A:A, 'Group Commissions'!A:A), 0),
         INDEX('Group Commissions'!G:I, XLOOKUP('Paul'!B11, 'Group Commissions'!A:A, 'Group Commissions'!A:A), 0))

This does the same job but uses XLOOKUP's ability to directly map the client row, then match the agent within that row's columns.

Example Verification #

When Paul!B11 = "Client 1" and Paul!B4 = "Paul":

• The first MATCH finds the row for Client 1 in Group Commissions!A:A.
• The inner INDEX pulls columns C-E from that row.
• The second MATCH finds where "Paul" is in those 3 columns (e.g., position 2 if it's in column D).
• The final INDEX returns the value from column H (since G:I is the commission range matching C:E), which would be "12.5" as expected.

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