# Record values from one column based on those in another column

The attached Excel workbook includes the data that corresponds with the two calculation needs associated with this question:

1) Need to fill Column C of the "ResultsTable" sheet with the day of year (DOY; in Column F of the "Data" sheet) that corresponds with the max depth for that year.  If two DOY's have the same max depth, return the largest of the two DOY's

2) Need to fill Column D with the DOY that corresponds with the first zero value encountered subsequent to the value derived in Column C.

I manually populated the correct values for AllSeasYear = 1993.

Is there a good way to automate this with Excel functions?
OriginalDataFile.xlsx
LVL 1
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Excel VBA DeveloperCommented:
The MaxDepthDOY value can be found with this formula (in C2, copied down):
=INDEX(OFFSET(Data!F1,MATCH(A2,Data!E:E,0)-1,0,COUNTIF(Data!E:E,A2)),MATCH(B2,OFFSET(Data!J1,MATCH(A2,Data!E:E,0)-1,0,COUNTIF(Data!E:E,A2),1),0))

Still working on the second one...

-Glenn
0
Author Commented:
Works perfect. Thanks Glenn.
0
Excel VBA DeveloperCommented:
Here's the formula for the first zero depth day after the Maximum - in cell D2, copied down (deep breath):
=OFFSET(Data!\$F\$1,MATCH(A2,Data!E:E,0)+MATCH(C2,OFFSET(Data!\$F\$1,MATCH(A2,Data!E:E,0)-1,0,COUNTIF(Data!E:E,A2),1),0)-1+MATCH(0,OFFSET(Data!\$J\$1,MATCH(A2,Data!E:E,0)+MATCH(C2,OFFSET(Data!\$F\$1,MATCH(A2,Data!E:E,0)-1,0,COUNTIF(Data!E:E,A2),1),0)-2,0,180,1),0)-2,)

To simplify things (no, really), the formula is only looking ahead 180 days after the max; seems to work.  My gut tells me this might be made a bit simpler, but I'm inclined to leave it be.

Regards,
-Glenn
0

Experts Exchange Solution brought to you by