Sorting or Searching?

[Guest]

I am still designing a purchase order form for automotive sales and now have
a new question.

For example, if I have a ZPP Premium Package that includes a list of options
that has its own codes, i.e. ZPP = 319, 4NA, 430, 431, 403..... how can I
get Excel to automatically pick up ZPP if I receive a full list that have
other options?

For example, I have an automobile that has the following list of options:

205, 465, 319, 203, 4NA, 4AB, 430, 431, 639

How can I get Excel to recognize that ZPP is part of this list?

Your assistance (again) is greatly appreciated.

 

[Dave O]

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)