Search a column and insert a code in a different column based on the finding in each row.

Hi Guys

Hoping someone can help and hoping this makes sense.

Every week I receive a worksheet with around 50 different transactions on it under the column(B) heading "Description". I run a sort and then insert a code in column E.

E.g. If the description in column B = Test account, the code in column E = P. Also, If the description in column B = sterling fee, the code in column E = V ......etc etc.

I want an IF statement or something to search the whole of column B and code column E appropriately in the same row.
I realize I will need to write all the 50 or so different options that the statement needs to look for but can anyone help with the basics please? Ideally I would write a list that can be searched and that I can add to at any time.

Andrew

make life simpler for yourself...

On another sheet, set up your table and use a simple INDEX-MATCH

=IFERROR(INDEX(Sheet2!B:B,MATCH(Sheet1!B2,Sheet2!$A$1:$A$5,0)),"")

Hmm. i thought I had posted a Q about this... but my phone seems to have lost it. can you describe EXACTLY what you mean when you say partial match? I suspect that you're asking an entirely different question....

Not tested extensively, but this seems OK...

=IFERROR(INDEX(Sheet2!$B$1:$B$5,SMALL(IF(IF(ISERROR(SEARCH(Sheet2!$A$2:$A$5,Sheet1!B2)),FALSE,TRUE),ROW(Sheet2!$B$2:$B$5)),1)),"")

Array entered.

Ah sorry.. They are array formulae.

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...