Having trouble with Index and Small formula...?

Options

Hi, I'm trying to find the smallest (or oldest) date in a column and then return a cell value from the same row of a different column. Below is my current formula. I want to return the "Lumber Type" with the smallest date from the "Last Cycle count Date" column.

=INDEX([Lumber Type]1:[Lumber Type]35, MATCH(SMALL([Last Cycle Count Date]35:[Last Cycle Count Date]1, 1)))

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    Instead of the small function. If your wanting the earliest date use the Min function.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Brandon Morales
    Options

    Im actually looking for the three smallest dates which will need to be counted. I figured using the small function in 3 different cells where N = 1, 2 and 3…

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    That makes more sense. Try This.

    =Index(Small([Last Cycle Count Date]1:[Last Cycle Count Date]35,1),Match([Lumber Type]@row,[Lumber Type]1:[Lumber Type]35,0))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Brandon Morales
    Options

    getting a circular reference error… :/

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    I see the issue and apologize for not noticing it earlier? Index Match needs something to match it too. So instead do the =Small([Last Cycle Count Date]1:[Last Cycle Count Date]35,1) In A Hidden helper row.

    Small([Last Cycle Count Date]1:[Last Cycle Count Date]35,1) for Cycle count#1

    Small([Last Cycle Count Date]1:[Last Cycle Count Date]35,2) for Cycle count#1

    Small([Last Cycle Count Date]1:[Last Cycle Count Date]35,3) for Cycle count#3

    THEN in the column your pulling the information to

    =Index([Lumber Type]1:[Lumber Type]35,Match(Helper@row,[Last Cycle Count Date]1:[Last Cycle Count Date]35,0))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!