Get the column or collection index of a Matched value

Options


In the example below I am trying to get the array index of the matched value. I'm looking for the number in columns Jan - Dec where it equals the value in Current Month. I can find the value, but I need the position so I can derive the financial period the entry falls into.

Or better yet, how would I get the position of the last populated column?

Thank you,

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    The MATCH function would output the column number where the value in [Current Month] is located in the Jan - Dec columns.


    =MATCH([Current Month]@row, January@row:Dec@row, 0)

  • earl_bennett
    earl_bennett ✭✭✭✭✭✭
    Options

    I thought that would work too, but I got NO MATCH when trying that...

    I just found be clicking on a Current Month cell that there is a hidden ` character in front of the value. I think the import process put that there. Also found that the column was set to drop down instead of plain text.

    After changing the column to plain text, the formula worked....


    ** Is there a way to find the last match in case there are duplicate entries? **

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!