obtain last value of sample according to two conditions

[AR2024]

Hi! How can I obtain the value of the next sample (AU) according to unit and batch. eg., the when AO is 50, AU should be 89, and so on. But it needs to be in accordance the unit and batch. When the unit and batch change it should assume the next different value of AO (sorry if it's not clear). eg., when unit changes, in AU15 should be 138.

 

[NickYOW]

my thought could be a number of IF statements in column AU if(B15="E1", 89, if(b15="E2",138,if(B15="F1",204,"")) if any of the batch change it will look through the formula and return the value.

 

[AR2024]

my thought could be a number of IF statements in column AU if(B15="E1", 89, if(b15="E2",138,if(B15="F1",204,"")) if any of the batch change it will look through the formula and return the value.

It will not work because I have more rows with different numbers