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

#### Uganda

##### Board Regular
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!

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
They do not need to all be next to each other.
For example,
Code:
``=MIN([COLOR=#333333]WT2,ABO2,ABR2,ACP2)[/COLOR]``

Last edited:
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.

They do not need to all be next to each other.
For example,
Code:
``=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.

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?

Last edited:
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

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

Last edited:
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!

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.

Replies
6
Views
363
Replies
3
Views
326
Replies
2
Views
139
Replies
2
Views
231
Replies
1
Views
312

1,203,170
Messages
6,053,866
Members
444,690
Latest member
itgldmrt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back