#Invalid Value in Index Match

cfischer
cfischer
edited 12/09/19 in Formulas and Functions

Hi Smartsheets Community, 

I am trying to write an index match that looks at a row that either says yes or no (only one column says yes) and return to me the corresponding value in the column below it. 

I currently wrote =index([column name]#:[column name]#, match("yes", [column name]#:[column name]#, 0)))

I keep getting an "invalid value" as the result. The index and match range are the exact same columns, I'm not sure what I'm doing wrong. Does anybody know how to fix this? 

Thanks!

 

 

Tags:

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    I assume your example is generic and not really what you typed.

    re: "The index and match range are the exact same columns, I'm not sure what I'm doing wrong."

    This makes no sense. 

    This for the whole column

    =INDEX([Column2]:[Column2], MATCH("yes", [Column2]:[Column2], 0))

    or this for the first ten rows

    =INDEX([Column2]1:[Column2]10, MATCH("yes", [Column2]1:[Column2]10, 0))

    will return "yes" if it finds it, but won't give you any further information.

    Are you looking for any instance of "yes"?

    Craig

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!