JulieD wrote:
Hi Kara

sounds like a VLOOKUP should do what you want, assuming your table
containing the department, code & description is on Sheet3 range A2:C100.

and the cell that the Help Desk is filling in the department on is cell A2
on sheet1, and you want the appropriate code & description filled in in
cells A2 and A3 respectively on Sheet2.

the formula for Sheet2 A2 would be
=VLOOKUP(Sheet1!$A$2,Sheet2!$A$2:$C$100,2,0)
and for Sheet 2 A3
=VLOOKUP(Sheet1!$A$2,Sheet2!$A$2:$C$100,3,0)

hope this helps


The above description seems to be confusing the sheets, and
unnecessarily using Sheet2!$A$2:$C$100 for a formula on Sheet2, where
the reference to Sheet2! is redundant. In any event, assuming as stated
above that the table is on Sheet3 range A2:C100 and that the formulas
are to be on Sheet2 in ranges A2 and A3 (and, although it didn't so
state but the formula provided suggests, that the lookup value is on
Sheet1 range A2), you can also do this with a single formula, without
array entering (see my previous response in this thread, although to
return the code and description to two adjoining cells in the same
column you would use a semicolon instead of a comma as the separator of
the column references):

=INDEX(VLOOKUP(Sheet1!$A$2,Sheet3!$A$2:$C$100,{2,3 },0),ROW(A1)) in
Sheet2 A2 and copy down to A3

The above confusion illustrates how helpful it can be when the original
poster gives a little more detail about where the data resides and where
the output is desired. The responder then can use the data furnished
rather than making up a little test case and trying to keep it all
straight as the solution is tested.

Alan Beban