# Return minimum result from multi options not first

Options

Hello,

I have 2 sheets collecting data, both sheets use a unique PRF reference number (which I use for match purposes), I need the look up to return the earliest date from the Book 1 Range 1 data, where the PRF numbers match more than once on the sheet. Currently it will only return the first option it finds, as you can see I added the MIN argument but not something I have used before, so unlikely in the correct argument point in the formula

=MIN(INDEX({Book1 Range 1}, MATCH([PRF Auto Number]@row, {Book1 Range 2}, 0)))

I have an excel formula that works for the above, if there a way of making if work in SS

=MIN(IF(Sheet2!B2:B4 = Sheet1!B2, Sheet2!C2:C4))

Thanks

Kate

Options

You've done a great job! The only issue here is the order of the functions. You want to wrap the MIN function around the Range inside of your INDEX function... try this, swapped around:

=INDEX(MIN({Book1 Range 1}), MATCH([PRF Auto Number]@row, {Book1 Range 2}, 0))

This will look through all the rows that match the PRF Auto Number and return the Minimum date from Range 1. Let me know if this works for you!

Cheers,

Genevieve

Options

You've done a great job! The only issue here is the order of the functions. You want to wrap the MIN function around the Range inside of your INDEX function... try this, swapped around:

=INDEX(MIN({Book1 Range 1}), MATCH([PRF Auto Number]@row, {Book1 Range 2}, 0))

This will look through all the rows that match the PRF Auto Number and return the Minimum date from Range 1. Let me know if this works for you!

Cheers,

Genevieve

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!