Hello,

I am trying to write an IF statement wherein of the first lookup returns the BLANK value, then i do another lookup. However getting the error. Note - Both the lookup are working if I write without IF statement. 

=if(Lookup(Box email without no domain,HW ADMP Report,ADMP Email without Domain,Final BU,false,false,true)=" ",if(lookup(Box Email Domain,HW Email Domain Reference List,Email Domain,Business Unit,false,false,true)),0,1)

Thank you for your responses.

Regards,

Bhawna

@Rhonda Keller

@Bhawna Singh,

Our Apptio CSM has advised us to break up the lookup from nested if statements. The complex IF and Lookup combined formula can cause heavy calculations with large dataset. We actually did a major cleanup of our tables and it did improve the overall calculations.

So I broke it out for you as follows. This should be much easier to decipher where it did not work. I believe you had an extra bracket in your original formula, plus you are missing the false condition in the IF statement.

I hope this helps. If you add the false condition, then it should work.

=If(LookupEmailwoDomain = "",LookupEmailwDomainFalse, false condition)

LookupEmailwDomain=Lookup(Box Email Domain,HW Email Domain Reference List,Email Domain,Business Unit,false,false,true)
LookupEmailwoDomain=Lookup(Box email without no domain,HW ADMP Report,ADMP Email without Domain,Final BU,false,false,true)
LookupEmailwDomainFalse=If(LookupEmailwDomain,0,1)

If I guess what you want to accomplish, then the complete formula should be as follows. I am assuming you want to do another lookup if it did not meet your first condition.

=If(LookupEmailwoDomain = "",LookupEmailwDomainFalse, LookupEmailwoDomain)

@Tony Wong - This is extremely helpful. Thank you so much. 

Hi @Bhawna Singh,

I totally second what @Tony Wong says about the performance impacts of nested IF statements esp. with lookups inside.

This kind of a "Order of Preference" lookup is a pattern I use a lot. Sometimes you may have multiple levels of lookup Here is how I do it:

Have your lookups in separate columns with good descriptive names like so:

LookupEmailwDomain = Lookup(Box Email Domain,HW Email Domain Reference List,Email Domain,Business Unit,false,false,true)
LookupEmailwoDomain = Lookup(Box email without no domain,HW ADMP Report,ADMP Email without Domain,Final BU,false,false,true)

LookupADUsername = THIS IS A FAKE THIRD function for the purpose of this example.

LookupXXUsername = THIS IS A FAKE FOURTH function for the purpose of this example.

Assuming you are trying to create a column name Business Unit, here is the pattern

Business Unit = if(LookupEmailwoDomain="",Business Unit Stage 1,LookupEmailwoDomain)

Business Unit Stage 1 = if(LookupEmailwDomain="",Business Unit Stage 2,LookupEmailwDomain)

Business Unit Stage 2 = if(LookupADUsername="",Business Unit Stage 3,LookupADUsername)

Business Unit Stage 3 = LookupXXUsername

:

:

and so on.

So the lookups are calculated first and the first non blank value is taken in the order of preference:

Business Unit

Business Unit Stage 1

Business Units Stage 2

Business Unit Stage 3

and so on.

I think I will christen this the "Order of Preference" Lookup Pattern.

Hope this helps.

Regards,

Sanjay Valiyaveettil

@Sanjay Valiyaveettil - Thank you for explaining and sharing the indepth information about the nested IF. very helpful.

Don't forget about leveraging the benefits of Tablematch, too.

https://tbmcouncil.jiveon.com/docs/DOC-5073

If you find yourself nesting If statements beyond one set, chances are good for a Tablematch solution.

I have found Tablematch is far more extensible, easier to understand, and sustain longer-term than nested If statements.