Hi All,

I am new in this forum and would like to ask your advise for below case:

I want to have a excel formula to enable filling the column Code1 and Code2 automatically with only filling Code3 column referring to the list of criteria to avoid the wrong combination
I have tried to use this formula: =IF(ISNUMBER(SEARCH("BB11",$H8)),"PP001",0) and found the formula has a limit only for 6 criterias while I have more than 20 project name. Kindly advise. Thanks in advance.

Note: Code 3 is unique numbers while Code 1 and Code 2 could be the same.


Date Description Code1 Code2 Code3
1-Jan-12 Danish-School fee BB111
15-Jan-12 Ali- health training BB211
23-Jan-12 Gender-staff cost BB314


List of criteria
Project Name Name Code1 Code2 Code3
Education School fee PP001 AA12 BB111
Uniform PP001 AA12 BB112
Supporting book PP001 AA12 BB113
Extra activities PP001 AA12 BB114
Field visit PP001 AA12 BB115
PTA PP001 AA12 BB116
Staff cost PP001 AA12 BB117

Health Training PP002 AB23 BB211
Health supplies PP002 AB23 BB212
Community Workshop PP002 AB23 BB213
Staff Cost PP002 AB23 BB214

Gender Training PP001 BC34 BB311
Gender awareness PP001 BC34 BB312
Community Workshop PP001 BC34 BB313
Staff Cost PP001 BC34 BB314