0

I have three different formulas that I would like to combine into one formula;

In cell I2 : =IF(ISNA(MATCH(CONCATENATE(A2,D2),INTRAFREQNCELL!E:E,0)),"NO","YES")

In cell J2 : =IF(ISNA(MATCH(CONCATENATE(A2,D2),INTERFREQNCELL!E:E,0)),"NO","YES")

In cell K2 : =IF(I2=J2,"NO","YES")

The formula in cell I2 and cell J2 look for matches in two separate sheets and return a YES if there is a match, or NO if there is no match.

The formula in cell K2 returns a YES if cell I2 and cell J2 match.

What I want to do is to get rid of columns I and J by combining the formula in all three cells.

I've tried using the INDEX function to do this but it didn't work. Is this possible?

  • Formula in Cell I2 & J2 are identical therefore use any one and after that Formula in Cell K2 is not required !! – Rajesh S Dec 4 '18 at 9:52
  • Y don't you share some sample data with us and so that we just fix the issue in better way. – Rajesh S Dec 4 '18 at 9:53
  • 1
    @RajeshS they are looking at different sheets. Cell I2 is looking at INTRAfreqncell and cell J2 is looking at INTERfreqncell – aab Dec 4 '18 at 10:19
  • @fixer1234 it's not a typo, I2 is looking at INTRAfreqncell and cell J2 is looking at INTERfreqncell – aab Dec 4 '18 at 10:24
  • ,, yeah in first look both sheet names looks similar !! ☺ – Rajesh S Dec 4 '18 at 10:47
2

There isn't enough information to see if your formulas work properly, so I'll just address combining them.

In general, if a formula contains a reference to the result in another cell, you can usually just replace the reference with the formula (excluding the initial =) in the referenced cell, as long as it's not an array formula (displayed with curly braces around it).

Doing that with K2 would yield:

=IF(IF(ISNA(MATCH(CONCATENATE(A2,D2),INTRAFREQNCELL!E:E,0)),"NO","YES")=IF(ISNA(MATCH(CONCATENATE(A2,D2),INTERFREQNCELL!E:E,0)),"NO","YES"),"NO","YES")

However, you can do better than that with your formulas because each one tests for a True/False condition, and then just displays the result as Yes/No, and you're using the same logical direction for each one. So you can simplify what's copied from I2 and J2 to just compare the True/False result:

=IF(ISNA(MATCH(CONCATENATE(A2,D2),INTRAFREQNCELL!E:E,0))=ISNA(MATCH(CONCATENATE(A2,D2),INTERFREQNCELL!E:E,0)),"NO","YES")

Since your output is just No/Yes, you could simplify it even more if True/False would suffice rather than Yes/No. You can just use the logical result of the comparison.

It appears your No and Yes may be reversed, showing "No" if I2 and J2 match and "Yes" if they don't). So just test for = or <>, depending on which you want to show as "True":

=ISNA(MATCH(CONCATENATE(A2,D2),INTRAFREQNCELL!E:E,0))=ISNA(MATCH(CONCATENATE(A2,D2),INTERFREQNCELL!E:E,0))

or

=ISNA(MATCH(CONCATENATE(A2,D2),INTRAFREQNCELL!E:E,0))<>ISNA(MATCH(CONCATENATE(A2,D2),INTERFREQNCELL!E:E,0))
  • #Fix1234,, it's working you get 10 ☺ – Rajesh S Dec 4 '18 at 10:55

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.