Formula to 2-way look up a unique identifier and a date, then take the highest number from another column

[MrsFraser07]

So.....this seems complicated to me but maybe someone here can help. What I'm looking to do is the following:

I have two worksheets in my spreadsheet. One has 2 columns of data, unique values (UWI) and dates. The other has corresponding data by month. What I want to do is lookup each UWI, find the corresponding month from the date, and then take the highest value in the first 6 months that it finds. Using the examples of my spreadsheet below, I want to take the UWI from Sheet 1 and the on prod date, go to sheet 2 and find that UWI and the first month it produced, in the first line for example it is 12/19/2009. Then take the highest value of Cal Dly Oil in the first 6 months. In this example it would be 183.95.

The way I can pull the data on Sheet 2 is from the beginning of time, so the data will always have the first 6 months for each UWI. Maybe the best way would be to find the UWI, find the first 6 values of Cal Dly Oil and then take the MAX of that? I'm just not sure how to put that formula together. Thanks for any help!

Sheet 1:

Sheet 2:

 

[Cubist]

Try something like. Lock the ranges on Sheet2.

=MAX(TAKE(FILTER(Sheet2!Avg Dly Oil Range, Sheet2!UWI Range = Sheet1!UWI),6))

 

[MrsFraser07]

I tried that, it doesn't work. I get a number but it is not the correct number which is the MAX of the first 6 values it finds. Thanks for your response though. I'll keep tinkering.

 

[MrsFraser07]

I tried it again, figured I must have had something incorrect and it turns out I did! I read up on the TAKE and FILTER functions and I had the incorrect UWI range. This solved my problem beautifully! I love learning something new. I can apply this in other spreadsheets. Many thanks Cubist!