Lookup lowest Monday tide

At a high level, this example is about finding a minimum value based on multiple criteria. To do that, we are using the MIN function together with two nested IF functions:

{=MIN(IF(day=I5,IF(tide="L",pred)))}

working from the inside out, the first IF checks if the day is "Mon", based on the value in I5:

IF(day=I5 // is day "Mon"

If the result is TRUE, we run another IF:

IF(tide="L",pred) // if tide is "L" return prediction

In other words, if the day is "Mon", we check if the tide is "L". If so, we return the predicted tide level, using the named range pred.

Notice we do not provide a "value if false" for either IF. That means if either logical test is FALSE, the outer IF will return FALSE. For more information on nested IFs, see this article.

It's important to understand that the data set includes 120 rows, so each of the named ranges in the formula contain 120 values. This is what makes this an array formula – we are processing many values at once. After both IFs are evaluated, the outer IF will return an array that contains 120 values like this:

{FALSE;FALSE;FALSE;FALSE;FALSE;3.27;FALSE;0.3;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;2.02;FALSE;0.17;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;3.04;FALSE;-0.55;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;1.96;FALSE;-0.64;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;3;FALSE;-0.02;FALSE;FALSE;FALSE;FALSE}

The key thing to notice here is only values associated with Monday and low tide survive the trip through the nested IFs. The other values have been replaced with FALSE. In other words, we are using the double IF structure to "throw away" values we aren't interested in.

The array above is returned directly to the MIN function. The MIN function automatically ignores the FALSE values, and returns the minimum value of those that remain, -0.64.

This is an array formulas and must be entered with control + shift + enter.

Minimum with MINIFS

If you have Office 365 or Excel 2019, you can use the MINIFS function to get the lowest Monday tide like this:

=MINIFS(pred,day,"Mon",tide,"L")

The result is the same, and this formula does not require control + shift + enter.

Get the date

Once you find the minimum Monday tide level, you will undoubtedly want to know the date and time. This can be done with an INDEX and MATCH formula. The formula in I7 is:

{=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))}

Working from the inside out, we need to first locate the position of the lowest Monday tide with the MATCH function:

MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))

Here, we run through the same conditional tests we applied above to restrict processing to Monday low tides only. However, we apply one more test to restrict results to the minimum value now in I6, and we use a slightly simpler syntax based on boolean logic to apply criteria. We have three separate expressions, each testing one condition:

(day=I5)* // day is "Mon"
(tide="L")* // tide is "L"
(pred=I6) // prediction is min value

Each of these expressions runs on 120 values and returns an array of 120 TRUE FALSE results. When these arrays are multiplied by one another, the TRUE FALSE values are coerced to 1s and 0s. The result is a single array like this:

{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

Because there is only one value in the entire data set that meets all three conditions, there is only a single 1 in the array.

Now you can see why we have configured the MATCH function to look for the number 1 in exact match mode. MATCH locates the 1, and returns a position of 88 directly to the INDEX function. We can now rewrite the formula like this:

=INDEX(date,88) // returns 23-Dec-19

The INDEX function then returns the 88th value in the named range date, which is 23-Dec-19. This is the date that corresponds to the lowest Monday tide level.

This is an array formulas and must be entered with control + shift + enter.

Get the time

The formula to retrieve the time of the lowest Monday tide is almost the same as the formula to get the date. The only difference is that the named range time is provided to INDEX instead of date. The formula in I8 is:

{=INDEX(time,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))}

In other respects the behavior of the formula is the same, so we end up with a similar result:

=INDEX(time,88) // returns 2:44 PM

As before, INDEX returns the 88th item in the array, which is 2:44 PM.

This is an array formulas and must be entered with control + shift + enter.

Note: in the event of a tie (two Monday low tides with the same value), the INDEX and MATCH formulas above will return the first match.

Date and time with XLOOKUP

With the XLOOKUP function, you can simplify the formulas used to get the date and time associated with the lowest tide:

=XLOOKUP(1,(day=I5)*(tide="L")*(pred=I6),date) // get date
=XLOOKUP(1,(day=I5)*(tide="L")*(pred=I6),time) // get time

This is an example that nicely shows off XLOOKUP's flexibility. We can use exactly the same logic from the INDEX and MATCH formulas above, in a simple and elegant formula.