automatic filling of a text of in a cell according to a series of letters of an adjacent cell,

New Contributor


I'm trying to find a formula that allow me to automaticaly fill a cell with name of places (Copenhagen DK or Aarhus DK) according to a series of letters of an adjacent cell.

Copenhagen, DK DK2575722021021720234 E000005900100000000
Aarhus, DK DK2622302019102621107 E000005900200000000

When the SEC code begins by 'DK25757', it's meen that it's Copenhagen, but when it's 'DK26223' it's Aarhus.

Actualy, I do that manualy but it is sources of errors. I would like that when I past de SEC code in the cell, automatically, the cell I choose fill in with the right city.

Somebody can help me ?

I have tryed with IF(), IF(or()), IF(AND()), but it doesn't worked.

Thank a lot


4 Replies
best response confirmed by allyreckerman (Microsoft)

Hi @foudou 

You need to create somewhere in your workbook a mapping table as the grey one below




Then, according to the above mapping table, formula in B2:

=VLOOKUP(LEFT(A2,7), D$3:E$6, 2, FALSE)

 Corresponding sample attached

@L z. 

Thank you very much.

I will try that this afternoon and I let you know if it works.

Kind regards


Youpi ! It works !
Thank you so much
Have a nice day !
You're welcome. When you have a minute, there a link to mark as Response on each reply you get => Can help those who search for existing solution. Thanks & Nice day too