IF And formula with cross sheet reference gives invalid operation error


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?




  • NickStaffordPM
    NickStaffordPM ✭✭✭✭✭
    edited 11/28/23

    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?



  • Paul Newcome
    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!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!