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

Hello

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

Foudou

excel

Lorenzo
Jul 08, 2021
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
XL_MapCodeToCity.xlsx18 KB

4 Replies

Lorenzo
Silver Contributor
Jul 08, 2021
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
XL_MapCodeToCity.xlsx18 KB
- foudou
  Copper Contributor
  Jul 14, 2021
  Youpi ! It works !
  Thank you so much
  Have a nice day !
  - Lorenzo
    Silver Contributor
    Jul 14, 2021
    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
- foudou
  Copper Contributor
  Jul 14, 2021
  Lorenzo
  Thank you very much.
  I will try that this afternoon and I let you know if it works.
  Kind regards
  Foudou

Forum Discussion

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