Sorting or Searching?
Here's one way. It assumes the option codes are entered in the range
B1:B9, which you'll need to change to match your spreadsheet. This
formula assumes that numbers such as 319 and 403 are entered as
numbers, not text. If they are entered as text, use the second version
of the formula.
The formula results in TRUE or FALSE: if all 5 constituent codes for
ZPP are present, the formula results in TRUE. You can use that in an
IF statement to handle the ZPP situation as appropriate.
=AND(SUMPRODUCT(--(B1:B9=319))=1,SUMPRODUCT(--(B1:B9="4NA"))=1,SUMPRODUCT(--(B1:B9=430))=1,SUMPRODUCT(--(B1:B9=431))=1,SUMPRODUCT(--(B1:B9=403))=1)
If option code numbers are entered as text:
=AND(SUMPRODUCT(--(B1:B9="319"))=1,SUMPRODUCT(--(B1:B9="4NA"))=1,SUMPRODUCT(--(B1:B9="430"))=1,SUMPRODUCT(--(B1:B9="431"))=1,SUMPRODUCT(--(B1:B9="403"))=1)
|