基于Client和Supply双条件的Excel跨表成本匹配函数应用求助

Hey there! Let's fix this lookup issue you're having. Your current IFS formula only checks a single row in your cost table instead of scanning all entries, and basic VLOOKUP setups often fail with multiple criteria since they rely on a single lookup column. Below are reusable, reliable solutions tailored to your needs:

1. XLOOKUP (Excel 365/2021+) – Most Concise #

If you're using a modern Excel version, XLOOKUP is the simplest way to handle multi-condition lookups:

=XLOOKUP(1, (Table2[Client]=@Client)*(Table2[Supply]=@Supply), Table2[Cost], "No Match")

• How it works: The (Table2[Client]=@Client)*(Table2[Supply]=@Supply) part creates an array of 1s and 0s, where 1 marks rows that match both your client and supply criteria. XLOOKUP finds the first 1 and returns the corresponding cost from Table2[Cost]. The final "No Match" is optional – it replaces errors if no matching entry exists.

2. INDEX + MATCH (All Excel Versions) – Maximum Compatibility #

For older Excel versions or when you need broad compatibility, this classic combo is foolproof:

=INDEX(Table2[Cost], MATCH(1, (Table2[Client]=@Client)*(Table2[Supply]=@Supply), 0))

• Note for pre-365 Excel: You need to enter this as an array formula by pressing Ctrl+Shift+Enter instead of just Enter.
• How it works: MATCH locates the row number where both conditions are true (the 1 in the array), then INDEX pulls the cost value from that row in the Table2[Cost] column.

3. SUMPRODUCT (For Unique Client+Supply Combos) #

If each Client+Supply pair only appears once in your cost table, SUMPRODUCT is another solid option:

=SUMPRODUCT((Table2[Client]=@Client)*(Table2[Supply]=@Supply)*Table2[Cost])

To handle cases where no match exists (instead of returning 0), wrap it in an IF statement:

=IF(SUMPRODUCT((Table2[Client]=@Client)*(Table2[Supply]=@Supply))=0, "No Match", SUMPRODUCT((Table2[Client]=@Client)*(Table2[Supply]=@Supply)*Table2[Cost]))

• How it works: It multiplies the condition arrays with the cost values – only rows that match both criteria contribute their cost to the sum, giving you the exact value you need.

Why Your Previous Attempts Failed #

• Your IFS formula only compares the current row of Table2 (using @Client/@Supply) instead of checking every row in the table. It can't scan all entries to find a match.
• Basic IF+VLOOKUP setups usually fail here because VLOOKUP only uses the first column of the lookup range for matching. You'd need to combine the Client and Supply columns into a single lookup key (e.g., Table2[Client]&Table2[Supply]) to make it work, which is less clean than the solutions above.

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