Using Countif to create a Lookup column

davidclubb 

The issue with your formula arises when counting occurrences of pipe sizes ending in zero. The COUNTIF function considers numbers and text identically, leading to miscounting for values like "10" or "20". Here's a corrected formula that addresses this:

=D3 & TEXT(COUNTIF($D$2:D3, LEFT(D3, LEN(D3)-1) & "*"), "0")

Explanation:

1. LEFT(D3, LEN(D3)-1): Extracts all characters except the last digit from D3. For "10", it returns "1".
2. "*": Appends a wildcard character to the extracted prefix, making the pattern "1*".
3. COUNTIF($D$2:D3, LEFT(D3, LEN(D3)-1) & "*"): Counts the occurrences of pipe sizes that start with the characters in D3 followed by any digit using the wildcard.
4. TEXT(..., "0"): Converts the count to text format, ensuring leading zeros are preserved (e.g., "1" instead of "1").
5. &: Concatenates the pipe size (D3) with the formatted count, resulting in the desired output (e.g., "10|1").

This formula effectively counts occurrences based on the pipe size's prefix, excluding the trailing zero, and displays the count with leading zeros when necessary.