Forum Discussion

Anıl Adaş's avatar
Anıl Adaş
Brass Contributor
Oct 05, 2018

Change the word in a cell automatically with its related word pls help..

Hi,   I have locations and codes of these locations and I need to change automatically the code instead of its location when I write a location in a cell.   For examle,   Location: Bamian Code...
excel
Formulas and Functions
Macros and VBA
Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Oct 05, 2018

Hi,

 

You can do this by using VLOOKUP.

But there are some locations have multiple codes!

Which code do you want to return? Do you want to return them all?

  • Anıl Adaş's avatar
    Anıl Adaş
    Brass Contributor
    Oct 05, 2018

    I want to return like below;

     

    Location at A2 must return to Code at B2

    Location at A3 must return to Code at B3

    • Haytham Amairah's avatar
      Haytham Amairah
      Silver Contributor
      Oct 05, 2018

      Sorry, but this is still not clear!

       

      Let's say that you have the Location column in column A, and the Code column in column B as the example file you attached.

      And some locations have multiple codes such as "SAN SEBASTIAN".

       

      Anyway, you can use VLOOKUP in cell E2 as below:

      =VLOOKUP(D2,A2:B7569,2,0)

       

      But please note that VLOOKUP in case of duplicate lookup value will return only the code of the first matched location which is ARSSN for the location of SAN SEBASTIAN.

       

      If you want to return the code based on the cell address you have to provide the cell address of the location instead of the location name as below:

      =INDEX(B2:B7569,MATCH(D2,INDEX(ADDRESS(ROW(A2:A7569),1,4),),0))

Resources