Substituting data from one column with required data from another column

[AlexeyNZ]

Good day everyone. I have one more question which I battle to solve. I have a column with various supplier stock codes (for example C-010, C-101, C-102). What I want is to create a column where those stock codes will be substituted with my own stock codes C-010 with G-15, C-101 with G-20, C-102 with G-25. 

Is anything I can use to speed up the process?

Stock table with supplier's codesMy table where I have allocated my own stock number to each supplier's code

[#2 · Accepted solution]

@AlexeyNZ 

You may try this...

=CHOOSE(MATCH(N2,{"C-010","C-101","C-102"},0),"G-15","G-20","G-25")

But it would be better to create a lookup table and use the VLookup formula to get the substituted value like this...

=VLOOKUP(N2,$U$2:$V$4,2,0)

Where $U$2:$V$4 is the Lookup Table.

Refer to the attached for more details.

[#3]

Brilliant, thanks a lot!

[#4]

@AlexeyNZ 

You're welcome! Glad it worked as desired.

Thanks for the feedback!