Newest/Oldest Date lookup

[DRExcel515]

I have a list of dates 12/31/2017, 11/30/2017 etc. in column B going back in most cases 15 years on a rolling month end basis. I was able to use this formula to pull in the oldest date:

MINA(INDEX('SQL Dump'!C:C,MATCH(Calcs!B3,'SQL Dump'!B:B,0))) This pulls in the oldest date for each identifier correctly, but I tried using a similar formula but with MAXA to grab the latest month end date for each identifier, but it doesn't work.

Any suggestions would be much appreciated.

Thank you,

 

[Gerald Higgins]

Does your posted formula of

MINA(INDEX('SQL Dump'!C:C,MATCH(Calcs!B3,'SQL Dump'!B:B,0)))

give you any different results using your data from this formula

=INDEX('SQL Dump'!C:C,MATCH(Calcs!B3,'SQL Dump'!B:B,0)))

I ask this because I think the second formula should return a single value, and therefore slapping a MINA on the front simply repeats that value.
And I assume MAXA does the same.

 

[DRExcel515]

Yes you are correct! I guess since it was bringing me the oldest month end I thought it was working properly. So it's just giving me the first occurrence (hence the oldest month end). Do you have a formula that will look up the oldest and newest dates for a symbol on another tab like I was trying to do above?

Thank you!

 

[Gerald Higgins]

Is the data in

'SQL Dump'!B:B

sorted in date order ?

 

[wideboydixon]

Perhaps:

=MIN(IF('SQL Dump'!B:B=Calcs!B3,'SQL Dump'!C:C))

Entered with Ctrl+Shift+Enter for array formula.

I think you could possibly re-express this as a SUMPRODUCT for a non-array formula

WBD

 

[DRExcel515]

Yes the SQL Dump tab has the dates listed oldest to newest in a pivot table. Some are a full 5yr of month-end data but some are not which in part is making this more of a challenge, as well as the data will change every month.

Thanks!

 

[DRExcel515]

How would you re-express the sum product >> Sumproduct(if( ? If I can avoid the array I'd prefer to as I already have quite a few in this sheet and it's getting a little big and slow


Thanks!

 

[wideboydixon]

I don't think using SUMPRODUCT is going to make it quicker; it will just save you hitting Ctrl+Shift+Enter. Your formulas are going to be slow because they work on whole columns (B:B, C:C etc.) rather than just the data that is present (e.g. B1:B16732)

David