Hi All!

I have read through the postings and have tried on my own...the results have
not been all that I would hope for....

Sheet A

Column A - a list of positions for planned resources
Column C - a list of positions for assigned resources
Column E - Department

Sheet B

Column D - a list of positions
Column F - the associated department for each position
_____________

Columns A and C have the same data in drop downs, but should not display
them at the same time. So if you have a planned resource (Chicken Plucker),
you enter the data in Column A; once the person has been assigned, you enter
their position in Column C.

I would like a formula on Sheet A in cell E1 so Excel would, on row 1, look
at cell A1 and do a lookup on Sheet B, finding the entry on Sheet A, cell A1,
and returning the associated department; if cell A1 on Sheet A is blank, then
the formula would look at cell C1 and do the same lookup. If both A1 and C1
are blank, E1 should be blank (versus an N/A or Value error)

This allows for a planned resource to be from one department, but if the
assigned resource is from a different department, the correct department will
still be in cell E1.

I have tried the SUMPRODUCT formulas I have, but they do counts versus
returning values. I tried 2 VLOOKUPs nested in an IF statement (along with
ISERROR arguments to eliminate the N/A or Value).....

Excel responded to both with the "You formula has an error" and when i
clicked ok, it highlighted the whole formula....telling me what Excel really
thought of my formula.

All help is greatly appreciated.
--
Greg