如何用公式对比数组值及用LOOKUP计算供应商款式数据差值

Hey there! Let's break down your two Excel questions step by step—first covering array value comparisons, then fixing that tricky LOOKUP issue you're stuck on.

There are a few common scenarios for array comparisons, each with straightforward solutions:

• Element-by-element comparison (return boolean results)：To check if values in two arrays match or have a size relationship, use basic operators directly. For example, if Array 1 is A1:A5 and Array 2 is B1:B5, to check if elements in A are greater than B, use =A1:A5 > B1:B5. In Excel 365/2021, just hit Enter; in older versions, use Ctrl+Shift+Enter to enter it as an array formula—this will return a list of TRUE/FALSE results.
• Calculate differences between corresponding elements：To get the difference between each pair of values in two arrays, simply subtract them: =A1:A5 - B1:B5. This works as a dynamic array formula in modern Excel, returning all results at once.
• Conditional array comparison：If you only want to compare values for a specific category (like a particular vendor), combine with the FILTER function. For example: =FILTER(D:D, A:A="coco") - FILTER(E:E, A:A="coco") will subtract values only for vendor "coco".

Your goal is to calculate the difference between the latest date value and the previous (second-latest) date value for the same vendor and style (like "coco" + "gk" where 6/8 minus 6/2 equals -86). The LOOKUP function isn't ideal here because it doesn't support multi-condition exact matches and relies on sorted data by default, which is why your setup failed. Here are two reliable solutions:

Solution 1: Use XLOOKUP (Recommended for Excel 365/2021+) #

XLOOKUP supports multi-condition searches and can easily find the latest date with reverse ordering. Assume your data structure is:

• Column A: Vendor
• Column B: Style
• Column C: Date
• Column D: Data Value

For cell E3 (targeting "coco" + "gk"), use this formula:

=XLOOKUP(1,(A:A="coco")*(B:B="gk")*(C:C=MAXIFS(C:C,A:A="coco",B:B="gk")),D:D) - XLOOKUP(1,(A:A="coco")*(B:B="gk")*(C:C=LARGE(IF((A:A="coco")*(B:B="gk"),C:C),2)),D:D)

How it works: #

• The first XLOOKUP uses MAXIFS to find the latest date for "coco" + "gk", then pulls the corresponding value.
• The second XLOOKUP uses LARGE(IF(...),2) to find the second-latest date for the same vendor/style, then gets its value.
• To make this formula auto-fill for all rows (using cell references instead of hardcoded values), adjust it like this:

=XLOOKUP(1,(A:A=A3)*(B:B=B3)*(C:C=MAXIFS(C:C,A:A=A3,B:B=B3)),D:D) - XLOOKUP(1,(A:A=A3)*(B:B=B3)*(C:C=LARGE(IF((A:A=A3)*(B:B=B3),C:C),2)),D:D)

Solution 2: Use INDEX + MATCH (Compatible with Older Excel Versions) #

If you don't have access to XLOOKUP, this combination works for all Excel versions:

=INDEX(D:D,MATCH(MAXIFS(C:C,A:A="coco",B:B="gk"),C:C,0)) - INDEX(D:D,MATCH(LARGE(IF((A:A="coco")*(B:B="gk"),C:C),2),C:C,0))

Note: In pre-365 Excel, you need to enter this as an array formula by pressing Ctrl+Shift+Enter instead of just Enter.

Troubleshooting Tips: #

If you get an error instead of -86, check these:

• Ensure all dates are formatted as actual dates (not text strings).
• Double-check that vendor/style names are exactly identical (no extra spaces, case mismatches).
• Make sure the vendor/style pair has at least 2 rows of data (otherwise, there's no "previous" value to compare).

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