嵌套VLOOKUP返回单个值：提取指定日期班次的Expr1003数值

Hey there! I get that you're trying to pull the correct Expr1003 value using a specific date (Expr1001) and shift, and nested VLOOKUP isn't giving you the right result. Let's walk through a few reliable solutions that work better for multi-condition lookups.

Solution 1: INDEX + MATCH (Most Compatible) #

This is the go-to method for multi-condition lookups and works in almost all Excel versions. It's way more flexible than VLOOKUP because it doesn't force your lookup column to be the first one in the range.

Formula: #

=INDEX(C:C, MATCH(1, (A:A=YourDateCell)*(B:B=YourShiftCell), 0))

Breakdown: #

• (A:A=YourDateCell)*(B:B=YourShiftCell): Creates an invisible array where only the row matching both your target date and shift returns 1—all other rows return 0.
• MATCH(1, ..., 0): Finds the exact row position of that 1 (your matching row).
• INDEX(C:C, ...): Pulls the corresponding value from column C (Expr1003) at that matched row.

Quick Note: #

• If you're using an older Excel version (pre-365/2021), you need to enter this as an array formula by pressing Ctrl + Shift + Enter instead of just Enter. Newer Excel versions handle this automatically.

Example: #

If your target date is in cell F2 (18-Sep-17) and target shift is in G2 (02), plugging those into the formula gives:

=INDEX(C:C, MATCH(1, (A:A=F2)*(B:B=G2), 0))

This returns 5856, which perfectly matches your dataset.

Solution 2: XLOOKUP (Excel 365/2021+) #

If you have access to the latest Excel versions, XLOOKUP simplifies multi-condition lookups into a single, clean formula—no array tricks needed:

Formula: #

=XLOOKUP(1, (A:A=YourDateCell)*(B:B=YourShiftCell), C:C)

This does the exact same job as the INDEX+MATCH combo but is more readable and straightforward to edit later.

Solution 3: SUMPRODUCT (Quick Alternative) #

Since your dataset has unique date+shift pairs, SUMPRODUCT can also get the job done. It multiplies the condition arrays and sums the matching Expr1003 values (which will just be the single matching value here, since there's no duplicates):

Formula: #

=SUMPRODUCT((A:A=YourDateCell)*(B:B=YourShiftCell)*C:C)

Why Nested VLOOKUP Isn't Ideal Here #

Nested VLOOKUP struggles with multi-condition lookups because it's built to match against a single lookup column. You could make it work by creating a helper column that combines date and shift (like A2&B2), but the methods above are more efficient and don't require cluttering your sheet with extra columns.

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