Lookup previous date in excel

1912 views excel
5

Looking for way to find out the last time an event occurred. Here is what I have for data in excel. I would like to be able to look up the Home column name in both the Home and away column then Subtract the date from the last time that name occurred. Is this possible? If so what formula am I looking for?

Date       Home Away
10/4/2017   TOR WPG
10/4/2017   STL PIT
10/4/2017   CGY EDM
10/4/2017   PHI SJS
10/5/2017   NSH BOS
10/5/2017   MTL BUF
10/5/2017   COL NYR
10/5/2017   WSH OTT
10/5/2017   MIN DET
10/5/2017   PIT CHI
10/5/2017   ARI ANA
10/5/2017   PHI LAK
10/6/2017   NYI CBJ
10/6/2017   FLA TBL
10/6/2017   VGK DAL
10/7/2017   NYR TOR
10/7/2017   DET OTT
10/7/2017   TBL FLA
10/7/2017   COL NJD
10/7/2017   BUF NYI
10/7/2017   NSH PIT
10/7/2017   MTL WSH
10/7/2017   MIN CAR

Desired outcome:

Date    |  Home|Away|Home_last|Away_Last
10/4/2017   TOR WPG 0   0
10/4/2017   STL PIT 0   0
10/4/2017   CGY EDM 0   0
10/4/2017   PHI SJS 0   0
10/5/2017   NSH BOS 0   0
10/5/2017   MTL BUF 0   0
10/5/2017   COL NYR 0   0
10/5/2017   WSH OTT 0   0
10/5/2017   MIN DET 0   0
10/5/2017   PIT CHI 1   0
10/5/2017   ARI ANA 0   0
10/5/2017   PHI LAK 1   0
10/6/2017   NYI CBJ 0   0
10/6/2017   FLA TBL 0   0
10/6/2017   VGK DAL 0   0
10/7/2017   NYR TOR 2   3
10/7/2017   DET OTT 2   2
10/7/2017   TBL FLA 1   1
10/7/2017   COL NJD 2   0
10/7/2017   BUF NYI 2   1
10/7/2017   NSH PIT 2   3

answered question

Could you give us an example of the result? I have trouble reading your description. Do you need to check Home only or both Home and Away? And you would like to subtract the second-last date from the last one?

Your intended calculation isn't really clear. Can you post the formula you are trying to write, what you have working, or pseudo code, and what cell(s) it would be in.

1 Answer

11

You can find the max date matching on one or more if conditions using an array formula. Check out this website: https://exceljet.net/formula/maximum-if-multiple-criteria

posted this

Have an answer?

JD

Please login first before posting an answer.