Pull data from cell based on max value against that data

[Pumperkin]

Hi, Hoping someone can help. This is kinda similar to my previous thread but I think different enough of an issue to warrant a new thread.

I have Devices in one column, Memory in another, IMEIs in another and days in stock in another.

I want to have a sheet that shows which of each device has been in stock the longest.

I have experimented with IF formulas and VLOOKUP, concatenating cells so that it's only 1 IF, but it doesn't seem to work.

I want to, for example, look up iPhone 7 32Gb, see which has been in stock longest and report the IMEI of it.

My horrendous looking attempt follows - it currently just returns "No Stock" for everything.

Not all devices are in stock so some should be "No Stock".

=IF(Shipped!M:M="Not Despatched", (IF(VLOOKUP(MAX(Shipped!N:N), Shipped!N:N:Shipped!T:T, 1, FALSE)=D2, Shipped!T:T, "No Stock")), "No Stock")

Thanks in advance

EDIT: Just to add - Column M is Despatch state (either there will be a date or "Not Despatched", Column N is time in stock, and column T is IMEI. I think the whole thing needs re-writing as it's assuming there will be only one device in stock for that length of time, which it isn't (multiple are received at the same time). Thanks

 

[Pumperkin]

I tried again with a double IF before the VLOOKUP but still not working

=IF(Shipped!P:P=Sheet1!A2,IF(Shipped!M:M="Not Despatched",VLOOKUP(MAX(Shipped!N:N),Shipped!N:N:Shipped!T:T,7,FALSE),"No Stock"),"No Stock")

My logic is that If The Make/Model (P:P) is equal to data on Sheet 1, then as long as Despatch state is "Not Despatched", it should look up the highest value time in stock and display the IMEI. Not sure where I've gone wrong but I feel like I'm down a rabbit hole!

 

[Pumperkin]

Just as I think I have a breakthrough it still doesn't work

I had the idea of making a helper column with the max days received, so that I don't have this formula within the VLOOKUP.

=IF(Shipped!P:P=Sheet1!A3,IF(Shipped!M:M="Not Despatched",VLOOKUP(E3,Shipped!N:N:Shipped!T:T,7,FALSE),"N/A"),"No Stock")

I have only started using VLOOKUPs this week so have probably made some elementary error - E3 is the days in stock, N:N is days received, T:T is IMEI, 7 is column 7 based on N being 1m and I want it to report the IMEI that matches the max days in stock E3.

 

[Pumperkin]

Bump as I haven't heard anything from anyone and still trying to get this to work