Xlookup Help to produce a number if not found on prior tab

Hello Community,

I am looking for some help and I'm sure someone here knows. I am new to the Xlookup formula and I am trying to return a formula if result is not found.

So, currently it sits as: =xlookup(d4,'2024'!D:D,'2024'!B:B, "not found",0)

That works and returns "Not Found" when it cannot find the value.

What I would like it to do is return the max number in a column +1.
So, I tried =XLOOKUP(D2,'2024'!D:D,'2024'!B:B,MAX(B:B)+1,0) which returns an error.

=XLOOKUP(D2,'2024'!D:D,'2024'!B:B,25000+1,0) which returns 25001 the whole way.

=XLOOKUP(D2,'2024'!D:D,'2024'!B:B,B1+1,0) which works until it finds a match on other tab) and then starts from that number.

and it does not work.

I did attach a sample. Any help is appreciated. Hope it's something simple I am missing.

TIA