multiple IF statements

[Paul Naylor]

Multiple IF AND statement with 3 conditions

Help?

Column J is a VLOOKUP Column C to another worksheet Master V7 File

=VLOOKUP(C1,'Master V7 File'!$A:$A,1,FALSE)

2 other columns ... column O with Text , column U with values "Y" or "N".

Trying to do a multiple IF(AND statement ....

Formula in Cell Y2....
IF Cell J2 = "#N/A" and Cell O2 "Processed" and Cell U2="N" then Y2 = "Y" , Else "N"

 

[KRice]

In cell Y2, try...
=IF(AND(ISNA(J2),O2="Processed",U2="N"),"Y","N")

 

[Paul Naylor]

That's great works a treat. Hoping help me out with another issue on same spreadsheet I.e. multiple IF(OR where 2 columns are output of VLOOKUP on 2 separate sheets . Getting #N/A results where value of either of VLOOKUP's is N/A

FORMULA in Cell Y2

Conditions : IF J2 or S2 ="True" or U2 = "Y" then result ="Y" , else "N" some of the results are displaying #N/A, not sure how to force "N" to be displayed if J or S is "#N/A?

 

[KRice]

Glad to help. Where you want the result...assuming perhaps Y2...enter this:
=IFERROR(IF(OR(J2="TRUE",S2="TRUE"),"Y","N"),"N")
There are no doubt other ways to do this. This approach is checking for "true". If you have some errors, one way to make an error trap is to wrap the entire function with the IFERROR function and then specify what should be returned if an error is encountered. Here that instruction is to return an "N". This will, however, execute and return an "N" for any type of error, not just the #N/A error. Is that okay, or do you want to specifically check for the #N/A error only?

 

[Paul Naylor]

Thanks for taking the time to reply, missing a condition OR U2="Y". Tried with other 2 conditions both results are output from vlookups and regardless if "True" or "N/A" getting a #Name? Error. Perhaps should have mentioned that @work were still working with Excel 97 !!!

Not sure if solution lies in getting VLOOKUP to output Yes or No to avoid #N/A error ????

VLOOKUp's formulas in format

=IF(VLOOKUP($C2,'S:\DPU\Broken Orders\Daily Broken Orders ASR\[Daily Broken Orders ASRs MASTER V1 File.xls]Master ASR V1 File'!$B:$F,1,FALSE)=$C2,"True","Not Found")

 

[KRice]

Hi Paul,
Yes, I think that would be best...to address the error where it first occurs. I thought Excel 97 (yikes!) had the IFERROR function? If not, then try wrapping the entire VLOOKUP with a slightly more convoluted IF function that uses the ISERROR function or the ISNA function inside of it. The basic structure would be:
=IF(ISERROR(insert function giving error),"N",insert function giving error)
By wrapping the error generation inside this IF statement, the IF(VLOOKUP functions are executed, and the result is generated normally. That result is then looked at by the ISERROR or ISNA function and if the result submitted to that function results in a TRUE (meaning no error codes are seen) then the ISERROR or ISNA evaluates as FALSE, which then returns the 2nd item (where I've typed "insert function giving error"...but in that case, the function wouldn't actually be giving the error). And if the IF/VLOOKUP function were to result in an error, then this IF(ISERROR or IF(ISNA will result in TRUE and return the 1st listed item, which is "N".

Sorry about missing the OR U2...just add that inside the OR statement, separated from the other with a comma.
=IF(OR(J2="TRUE",S2="TRUE",U2="Y"),"Y","N")

And the VLOOKUP error trap version looks similar to this:
=IF(ISERROR(IF(VLOOKUP($C2,'S:\DPU\Broken Orders\Daily Broken Orders ASR\[Daily Broken Orders ASRs MASTER V1 File.xls]Master ASR V1 File'!$B:$F,1,FALSE)=$C2,"True","Not Found")),"N",IF(VLOOKUP($C2,'S:\DPU\Broken Orders\Daily Broken Orders ASR\[Daily Broken Orders ASRs MASTER V1 File.xls]Master ASR V1 File'!$B:$F,1,FALSE)=$C2,"True","Not Found"))
Or possible use ISNA in place of ISERROR.

Also, you should probably update your profile here to reflect the Excel version(s) that you are using.

 

[Paul Naylor]

Hi KRice, works a treat . Need to then if results of a VLOOKUP = "True" display the adjacent cell in the Vlookup table but if "False" display "N/A" trying to adapt your earlier solution but keeps throwing up a #N/A error and not sure why?

Original formula using :

=IF(J2="True",VLOOKUP($C2,'Master File V7'!$A:$B,2,FALSE))

Adaptation

=IF(J2="True",ISERROR(VLOOKUP($C2,'Master File V7'!$A:$B,2,FALSE)),"N/A",VLOOKUP($C2,'Master File V7'!$A:$B,2,FALSE))

Not sure where I've gone wrong?

 

[KRice]

This part of your formula:
VLOOKUP($C2,'Master File V7'!$A:$B,2,FALSE)
uses C2 as the lookup value for search column A of the 'Master File V7' sheet. Since you are searching for an exact match (the FALSE option), values in column A do not need to be in any particular order; however, only the first match will be found...and when found, the corresponding value in column B (the 2nd column of the range because of the "2" argument) will be returned. So if this is what you want, then there is no issue with that part of the formula. But you've wrapped that formula with an ISERROR, which will return a FALSE if a value is found (because the VLOOKUP did not generate an error)

So the entire function first evaluates whether J2 is TRUE. If so, then if I understand correctly, you want the VLOOKUP function to execute and return a value if one exists or an N/A otherwise.
But what should happen if J2 is not TRUE? Do you also want an N/A returned for that condition?

If so, then you might consider something like this:
=IF(AND(J2=TRUE,NOT(ISERROR(VLOOKUP($C2,'Master File V7'!$A:$B,2,FALSE)))),VLOOKUP($C2,'Master File V7'!$A:$B,2,FALSE),"N/A")

This checks to ensure that both J2 is true and that the VLOOKUP function does not generate an error (or put differently, VLOOKUP finds a matching value). When both of those conditions are met, then the result of the VLOOKUP function is returned. Under any other scenario (either J2 is not true or the VLOOKUP generates an error), then "N/A" is returned.

 

[Paul Naylor]

Hi KRice

Thats fantastic !!!! With youre help we've managed to virtually eliminate all the unnecessary checks which in turn increased our productivity by 100%+.

Couldn't have done it without youre help ???

 

[KRice]

Glad to help! Thanks for the update.