Last value from INDEX MATCH

I am a smartsheets newbie, but have good experience with Excel and have been trying some things for an inventory management tool that I am creating.

Typically in excel I use the following formula to pick the last value from INDEX MATCH and it works well to give me the last matching value in the range of C3 to C12 from the match between E3 and the range of repeated values in B3 to B12.


Somehow it seems that a similar formula in smartsheets throws a #INVALID OPERATION error.

=INDEX({LoggerData Range 4}, MATCH(2, 1 / ({LoggerData Range 2} = [Barcode #]@row)))

Am I missing something, or is this something I can do to make this work?



  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @narora

    I hope you're well and safe!

    Try something like this.

    =INDEX({ColumnWithTheValueYouWantToShow}; MATCH(CellThatHaveTheValueToMatch@row; 
    {ColumnWithTheValueToMatchAgainsTheCell}; 0))

    Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic week!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Thanks @Andrée Starå for you quick response.

    As you mentioned, the formula works well for me in the United States with the comma, but I do not want the first match of "ColumnWithTheValueToMatchAgainsTheCell" rather I want the last match. If I could explain by example:

    AA 40

    BB 50

    CC 30

    BB 100

    DD 100

    BB 70

    EE 60

    GG 80

    VV 40

    BB 10

    Today INDEX MATCH of BB the following formula works well to give me 50, but I want to see if smartsheets can provide me 10.

    =INDEX({LoggerData Range 4}, MATCH([Barcode #]@row, {LoggerData Range 2}, 0))

    If this data was in excel the following formula does give me 10 as an answer =INDEX($C$3:$C$12,MATCH(2,1/(B3:B12=E3)))

    but in smartsheets it gives me #INVALID OPERATION

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!