=(INDEX(COLLECT Invalid Value Error

I am getting an Invalid value error in this formula:

=(INDEX(COLLECT({Zit Zap 102621 Units Needed}, {Zit Zap 102621 Formula}, Formula@row, {Zit Zap 102621 Inv ID}, [RM Inventory ID]@row), 1))

I'm trying to bring in the RM Units Needed from Spreadsheet 2 when the Formula and RM Inventory ID from Spreadsheet 2 matches the Formula and RM Inventory ID on Spreadsheet 1. The error is on Spreadsheet 1.

Any suggestions?

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The text in the formula column on your target sheet is "Zit Zap" whereas the text in the formula column on the source sheet is "ZitZap" you have a space between one and not the other. This means the COLLECT function is pulling in zero rows, so the 1 in the INDEX function indicating to pull from the first row is causing the error (since there is no "row 1" for the INDEX function to pull from).


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!