Lookup Help

[vzgeekgirl]

Hi everyone, new to this site and after looking at some of the responses, I know I'm in the right place as everyone is so knowledgable.
I'm a casual user of Excel 2003 trying to advance my skills so have taken on a project that will allow me do so. I have been using the lookup function and it's very useful, however, I've run into a situation where I think something else is needed and I'm stuck.
On the attached worksheet in Cell F2, I need to return a value based on the following: If Cell D2 is equal to Billable and cell E2 = Range Name Billing Departments", return the value "Y", otherwise, lookup cell B2 and if found return the value "Y" and if not found, return the value "N"
Does this make sense? I appreciate any help you can provide and maybe in the future, I'll be able to help someone else.

[NBVC]

Try:

=IF(OR(AND(D2="Billable",ISNUMBER(MATCH(E2,Billing_Departments,0))),ISNUMBER(MATCH(B2,Associates,0))),"Y","N")

[vzgeekgirl]

WOW! I didn't expect an answer so quickly. I appreciate the answer and while this resposne did in fact return answers I could use, I need to clarify my original question. I only want to retun a value of Y if cell D2 is Billable. So if D2 is billable, continue on with the rest of the equation, but if cell D2 is anything other than billable, return a value of "N".

[NBVC]

Try:

=IF(D2="Billable",IF(OR(ISNUMBER(MATCH(E2,Billing_Departments,0)),ISNUMBER(MATCH(B2,Associates,0))),"Y","N"),"N")

[vzgeekgirl]

Works perfectly. Can you explain what adding the "N" at end of this does? Trying to understand the breakdown of the formula.

Try:

=IF(D2="Billable",IF(OR(ISNUMBER(MATCH(E2,Billing_Departments,0)),ISNUMBER(MATCH(B2,Associates,0))),"Y","N"),"N")

[FDibbins]

yes or no perhaps?

[NBVC]

Works perfectly. Can you explain what adding the "N" at end of this does? Trying to understand the breakdown of the formula.

The N at the end is for the first IF.. .so if D2 does not equal "Billable", the formula skips the inside IF function and immediately returns N... if D2 is Billable, then it goes to the other IF and checks for matches...

[vzgeekgirl]

The N at the end is for the first IF.. .so if D2 does not equal "Billable", the formula skips the inside IF function and immediately returns N... if D2 is Billable, then it goes to the other IF and checks for matches...

Thanks so much for the information. That makes more sense to me than the other post - "Yes or No perhaps"
I really appreciate your help

[vzgeekgirl]

I marked this as unsolved so I could continue on with asking questions. So I woke up at 3:00 AM thinking about this and thought of another scenerio that could appear. I've attached another Excel file.
The equation is the same as the original with one change, If D2 is billable and E2 is in the named range, return a "Y" other wise return "N". But also, if E2 isn't found in the named range, look for a match between the project and name and the same information on the billing exclusions worksheet and return a "Y" if found, otherwise return "N".

Bottom line is I'm trying to determine what hours should be billable based on either the billing department or an exception which would be a project and name combination.

[jealkon]

I'll be interested to see the next response as I haven't figured out how to do this (by no means am I anywhere near most of these people, they are amazing with what they know)
Good luck

[vzgeekgirl]

Moving to top to see if someone can help

[NBVC]

Sorry, I was away on a conference yesterday.....

is this what you mean?

Please Login or Register  to view this content.

it will return Y if D is "Billable" and either of these are true:

The Dept is found in the Billing_Departments range.
The Name is found in Associates range
The combination of Project and Name is found in the Project/Associate range.

[vzgeekgirl]

Yes - that seems to work perfectly.
Hope you were away and enjoyed yourself. Sometimes, conferences can be a real bore