How to return lowest date when index matching complete columns rather than just a selection?

D Rowland

Is anyone able to help with the formula to Index Match the SO Number column and return the lowest date listed in the "finished date" column into the "first date" column? I can make it work when selecting just the relevant data in the column, however it returns errors as soon as I try change it to search the complete column (I want to convert it to a column formula).

Protonsponge

Hello @D Rowland

I think a MIN(COLLECT formula could be useful for you to return the earliest date for each [SO Number]. I have made a quick demo using the formula below.

The formula will return the earliest date from [Finished Date] where the SO number in the Data Sheet matches the [SO Number]@row in the look up/formula sheet.

=MIN(COLLECT({Data Sheet_Finished Date}, {Data Sheet_SO Number}, [SO Number]@row))

Data Sheet:

Look Up/Formula Sheet:

I hope that is helpful to you in some way,

Protonsponge

Protonsponge

Hello @D Rowland

I think a MIN(COLLECT formula could be useful for you to return the earliest date for each [SO Number]. I have made a quick demo using the formula below.

The formula will return the earliest date from [Finished Date] where the SO number in the Data Sheet matches the [SO Number]@row in the look up/formula sheet.

=MIN(COLLECT({Data Sheet_Finished Date}, {Data Sheet_SO Number}, [SO Number]@row))

Data Sheet:

Look Up/Formula Sheet:

I hope that is helpful to you in some way,

Protonsponge

D Rowland

Thank you - this has worked!!