Yipee! After much fiddling, I came up with a formula that does what I
need...however (there is always a however, isn't there?) when both A1 and C1
are blank, I get an #N/A error. I have tried to put the formula within an
ISERROR statement, but then I get the formula error message from Excel.

Ideas?

Here is the formula that seems to be working:

=IF(ISBLANK(A13),VLOOKUP(C13,Dept!$D$3:$F$24,3,FAL SE),VLOOKUP(A13,Dept!$D$3:$F$24,3,TRUE))

If I have a postion in A1 and C1 is blank, I get the department: if I have a
position in C1 and A1 is blank, I get the department. When both are blank, I
get a #N/A error; if both are filled (a no-no as far as using the sheet), I
get the department.
--
Greg


"Greg in CO" wrote:

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