questforexcel
Board Regular
- Joined
- Jan 18, 2019
- Messages
- 128
- Office Version
- 2013
- Platform
- Windows
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))
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))