Vlookup or if then

[Jenny]

I have a spreadsheet that has a column with company
numbers. I want to be able to associate another category
to these numbers. So, if the data in column D equals
06118, 30027, 30019, 30020 then return the value "Misc"
in column E. Or if the data in column D equals 11111,
33333, 55555 then return the value "Stuff" in column E.
Does anyone know how I would do this?

 

[Frank Kabel]

Hi
one way:
1. Create a separate table with your associated values.
e.g. call this sheet lookup:
A B
1 06118 Misc
.....

Now on your other sheet use the formula
=VLOOKUP(D1,'lookup'!$A$1:$B$20,2,0)

 

[Dave R.]

One way is to create a table of the values and associated categories.
e.g.

C1 D1

6118 Misc
30027 Misc
30019 Misc
11111 Stuff
33333 Stuff
xxxxx Stuff


Then use either vlookup or index/match such as, with a a number in A1

=INDEX(D$1$10,MATCH(A1,C$1:C$10,0))

will return misc, stuff, etc. based on value in A1.

 

[Norman Harker]

Hi Jenny!

You can use VLOOKUP for this using the 4th argument of FALSE

That gives you (eg)

Column H is formatted as text

H1:I7
06118 Misc
30027 Misc
30019 Misc
30020 Misc
11111 Stuff
33333 Stuff
55555 Stuff

D1 is formatted as text

E1:
=VLOOKUP(D1,$H$1:$I$7,2,FALSE)