formula with multiple conditions

Question

Hi,

in the workbook link below i'm trying to create a formula in column O and Column P in the first tab which:

• column O: if the ID in column A on the first tab is the same as the ID in SIO tab in column H, i want to bring back the latest date in the dataset in column O. ie in the first tab for ID 50003208 W21410 5687, i want column O to bring back the latest date for this ID from tab 2. which in this case would be 30/03/25.

in column P in the first tab, i want the formula to bring the rate associated with the ID and latest end date, ie for 50003208 W21410 5687 the rate would be 162.7943 as this is the rate in tab 2 for this ID at the latest end date.

Any help would be greatly appreciated. thanks

Test workbook.xlsx

Answer 1

In O2:

=LET(d, MAXIFS(SIO!$E$3:$E$3219, SIO!$H$3:$H$3219, A2:A321), IF(d=0, "", d))

In P2:

=IF(O2#="", "", XLOOKUP(A2:A321&O2#, SIO!$H$3:$H$3219&SIO!$E$3:$E$3219, SIO!$G$3:$G$3219, ""))