Lookup across multiple columns

**Caradonguy** · 06-12-2013, 11:52 AM

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!

**ChemistB** · 06-12-2013, 12:39 PM

This one starts with 000 and is a varient of what you have

=LEFT(C2,4)&TEXT((RIGHT("000"& MAX(IF((I$2:$I$9999=C2)*(1),H$2:$H$9999)),3)+COUNTIF($C$1:C1,C2)),"000")

This one starts with 001
=C2&TEXT(COUNTIF(A:A,C2&"*")+ COUNTIF($D$1:$D1,C2&"*")+1,"000")
I put the first equation in D and 2nd in E (modified of course)

**Caradonguy** · 06-13-2013, 01:32 AM

Hello ChemistB
Thanks so much for solving my query - it is exactly what i wanted, wish i'd found you before!
I'd like to click the star to add to your reputation but there wasn't one on the thread as far as i can see - sorry i'm new to this so not 100% au fait. If you can tell me where to find it, i'll click it!
Thanks
Caradonguy

** have found star now, and clicked it **