Hello -

I have multiple sheets set up to feed into 1 result set and am trying to find a way to pull information from the result set into a table, if the criteria is met. Here is what I have, however, I keep getting #INVALID OPERATION.

=IF({BCOA Range 11} = "YES", AND({BCOA Range 10} = "LCB", INDEX({BCOA Range 12}, MATCH([Column3]1, {BCOA Banking Center #}, 0))))

Also, I will need to add on to this for another scenario as well -i.e. =IF({BCOA Range 11} = "YES", AND({BCOA Range 10} = "LTCF", INDEX({BCOA Range 12}, MATCH([Column3]1, {BCOA Banking Center #}, 0))))...

HELP! please.. :-)

Thank you in advance..

Best Answer


  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi @Susan Albers

    Not sure I follow what you intend to do, but there's many errors here:

    1. In both your formulas I believe the AND is wrongly placed. I guess it's part of the criteria for the IF function, so it should be like this: =IF(AND(Criteria 1, Criteria 2, ...)
    2. In both case you're trying to check the value of a full range. You cannot do that. {BCOA Range 11} cannot be compared to "YES". You have to reference a cell here, not a range. You can do that, but only in functions like COUNTIFS or SUMIFS to name a few.

    All I can say for now :)

  • Susan Albers

    Thank you @David Joyeuse -

    Your feedback has helped, however, now I am receiving #INCORRECT ARGUMENT SET, using the following:

    =IF(AND({LegacyCompany} = "LCB", {Self Audit} = "YES", INDEX({BCOA District #}, MATCH([Column3]1, {BCOA Banking Center #}, 0))))

    Any insight would be greatly appreciated!

    Thank you.

  • Susan Albers


    I have 2 active forms on one sheet. These forms are being completed by separate groups. I need to pull the score from each group/form and display it on a summary sheet. I am running into trouble on this example:

    It will find a match on the Banking Center #, and pull the responses for group 1

    =IF([Self Audit Received]@row = "YES", INDEX({Score group 1}, MATCH([Banking Center #]@row, {BankingCenter#Range 3}, 0)))

    However, there will be a second form that I need to pull from information from as well, and it is stopping once it finds a match. Is there a way to pull this information together into one form - or loop through the results set until it finds a value >0?

  • Susan Albers
    Susan Albers ✭✭
    Answer ✓

    Please ignore these questions, as I was able to find a solution that will meet my needs. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!