Why is this IF function giving an Invalid Operation error?

Mross0878

I have this formula:

=IF({Transaction Satisfaction Survey - Quarter} = Quarter@row, "Response Received", "No Response Received")

The formula is very straight forward and the cross-sheet reference is calling the Quarter Column in a second sheet. I can't seem to get the formula to not provide the #Invalid Operation error. can someone tell me what I did wrong? The operators look fine to me, but thats what the error article says is the probable cause.

Thanks!

Paul Newcome

So basically… If there is a row on the other sheet that has the same [First Name], [Last Name], and Quarter, you want to output "Match". Otherwise you want to output "No Match"? If that is correct, try this:

=IF(AND(Quater@row <> "", [First Name]@row <> "", [Last Name]@row <> ""), IF(COUNTIFS({Quarter}, @cell = Quarter@row, {First Name}, @cell = [First Name]@row, {Last Name}, @cell = [Last Name]@row) > 0, "Match", "No Match"), "Issue")

Paul Newcome

What exactly are you wanting to accomplish?

Mross0878

Hello @Paul Newcome,

Thanks for reaching out. After looking back at the problem I realized I needed a bit more. That original Formula was not right at all. So, ignoring that initial one, can we discuss this one:

=IF(AND(NOT(ISBLANK(Quarter@row)), NOT(ISBLANK([First Name]@row)), NOT(ISBLANK([Last Name]@row))), IF(AND(INDEX({Transaction Sat - Quarter}, MATCH(Quarter@row, {Transaction Sat - Quarter})) = Quarter@row, INDEX({Transaction Satisfaction Survey - First Name}, MATCH([First Name]@row, {Transaction Satisfaction Survey - First Name}, 0)) = [First Name]@row), "Match", "No Match"), "Issue")

Here is the problem I am trying to solve for: I am trying to use this formula to look for a row in the second sheet that matches the First Name, Last Name, and Quarter columns to the row on the first sheet. Both sheets have the same columns names for these fields. The formula above works partially, the formula searches the appropriate sheet and finds the correct information, however it's not looking at it row by row, it's instead looking at the entire data set and identifying a match if the data exists, regardless of whether it's the same row or not. I am not sure how to make it look at the data row by row and not return a match if the data is not all present in one row. I hope that makes sense.

Thanks!

Mike

Paul Newcome

So basically… If there is a row on the other sheet that has the same [First Name], [Last Name], and Quarter, you want to output "Match". Otherwise you want to output "No Match"? If that is correct, try this:

=IF(AND(Quater@row <> "", [First Name]@row <> "", [Last Name]@row <> ""), IF(COUNTIFS({Quarter}, @cell = Quarter@row, {First Name}, @cell = [First Name]@row, {Last Name}, @cell = [Last Name]@row) > 0, "Match", "No Match"), "Issue")

Mross0878

Paul,

You are a master. Thank you. I looks like its working. I am not sure I would have ever gotten to this on my own. Can you help me understand why it needs to be

 {Last Name}, @cell = [Last Name]@row

instead of something like

 {Last Name}@cell, = [Last Name]@row

Thanks!

Mross0878

Apologies Paul, I do have one more step for the formula. I need to check for blanks on the second sheet instead of the first. Can I do this:

=IF(AND({Quarter} <> "", {First Name} <> "", {Last Name} <> ""), IF(COUNTIFS({Quarter}, @cell = Quarter@row, {First Name}, @cell = [First Name]@row, {Last Name}, @cell = [Last Name]@row) > 0, "Match", "No Match"), "Issue")

instead of your original:

=IF(AND(Quater@row <> "", [First Name]@row <> "", [Last Name]@row <> ""), IF(COUNTIFS({Quarter}, @cell = Quarter@row, {First Name}, @cell = [First Name]@row, {Last Name}, @cell = [Last Name]@row) > 0, "Match", "No Match"), "Issue")

Paul Newcome

You shouldn't need to check for blanks on the second sheet. If there are blanks in the second sheet, the row containing the blank won't be grabbed by the COUNTIFS because a blank doesn't equal text.

So if Quarter@row is "Q4", a row in the other sheet that is blank in the Quarter column won't equal "Q4" which means it won't get included in the COUNTIFS which in turn means it won't trigger a false "Match".

Mross0878

Thank you @Paul Newcome,

Just to make sure I fully understand the formula you wrote, can you review my plain English explanation and let me know if I am reading it right?

This formula is checking to make sure the First Name, Last Name, and Quarter cells in this row are not equal to NULL, and if that is true, its then going to <the second sheet>, grouping all of the rows that have the Quarter, First Name, and Last Name column data that matches the data in <the first sheet>'s Quarter, First Name, and Last Name field of that row. If it all matches, the formula returns Match, and if not, it returns No Match. If any of the fields are blank on <the first sheet>, it returns Issue.

I still am hazy on the @cell portion in the nested IF, after the cross-sheet references. Is that just telling it to look at the specific cells in the column and thats why I was getting the false positives in my original formula?

thanks!

Paul Newcome

You are correct with all of it. Both how the formula is working as well as @cell.