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: Afbin

 

I want to see the code(Afbin) even if I write the location(Bamian). 

 

I added an example file to understand easy. If you know any way to make it possible, pls help me.  

excel
Formulas and Functions
Macros and VBA

3 Replies

  • 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