Excel中利用INDEX/MATCH查询员工当日所属区域（含区域变动场景）

Got it, let's solve this problem where employees switch regions (resulting in duplicate name rows in your rota) and you need to pull the correct region for each completed job based on its date.

First, Let's Recap Your Setup #

You have two tables:

1. Rota Table: Rows for employees (duplicates when regions change), with vertical names, horizontal dates, and a Region column. Duplicate name rows will have non-empty values only for dates when that region assignment was active.
2. Job Completion Table: Rows for each completed job, with Name, Job#, CompletedDate, and JobType.

Your existing formulas work for unique names, but we need to adjust them to handle duplicate names by checking which rota row has a non-empty entry for the job's date.

Formula Solutions #

Option 1: Excel 365/2021 (Using XLOOKUP - Simplest)

Use this formula in your Job Completion Table's Region column (assuming your job name is in cell A2 and CompletedDate is in D2):

=XLOOKUP(1,(Rota!$A:$A=A2)*(INDEX(Rota!$C:$G,,MATCH(D2,Rota!$1:$1,0))<>""),Rota!$B:$B,"No matching region",0,1)

Breakdown of how this works:

• Rota!$A:$A=A2: Matches rows where the rota name matches the job's employee name.
• INDEX(Rota!$C:$G,,MATCH(D2,Rota!$1:$1,0))<>"": Finds the column in the rota that matches the job's CompletedDate, then checks if that cell is non-empty (meaning this rota row is the active region assignment for that date).
• Multiplying these two conditions gives 1 only for the correct row. XLOOKUP then pulls the corresponding Region from column B.
• The "No matching region" is a fallback if no valid row is found.

Option 2: Older Excel Versions (INDEX/MATCH Array Formula)

If you don't have XLOOKUP, use this array formula (must press Ctrl+Shift+Enter after entering it, not just Enter):

=IFERROR(INDEX(Rota!$B:$B,MATCH(1,(Rota!$A:$A=A2)*(INDEX(Rota!$C:$G,,MATCH(D2,Rota!$1:$1,0))<>""),0)),"No matching region")

This works similarly to the XLOOKUP version:

• The MATCH function finds the row number where both the name matches and the date column has a non-empty value.
• INDEX pulls the Region from that row, and IFERROR handles cases where no match exists.

Testing with Your Example Data #

Let's say your rota now has an extra row for John Smith (West region, with non-empty values only for 15-17/12/19):

For John's job on 14/12/19, the formula will return North (since that row has a non-empty value for 14/12). For his job on 16/12/19, it will return West—exactly what you need for accurate region-based task stats.

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