如何检查工单编号是否在指定列？IF函数公式异常求助

Let me break down the problem and walk you through the fix:

Why Your Current Formula Fails #

The REGEXMATCH function is built for regular expression pattern matching, not for checking if a value exists in a range. When the 'Shipment Inventory Responses'!D:D column is empty, your formula effectively checks if E2 matches an empty string — which can return TRUE unexpectedly (even if E2 has a value, the function’s handling of empty ranges leads to this false positive). Additionally, REGEXMATCH can trigger false "Yes" results for partial matches (e.g., if E2 is "ORD123" and D column has "ORD1234"), which breaks your unique ID check.

The Correct Solutions: Existence-Check Functions #

For verifying unique ID presence, use functions designed specifically for this task:

Option 1: Using COUNTIF

This counts how many times E2 appears in the target column. A count greater than 0 means the ID exists:

=IF(COUNTIF('Shipment Inventory Responses'!D:D, E2) > 0, "Yes", "No")

• When the target column is empty, COUNTIF returns 0, so the formula correctly outputs "No".
• It enforces exact matches, which is ideal for your unique work order IDs.

Option 2: Using MATCH + ISNUMBER

The MATCH function returns the position of E2 in the column if it exists; otherwise, it returns an error. ISNUMBER checks if the result is a valid position:

=IF(ISNUMBER(MATCH(E2, 'Shipment Inventory Responses'!D:D, 0)), "Yes", "No")

• The 0 in MATCH ensures an exact match.
• When the target column is empty, MATCH returns an error, so ISNUMBER becomes FALSE, and the formula outputs "No".

Quick Pro Tip #

Double-check that the format of your work order IDs in E2 matches the format in 'Shipment Inventory Responses'!D:D (e.g., both text or both numbers). Mismatched formats can cause false "No" results even when the ID exists.

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