Having trouble with Index and Small formula...?

Brandon Morales

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)))

Mark.poole

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

Brandon Morales

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

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))

Brandon Morales

getting a circular reference error… :/

Mark.poole

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))