Smallest value (earliest date) but not in a list. How to adapt MIN(IF(...) function

[Uganda]

Hello

I need to find the earliest date out of four fields but they are not next to each other.

I have seen the below formula:

=MIN(IF(A1:A4>=01/07/15, A1:A4))

However I can't seem to make it work when not selecting a list to choose from i.e. I want to choose from WT2, ABO2, ABR2 and ACP2.

Can anyone advise what formula I need to use?

Thanks so much!

 

[Joe4]

They do not need to all be next to each other.
For example,

=MIN([COLOR=#333333]WT2,ABO2,ABR2,ACP2)[/COLOR]

 

[Eric W]

If you're looking for the minimum date that's above a given date, you can try:

=MIN(IF(WT2>=G4,WT2,99999),IF(ABO2>=G4,ABO2,99999),IF(ABR2>=G4,ABR2,99999),IF(ACP2>=G4,ACP2,99999))

where your given date is in G4. If no date satisfies your condition, it will return 99999. Granted, this doesn't scale well, but I haven't found an array formula that works with disjoint ranges.

 

[Uganda]

They do not need to all be next to each other.
For example,

=MIN([COLOR=#333333]WT2,ABO2,ABR2,ACP2)[/COLOR]

Thanks Joe! I know that the MIN formula can contain a list however with the IF formula it wasn't working

My issue is that I need to pick the MIN date but it has to be after 01/07/15.

Thanks in advance

 

[Joe4]

My issue is that I need to pick the MIN date but it has to be after 01/07/15.

Did you see Eric's response?

 

[FDibbins]

Note that if you are using an actual date, you will need to put it "" or in the DATE() function, otherwise excel will think it is a calc...

=MIN(IF(A1:A4>=01/07/15, A1:A4))
would be seen as...
=MIN(IF(A1:A4>=0.00952381, A1:A4))
1 divided 7 divided 15 = 0.00952381

 

[Jonmo1]

Try this as an array entered with CTRL + SHIFT + ENTER

=MIN(IF(CHOOSE({1,2,3,4},WT2,ABO2,ABR2,ACP2)>"01/07/15"+0,CHOOSE({1,2,3,4},WT2,ABO2,ABR2,ACP2)))

 

[Uganda]

Jonmo1, thanks so much for this. I have never used the CHOOSE function and I am not sure how it works but I will read up on it! This has worked very well. Thanks, have a good evening!

 

[Uganda]

Thanks Eric for your help, I found the answer below from Jonmo1. The reason your solution wouldn't work is that I couldn't prioritise the columns.

 

[Uganda]

FDibbins, thanks for your sage advice. I apprecaite it!