How to get results depending of a text in other column?

**rwilson91** · 10-09-2013, 05:32 AM

Attached is my excel file, what i am trying to do is in column P.
Depending on the supplier, column I, I need an abbreviation in column P, example;
if Axis PEC is the supplier I want PEC to appear in column P,
if Farnell is the supplier then I want FA in column P,
if supplier is FIRST4MAGNETS, I want F4M in column P

the possibility could be 150+ different suppliers though so obviously can’t do it with a IF stament, which is the only way I know so that’s out the question, is this possible and if so, how can I, or am I expecting too much from excel this time?

if you can help me would be forever grateful.

side note, i would be wanting to do a similiar thing in column Q, to generate either letter B or M depending on the supplier. but i figure once my original question is answered i can rework it to do this as well.

thanks for reading

EDIT: sorry if this is in the wrong area.
-----------------

well i'd moan that im gettin told my title sucks but you gave me the perfect solution to my problem so i cant moan.

sorry for my lack of sticking to rules, in a rush to get an answer, which as i said very grateful for, thank you.

and i see you're from warrington, which is where i currently live, but nvm about that.

thanks again.

**Pete_UK** · 10-09-2013, 05:43 AM

First of all, your title sucks - we can see that you are a new guy as you only have 1 post. Also, you have posted in a help forum, so it is obvious that you need help. Read the Forum Rules - titles should describe what your problem is, so please change it to something more descriptive.

You need to set up a table somewhere (it could be in Sheet2) with a list of all your supplier names in one column and a list of the abbreviations that you want to use. Suppose this is in columns A and B in Sheet2. Then you can use this formula in P5 of Sheet1:

=IFERROR(VLOOKUP(I5,Sheet2!A:B,2,0),"")

then copy down. If you use column C in Sheet2 for your other category, then you can do this in Q5:

=IFERROR(VLOOKUP(I5,Sheet2!A:C,3,0),"")

then copy that down.

Hope this helps.

Pete

**TMS** · 10-09-2013, 05:46 AM

You could list all your suppliers in a Table on a separate sheet. For example, on Sheet2, column A could have the Supplier name and column B could have the code.

You could then use VLOOKUP to return the appropriate code.

For example,

Formula:

Please Login or Register  to view this content.

And you could use the list of Suppliers in Sheet2, column A as a Data Validation List to ensure that you only select valid supplier names.

Regards, TMS

**Andy Pope** · 10-09-2013, 05:48 AM

@MODS, This is my second attempt at replying to this post. The first one, which included the standard Thread Title message appears to have gone.
I will not repeat the advice Pete_UK provided the OP about rules.

The attached includes a lookup table on sheet2.
Lookup formula in column P

=VLOOKUP(I11,tblSUPPILERS,2,FALSE)

in column Q

=VLOOKUP(I11,tblSUPPILERS,3,FALSE)

**rwilson91** · 10-09-2013, 06:12 AM

Originally Posted by Pete_UK

First of all, your title sucks - we can see that you are a new guy as you only have 1 post. Also, you have posted in a help forum, so it is obvious that you need help. Read the Forum Rules - titles should describe what your problem is, so please change it to something more descriptive.

You need to set up a table somewhere (it could be in Sheet2) with a list of all your supplier names in one column and a list of the abbreviations that you want to use. Suppose this is in columns A and B in Sheet2. Then you can use this formula in P5 of Sheet1:

=IFERROR(VLOOKUP(I5,Sheet2!A:B,2,0),"")

then copy down. If you use column C in Sheet2 for your other category, then you can do this in Q5:

=IFERROR(VLOOKUP(I5,Sheet2!A:C,3,0),"")

then copy that down.

Hope this helps.

Pete

well i'd moan that im gettin told my title sucks but you gave me the perfect solution to my problem so i cant moan.

sorry for my lack of sticking to rules, in a rush to get an answer, which as i said very grateful for, thank you.

and i see you're from warrington, which is where i currently live, but nvm about that.

thanks again Pete_UK

**Pete_UK** · 10-09-2013, 09:11 AM

Well, let's face it - "New guy needs help" doesn't say anything about your problem, so thanks for changing it and I'm glad the solution worked for you.

VLOOKUP is a very useful function, and I'm sure you will have cause to use it many times in the future.

Pete