Return minimum result from multi options not first

Kate Hayes

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

Genevieve P.

Hi @Kate Hayes

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

Genevieve P.

Hi @Kate Hayes

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