Excel Formula

purohitchetan 

If FILTER() is available in your version of Excel that's definitely better to use it.

If not, in any case it's better to use Excel structured tables for the source data.

If not (you don't want to invest some time to learn about structured tables or you are on something like Excel 2000), when it's better to use dynamic ranges.

In the formula the part as

(Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

returns only actual data range, not million of rows if you use Sheet1!A:A instead. Alternatively you may use something like Sheet1!$A$2:$A$19, but that's not a good ide from maintenance point of view - you have to change the formula each time the range is changed.

With that main formula is 

=INDEX(Sheet1!$B:$B, <row number where condition is met>)

other words we take from column B value in row #, the rest is to calculate which row number to take.

Next step is to find such row number, we use MATCH() for that

MATCH(1,
      INDEX( (<dateRange>=MAX(<dateRange>)* (<IDrange>=<ID>), 0)
, 0)

Criteria within as 

(<dateRange>=MAX(<dateRange>)* (<IDrange>=<ID>)

returns sets of 1 (TRUE) and 0 (FALSE) if criteria met or not met. With MATCH(1, ... we find first one for which criteria is met.

We may avoid of using INDEX() here but when we shall use array function entered with CTRL+SHIFT+ENTER. If with INDEX() it natively returns an array to MATCH() and we may work with regular formula.

Again, above is legacy style which could be used if you don't have or don't want to use modern Excel functionality. Some of "modern" functionality as tables is available for at least 10 years, some as FILTER() appeared for everyone on 365 about an year ago or so.

purohitchetan 

Did my sample with FILTER not work at all? Sergei was proposing his as an alternative if you didn't have access yet to FILTER... some response, one way or the other, would be appreciated.