ABS formula not able to accept an Array

Table 1:

C2 =INDEX(Table 3::A,I)

D2 =XLOOKUP(A2,Table 2::A,Table 2::A,"NF",−1)

E2 =XLOOKUP(A2,Table 2::A,Table 2::A,"NF",1)

F2 =IF(A2−D2≤E2−A2,D2,E2)

G2 =XLOOKUP(F2,Table 2::A,Table 2::B)

H2 =DUR2HOURS($A2−F2)

I2 =MINIFS(Table 3::E,Table 3::D,G,Table 3::B,"<="&H2,Table 3::C,">="&H2)

Fill down with all of them

Table 2 is all manually entered. Note that you will get an error in Table 1 if a siting is before the first tide or after the last tide in Table 2.

Table 3 is manually entered except for column E

E2 = ROW()

fill down

Hide columns D-I in Table 1

Hide column E in Table 3

That is way too complicated. If anything changes in your table and you need to edit that formula it will be a royal pain. If something doesn't work right it will be very difficult to debug. Here are some thoughts:

First I have a question about your formula which might negate/change what I write below. You are limiting the range of your ABS to tides that occur on the same day but tides are a continuous thing and your shark siting might be closer to a tide on the next or previous day. Your formula doesn't look at those tides and, to me, it will give wrong results. What I am suggesting below uses the date & time as continuous and looks in Table 2 for the closest date & time. It may return a value from the day before or the day after.

1. Make Table 1 column A the date & time of each siting and delete columns B and C. You also won't need columns E or F so you can delete them, too
2. Make Table 2 column A the date & time of each tide, delete column D, and remove the time from column C. Use column C only for the depth. All the info for each tide will fit on a single row; it will not require two rows per tide. All data for a tide must be in one row. Every row needs a date & time in column A.
3. With those two changes you can use XLOOKUP to look up what the closest tide was without having to use INDIRECT at all. In one column you will look for a date & time in Table 2::A that is "exact or next smallest" to your siting and return that date & time. In another you will look for a date&time that is "exact or next highest" and return that date & time. I'll call those two columns J and K for now
4. With those two, you can do some simple math to determine which is closest and that will determine which of those date&times you use for any other lookups. =IF(A-J<=K-A,XLOOKUP(J,Table 2::A,Table 2::B),XLOOKUP(K,Table 2::A, Table 2::B)) will give you whether it was closer to low or high tide.

That's the general idea. I don't understand your Table 3 and I'm not going to even try deciphering your long formula so I'll stop here. Most of this requires extra columns to do the work piece by piece rather than trying to force everything into an extremely long formula.

Long, unwieldy formulas like that are a signal to step back and try to present the problem in simpler terms.

Articulating in simple terms what you are trying to do can help.

So you are given [date, time, number of sharks, a tide schedule].

And you want exactly what kind of results?

SG