# Thread: Find last number in column if year matches

1. ## Find last number in column if year matches

I have a worksheet with a large amount of production data broken up into production years and what I am needing is to find the last data entered in a column if the production year matches.

What I need it to do is look at column B last row and if the cell on Sheet3 D3 matches number in column B last row data entered it will return the last data from column J entered for that production year.

Thank you are any and all help

 A B C D E F G H I J K Row Day of Production Production Year # Total ABC Production Pipe QC Total ABCQC Production Total Daily Production Start Date End Date Total Year Production Total Year Over Contract ABCQC Over Contract 2 10 3 10 4 10 5 10 6 10

2. I think it'd be easier to understand if you attached a small workbook with sample data and all necessary worksheets

3. ## Find last number in column if year matches

Sorry it took so long but I had to sanitize the file. Hope this helps

Thank you for looking into my problem.

4. In the attached, formula at cell K15:
=INDEX(Sheet3!\$U\$2:\$U\$1905,MATCH(MAX(IF(Sheet3!\$L\$2:\$L\$1905=\$C15,Sheet3!\$K\$ 2:\$K\$1905)),Sheet3!\$K\$2:\$K\$1905,0))
gives the result 920,011.
I've guessed that Pipe A is Pipe AQC in column U of sheet 3.
It actually returns the value in column U that is on the same row of the latest (max) date in column K where there's a 6 in column L.
You can copy the formula down.
Similar formulae in L15 and M15.
For confirmation, there's a pivot table at cell O14, but this returns the max value in column U (or V or X) where there's the same Year# in column L (it doesn't look at the dates at all).

It looks as if the data in Sheet3 has been put together from data elsewhere, with lots of calculations added. It would probably be a lot simpler and robust to get the data you want directly from these source data.

5. Thank you for all your help, it worked great!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•