Having trouble with Excel Lookup

[renigar]

Hello Everyone,

The attached Workbook has 15 years of freshwater flow data. There are three sheets. The two main sheets are SupplySummary and FlowData. On SupplySummary, column R is Max Day (MGD) for the year and column S is Max Day (Date). The day in that particular year that had the highest flow. The formulas on SupplySummary sheet should return values from the FlowData sheet. I can't seem to figure out how to lookup the Max Day (Date) value. What am I doing wrong? I've tried index match and xlookup and keep getting #Value! error.
The attached file contains macros but, there are no auto or event macros. If you don't mind, look at the formula I used in column R, I think there must be a better way. The one I'm using is slow to update.

Thanks,
renigar

 

[combo]

Why array function in S3? Try regular one.

 

[renigar]

Combo,
In my mind, which is often times foggy, the formula has to check through 11 columns and 5000+ rows that will grow every month. It seemed like an array to me. I also thought that since I already got the Max Day value in Column R and the date I need is on the same row as that value, there must be an easy way to get it. But like I said the brain fog settled in and I'm not seeing it. If you would like to suggest which regular function could do the trick I would be happy to hear it.

Thanks,
renigar

 

[mintjulep]

Many functions (including the lookup functions) accept or require arrays as inputs. Such functions are NOT array functions.

Array functions return an array.

In current versions of Excel functions that return arrays do so automatically. It's no longer necessary to explicitly enter them as array functions.

 

[renigar]

I appreciate the comments. I'm stuck here or maybe slightly blind. I figured xlookup was the way to go but, I'm getting the #VALUE! error and can't see what's messing me up. You don't need to solve it but, I could use a helpful suggestion.
Thanks