VLOOKUP, IF, SUMPRODUCT...oh my!

[Greg in CO]

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 in CO]

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,FALSE),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.

 

[Bernie Deitrick]

The "General Form" is

=IF(ISERROR(..old equation..),"value to return if error",..old equation...)

so, using your formula:

=IF(ISERROR(IF(ISBLANK(A13),VLOOKUP(C13,Dept!$D$3:$F$24,3,FALSE),VLOOKUP(A13,Dept!$D$3:$F$24,3,TRUE))),"value
to return if
error",IF(ISBLANK(A13),VLOOKUP(C13,Dept!$D$3:$F$24,3,FALSE),VLOOKUP(A13,Dept!$D$3:$F$24,3,TRUE)))

unless you are using Excel 2007, which can handle errors more efficiently.

HTH,
Bernie
MS Excel MVP

 

[Pete_UK]

You could do it like this:

=IF(AND(A13="",C13=""),"",IF(A13="",VLOOKUP(C13,Dept!$D$3:$F
$24,3,0),VLOOKUP(A13,Dept!$D$3­:$F$24,3)))

Interesting that you have FALSE as the 4th parameter for the first
lookup, but TRUE for the second lookup.

Hope this helps.

Pete

 

[Greg in CO]

Hi All!

Thanks so much for your help...the folks on this forum are just amazing when
it comes to helping...just super!!!!

I am trying each formula to see which one is the best fit.

Roger,

When I paste yours in, then copy it to other cells, it works for two cells,
then starts returning #N/As in later cells.....very odd. I have checked and
only the cell refs on the active sheet are changing....the fixed refs for the
other sheet seem fine.

Here are the formulas as pasted in and the results:

=IF(AND(ISBLANK(A14),ISBLANK(C14)),"",IF(ISBLANK(A14),VLOOKUP(C14,Dept!$D$3:$F$24,3,FALSE),VLOOKUP(A14,Dept!$D$3:$F$24,3,TRUE)))

Results: Both A14 and C14 are blank, returns a blank cell instead of an
error.

=IF(AND(ISBLANK(A15),ISBLANK(C15)),"",IF(ISBLANK(A15),VLOOKUP(C15,Dept!$D$3:$F$24,3,FALSE),VLOOKUP(A15,Dept!$D$3:$F$24,3,TRUE)))

Results: Both A15 and C15 are blank, returns a #N/A error

I've tried pasting special, just the formula, then pasting normally. It's
just odd.

I'll post results for the other formulas as well.

 

[Greg in CO]

Bernie, your formula appears to work fine. Thanks!

 

[Greg in CO]

Pete, your formula works fine as well...thanks!!!!

 

[Greg in CO]

As an FYI, the testing was:

Both A13 and C13 blank
Both A13 and C13 filled (all formulas returned the Dept value for A13..this
would be an invalid user entry, as it indicated that you had a planned and an
assigned resource using the same hours)
A13 filled
C13 filled

Thank again to all the gurus her who take the time to help...I have learned
so much from you folks!!!!

)

 

[Pete_UK]

You're welcome, Greg - thanks for feeding back.

Pete