How to make excel learn formula reference pattern

[Shreya]

Hello,

I am using the spreedsheet posted above to get the latest date from the 'In Column' for each book/ number i.e lets say book 1 in above case was lent out 3 times and returned las on 11-28-20, I want to see the last day when it was returned. The formula I am using in the spreedsheet is:

The issue I am facing is that I have about 220 books and excel is not learning the pattern of my formula AB,AA,BB then CD,CC,DD then EF,EE,FF. Is it possible to teach excel the pattern so that I don't have to type that in 200 times? Your ideas on this is highly appreciated.

Thank you

 

[Shreya]

just to explain a little bit further. The formulas I am typing in are: =IF(MAX(Sheet1!A:B)=MAX(Sheet1!A:A),"",MAX(Sheet1!B:B)) for 1, =IF(MAX(Sheet1!C:D)=MAX(Sheet1!C:C),"",MAX(Sheet1!D:D)) for 2, =IF(MAX(Sheet1!E:F)=MAX(Sheet1!E:E),"",MAX(Sheet1!F:F))