Index & Match Formula to provide row number if found in column

IB3 ✭✭
edited 06/07/23 in Formulas and Functions

=IF(INDEX(ColumnA:ColumnA,MATCH(ColumnB@row, ColumnB:ColumnB,0), INDEX(Auto:Auto, MATCH(Auto@row,Auto:Auto, 0), "")))

Auto = System column to pull row number

ColumnA =Text

ColumnB =Text

ColumnC= Formula location & Row Number Output

So, what I’m trying to achieve is to have a formula to Lookup ColumnA text and match ColumnB text. If there is not a match, it returns blank, but if there is a match, to return the Auto of ColumnA.


I’m sure I’m overlooking something easy here, as I’m receiving an #INCORRECT ARGUMENT but I would be welcome for your assistance!


Best Answer


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

    Try this:

    =IFERROR(INDEX(Auto:Auto, MATCH(ColumnB@row, ColumnA:ColumnA, 0)), "")

  • IB3
    IB3 ✭✭

    Thanks Paul! You always know how to figure these types of fixes! lol

    I dropped it in, and it's returning "1" for all of the values expect the one that matches. Is there an easy fix to make sure that the column doesn't return any value? (Blank)

  • IB3
    IB3 ✭✭

    Hi Paul! Nevermind, I figure it out! lol

    I've been staring at computer screens too much! Thank you again for your amazing help!😅

  • Michael Bull
    Michael Bull ✭✭✭✭
    edited 11/26/23


    I came across the same problem. I want to know the relative position in a parent/children tree of a project

    So instead of looking up the row in a helper column, I am searching for the match of the Auto-Number (called RowID in my spreadsheet)

    MATCH(RowID@row, RowID:RowID, 0) is the momentary position of the child (the row itself)

    MATCH(PARENT(RowID@row), RowID:RowID, 0)) is the momentary position of the associated parent row

    As the RowID is unique by design and autogenerated, I am sure I am not screwing it up like a helper column.

    If there is a sneakier way of circumventing smartsheet limitations of spreadsheet functions, then well I have not found it yet.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!