VLOOKUP, ONE TO MANY

[lehigh46]

Hi All,


I have two sheets.
The first sheet, which we'll call "WORK TYPES" has two colmuns.
Column A has a work type number followed by a comma and column B has
the description associated with the number. Each row is a single work
type.

Example:

15, Pavement Markings
16, Guide Rail
17, Signs
18, Fence
19, Seeding

The second sheet, we'll call "CONTRACTORS" where column A contains the
contractors name, column B where I would type in the work type numbers
and column C which would contain a VLOOKUP fomula to find the worktype
description from the "WORK TYPES" sheet.

Now suppose one contractor does more that one type of work. How would
I write the formula?

Example of desired results:

AJAX, Inc. 16, 17, 18, Guide Rail, Signs, Fence



Thanks for any help that you can give me.

Tom

 

[T. Valko]

You would need to use a separate cell for each work type and a separate
lookup formula for each of those cells.

 

[Keith Faulconer]

I don't know if this is the optimal solution, but I think it would work.

I don't think you can enter the Work Types in the same cell. I would put
each one in a different cell. Is there a max number of Work Types a
contractor would do? If so, make that the number of columns to enter the
work type vlookup functions. I would also enter in a cell that tells you
how many the contractor is doing. For example, if the most a contractor
would be doing would be 5, you could have the contractor in column A, the
number of Work Types in column B, and the possibility of entering a work
type in columns c through g (the 15,; 16,; etc). I would enter the formula
in column h that would say
=if(c2<>"",vlookup(c2,jobtype,2,0),"")

in column h, enter

=if(d2<>"",vlookup(d2,jobtype,2,0),"")

and so on.

This says that if nothing is in the cell with the job type, to leave it
blank, but if there is something there to lookup the decsription for the job
type.

Hope that helps. If I confused you further, email me back at
(e-mail address removed)