Nested Ifs

[questforexcel]

Hi All,

My Apologies, I am not able to download external content so would not be able to show my sheet or calculations. Will try my best to explain what I am trying to achieve and would appreciate your help.

I need to highlight variance across two data sets. Data columns are identical in the two sets.

I need two check two conditions within one formula. First, two check if one name is present in the other table. I have done that using the below formula

=IF(COUNTIF('Current Month data '!$A$2:$A$243,'Prior Month data'!$A176),"",'Prior Month data'!$A176)

Second, each of these names are assigned a value ranging from 0-1. I need to check if the counts in these two data sets are identical. I have done that using this formula :

=IF(VLOOKUP('Current Month data '!$A135,'Current Month data '!$A$2:$W$243,23,0)=VLOOKUP('Current Month data '!$A135,'Prior Month data'!$A$2:$W$243,23,0),"",'Current Month data '!A135)

NOW,

I am trying to combine both these IF Functions. I need my formula to check both these conditions and display a value if any of these conditions has been fulfilled. I tried using the below two formulas but could not succeed. Could you please help me on a better way to combine my formulas.

=OR(IF(COUNTIF('Current Month data '!$A$2:$A$243,'Prior Month data'!$A136),"",'Prior Month data'!$A136),IF(VLOOKUP('Current Month data '!$A135,'Current Month data '!$A$2:$W$243,23,0)=VLOOKUP('Current Month data '!$A135,'Prior Month data'!$A$2:$W$243,23,0),"",'Current Month data '!A135))


=(IF(COUNTIF('Current Month data '!$A$2:$A$243,'Prior Month data'!$A136),"",'Prior Month data'!$A136),IF(VLOOKUP('Current Month data '!$A135,'Current Month data '!$A$2:$W$243,23,0)=VLOOKUP('Current Month data '!$A135,'Prior Month data'!$A$2:$W$243,23,0),"",'Current Month data '!A135))

 

[NiMip]

Looks like your individual formulas are returning either a blank "" or a value from within the data (text I'm assuming, since you said a name?) depending on whether the IF test is true or false, respectively. What this means when you're using the OR statement is that it'll be seeing something like =OR("",textresult), neither of which will be registered as a fulfilment of the condition.

If all you want is a TRUE or FALSE result out of your combined formula, you just need the individual tests, and not the results you've returned in the individual formula. Try something like the formula below (will need to check I've got the tests correct to rightly return a TRUE or FALSE result):

=OR(COUNTIF('Current Month data '!$A$2:$A$243,'Prior Month data'!$A136)>0,VLOOKUP('Current Month data '!$A135,'Current Month data '!$A$2:$W$243,23,0)=VLOOKUP('Current Month data '!$A135,'Prior Month data'!$A$2:$W$243,23,0))

 

[questforexcel]

Thank you so much for your response will try with this

 

[NiMip]

Thank you so much for your response will try with this

How did you go? Any success?