Lookup mutiple columns - formula
Hello
I have a formula that i need help with. If you look at the attached spreadsheet, you'll see that in Column D there is a fairly involved lookup_for_checking_new_codes - 4 letter codes - not working (2).xlsxformula that someone did for me a while ago. The idea is that it is creating new customer/prospect codes from a four letter prefix, and then add on a 3 digit suffix, to create an unique code. This is what is meant to happen:

1) The formula in Col D looks at the letters in Col C and uses that as the first part of the new code. [eg ABCD]
2) The formula in Col D will then add 3 digits to the prefix to create the code. [eg. ABCD001]
3) It is then meant to look in Col A at the list of existing codes and if it sees the code it has just created in Col A, it will automatically use the next consecutive number. [ABCD002]
4) If ABCD002 has already been used as well, then the formula should choose ABCD003, and so on.
5) In addition, Col D also needs to look at the codes it has already created further up column D to make sure it isn't duplicating a code it has just created, and if so then use the next consecutive number.

The formula is working okay on step 5, but not step 3; ie. it is looking further up Col D, but not Col A. The red cells in Col E on the attachment show where the formula is not working correctly. (I did a simple IF= formula to check where the duplicates were - formula now deleted).

Can you help me to sort out this formula in Col D so that it correctly looks up both Col A and Col D?

Many thanks and sorry for such a long post, i can't think of a more concise way to describe it!