Excel公式需求：匹配3个值返回第4个值及区块统计实现

Hey there! Let's break down each of your Excel formula needs step by step, tailored for your Main Sheet and Data Dump worksheets:

1. Match 3 Values & Return a 4th Value #

For this, you have two solid options depending on your Excel version:

Option 1: XLOOKUP (Excel 365/2021+) #

This is the cleanest approach for multi-condition matches. Suppose your three lookup values are in Main Sheet cells A2, B2, C2, and you want to pull the corresponding value from column D in Data Dump:

=XLOOKUP(1, ('Data Dump'!$A:$A='Main Sheet'!$A2)*('Data Dump'!$B:$B='Main Sheet'!$B2)*('Data Dump'!$C:$C='Main Sheet'!$C2), 'Data Dump'!$D:$D)

The 1 matches the position where all three conditions are true (since TRUE*TRUE*TRUE=1).

Option 2: INDEX + MATCH (All Excel Versions) #

If you're on an older Excel version, use this array formula (press Ctrl+Shift+Enter after entering it):

=INDEX('Data Dump'!$D:$D, MATCH(1, ('Data Dump'!$A:$A='Main Sheet'!$A2)*('Data Dump'!$B:$B='Main Sheet'!$B2)*('Data Dump'!$C:$C='Main Sheet'!$C2), 0))

The MATCH function finds the row where all three conditions align, and INDEX pulls the value from column D.

2. Count Expiring Blocks by Service Type, Time Period & Block Length #

Use the COUNTIFS function to count rows that meet all your criteria and have an expiration date that's due (or past due). Let's assume:

• Service Type is in Data Dump column E
• Time Period is in column F
• Block Length is in column G
• Expiration Date is in column H

Here's the formula for Main Sheet (adjust cell references to match your setup):

=COUNTIFS(
  'Data Dump'!$E:$E, 'Main Sheet'!$E2,
  'Data Dump'!$F:$F, 'Main Sheet'!$F2,
  'Data Dump'!$G:$G, 'Main Sheet'!$G2,
  'Data Dump'!$H:$H, "<="&TODAY()
)

If you want to count blocks expiring in the next X days, replace "<="&TODAY() with ">="&TODAY(), "<="&TODAY()+7 (for 7 days, adjust the number as needed).

3. Query Acceptable Vehicle Counts (Standard/Large) by Time Period & Service Type #

To make this user-friendly, use a two-condition lookup to pull the allowed vehicle counts directly into cells. Let's say in Data Dump you have:

• Column A: Service Type
• Column B: Time Period
• Column C: Standard Vehicle Limit
• Column D: Large Vehicle Limit

For Standard Vehicles: #

=XLOOKUP('Main Sheet'!$A2&'Main Sheet'!$B2, 'Data Dump'!$A:$A&'Data Dump'!$B:$B, 'Data Dump'!$C:$C)

For Large Vehicles: #

=XLOOKUP('Main Sheet'!$A2&'Main Sheet'!$B2, 'Data Dump'!$A:$A&'Data Dump'!$B:$B, 'Data Dump'!$D:$D)

If you prefer INDEX + MATCH (compatible with all versions):

=INDEX('Data Dump'!$C:$C, MATCH('Main Sheet'!$A2&'Main Sheet'!$B2, 'Data Dump'!$A:$A&'Data Dump'!$B:$B, 0))

This lets users input a service type and time period in Main Sheet, and the formula automatically pulls the correct vehicle limits—no clunky UI needed!

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