looking for a lookup solution

[baucucu]

Hi guys,

I am looking for a solution that would help me to lookup a series of codes within a list of concatenated codes.

the concatenated codes list looks like this

"Header status"
2INV 2TOP 3PAE OPN
2INV 2LEA DYNM L1 OPN REL
2INV DYNM OPN
2DUP 2INV LX OPN
2INV 3PAE OPN
2INV L1 OPN
2INV L1 OPN
2INV 2LEA L1 OPN
2NTI ID69 OPN
2NTI ID69 OPN
2INV 2LEA DYNM L1 OPN REL

while my code list looks like this

"Code"
CFGU
6CFG
2CFG
2BDB
6E0
6DIS
2VAT
2CMT
2CTY
2SHP
1ADR
2PUR
2LEA
LXOH
6OBN
6OBS
OBN
OBS
6B0
2DUP

I need a solution that does not involve VB since there is no one to update the VB script if there will be future changes.

Thank you guys

[daddylonglegs]

I'm not clear exactly what you want to do. If you just want a TRUE or FALSE next to each code, TRUE if it occurs in "header status" then try this formula in D2 copied down to D21

=(SUMPRODUCT(--ISNUMBER(SEARCH(C2,A$2:A$12)))>0)

where "header status" is A2:A12

codes in C2:C21

[baucucu]

I'm not clear exactly what you want to do. If you just want a TRUE or FALSE next to each code, TRUE if it occurs in "header status" then try this formula in D2 copied down to D21

=(SUMPRODUCT(--ISNUMBER(SEARCH(C2,A$2:A$12)))>0)

where "header status" is A2:A12

codes in C2:C21

Thanks for the reply.

There is a set of rules for each code, i.e. CFGU means config error and if the string contains this code I need trigger something.
Basically I need to place a message next to the string based on what codes are identified in it. Does it make more sense to you now?

[baucucu]

Thanks for the reply.

There is a set of rules for each code, i.e. CFGU means config error and if the string contains this code I need trigger something.
Basically I need to place a message next to the string based on what codes are identified in it. Does it make more sense to you now?

Just to be specific,

The formula should analyze the string <2INV 2LEA DYNM L1 OPN REL> based on a prioritized(sorted) code list and the most important code in the string will trigger a message.