IF And formula with cross sheet reference gives invalid operation error

Judith Pappler

Hi team,

This is probably so obvious but i don't manage to get the following formula to work:

=IF(AND({EMEA KPI General data Country} = Country@row, {EMEA KPI General data Payroll month} = Month@row), {EMEA KPI General data Reason for manual pay})

I get error invalid operation. Can you help?

Thanks

Judith

NickStaffordPM

Most Likely Cause: I don't think you have an "if wrong" action to complete the formula based on what you have posted.

Expression: AND({EMEA KPI General data Country} = Country@row, {EMEA KPI General data Payroll month} = Month@row)

If Correct: {EMEA KPI General data Reason for manual pay}

If Wrong: ???

Less Likely Cause: Also could be that AND and OR statements typically return TRUE or FALSE, so maybe you need you specify which you want the Value True option to return IE = True or = Fa'se after the expression

Let me know if this does not help

Judith Pappler

Hi Nick,

thanks for the quick reply. I had left the if wrong space empty because according to the formula help pop up it is optional. I have added a "" after the comma for the case that it is wrong (so to show a blank) but it does not change the error.

What I want to happen is that if the country and the month of the other sheet match the current sheet row then I want the value of the reference field to be displayed. Should i maybe use a different formula for it? I was thinking of vlookup but it depends on two different cells and i don't want to add an extra column with a combination code of country and month.

Any ideas?

Thanks

Judith

Paul Newcome

You are going to need a different formula altogether. An INDEX/COLLECT should work for you.

=INDEX(COLLECT({EMEA KPI General data Reason for manual pay}, {EMEA KPI General data Country}, @cell = Country@row, {EMEA KPI General data Payroll month}, @cell = Month@row), 1)

Judith Pappler

fantastic, this works, thanks so much!!!

Paul Newcome

Happy to help. 👍️