Adding a Custom Column based on an existing Column (EXPERT)

[Sean W.]

I need to add a new column based on an existing column of data.

The existing data is in the format below:

ID67
ID6802
ID6802
ID61
IA91
IA92
BMID75
BMID25

Each one of the data points will be grouped into different categories like
below,

ID67 = Red
ID6802 = Orange
ID6802 = Orange
ID61 = Pink
IA91 = Brown
IA92 = Brown
BMID75 = Black
BMID25 = Black

I would manually do it but there are a couple thousand rows. Also there are
about 400 different types of data ("IA91") that have an associated group
("Brown").

Can someone give me a formula for this that can be applied to multiple
spreadshirts once the association is established?

 

[Bernard Liengme]

Have a look in Help at VLOOKUP
Then come back if more questions
best wishes

 

[Sean W.]

I am recieving an error when I use the VLOOKUP Function. Below is the actual
formula I am using.

=VLOOKUP(O3,Sheet2!A3:B6:$B$8,2,TRUE)

With O3 Cell "ID65"

and the reference table on sheet2 being:
ID61 3
ID65 2
ID67 4
ID68 1

The error states a VALUE# error. Anyone know how to fix this problem?

 

[Pierre]

I am recieving an error when I use the VLOOKUP Function.  Below is the actual
formula I am using.

=VLOOKUP(O3,Sheet2!A3:B6:$B$8,2,TRUE)

With O3 Cell "ID65"

and the reference table on sheet2 being:
ID61    3
ID65    2
ID67    4
ID68    1

The error states a VALUE# error.  Anyone know how to fix this problem?






- Show quoted text -

Sean, a couple of thoughts:
Make sure there are no extraneous characters in your data; either in
the 1st data column, or in your value to be looked up column. The TRIM
function will remove unwanted spaces that can hose your lookup; if
they exist.
You might also try using the =TEXT(A1,0) etc.formula to make sure
your data is on the same playing field. Use another column to clean
up the data with that formula, and paste them back to their original
positions.

See if those might do the trick.

Pierre

 

[Pete_UK]

The table ranges are incorrect in your formula - I think you mean
this:

=VLOOKUP(O3,Sheet2!A3:B6,2,FALSE)

Hope this helps.

Pete