寻求财务模型完整性校验的Excel公式：检查区域值全匹配

Got it, let's solve this problem where you need to verify every product level in Tab1!B1:B100 exists in Tab2!C2:C200 and return a clear "Complete" or "Incomplete" status. I'll break this down by Excel version since dynamic array functions make this simpler in newer releases.

For Excel 365 / Excel 2021 (Dynamic Array Support) #

This is the cleanest approach, thanks to native array handling:

=IF(AND(COUNTIF(Tab2!C2:C200, Tab1!B1:B100)>0), "Complete", "Incomplete")

How it works: #

1. COUNTIF(Tab2!C2:C200, Tab1!B1:B100) generates an array where each entry is the number of times the corresponding value in Tab1!B1:B100 appears in Tab2!C2:C200.
2. AND(...) checks if all values in that array are greater than 0 (meaning every value from Tab1 has at least one match in Tab2).
3. The IF function returns "Complete" if the check passes, otherwise "Incomplete".

If you prefer a more explicit row-by-row check, you can use BYROW and LAMBDA:

=IF(ALL(BYROW(Tab1!B1:B100, LAMBDA(x, COUNTIF(Tab2!C2:C200, x)>0))), "Complete", "Incomplete")

For Older Excel Versions (Pre-365/2021) #

Older Excel requires an array formula (enter it with Ctrl+Shift+Enter instead of just Enter):

=IF(COUNT(IF(COUNTIF(Tab2!C2:C200, Tab1!B1:B100)=0, 1))=0, "Complete", "Incomplete")

How it works: #

1. COUNTIF(...) creates the same match-count array as before.
2. IF(...,1) marks any value with 0 matches (missing from Tab2) as 1.
3. COUNT(...) counts how many such missing values exist. If the count is 0, all values are present, so return "Complete".

Bonus: Handle Empty Cells & Case Sensitivity #

• Ignore empty cells in Tab1: If you don't want blank cells in Tab1!B1:B100 to trigger an "Incomplete" status, adjust the 365 formula like this:

  =IF(AND(IF(Tab1!B1:B100<>"", COUNTIF(Tab2!C2:C200, Tab1!B1:B100)>0, TRUE)), "Complete", "Incomplete")
  

  This treats blank cells as automatically valid.

• Case-sensitive matching: If you need exact case matches (e.g., "ProductA" vs "productA" are different), use EXACT and SUMPRODUCT (works in all versions):

  =IF(AND(BYROW(Tab1!B1:B100, LAMBDA(x, SUMPRODUCT(--EXACT(Tab2!C2:C200, x))>0))), "Complete", "Incomplete")
  

  The -- converts the boolean results from EXACT to 1s and 0s, and SUMPRODUCT counts how many exact matches exist.

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