xlookup date of max value based on condition. If criteria value isn't found return ""

heretolearnexcel

Board Regular
Joined
Jan 22, 2019
Messages
58
Office Version
  1. 365
Platform
  1. Windows
Here's the formula I'm trying to use, but I keep getting an array filled with #VALUE errors:

=LET(Return;XLOOKUP(MAX(data!N2:N1170);(data!AG2:AG1170=J2)*data!N2:N1170;data!N2:N1170;"not found";-1);Range;(data!AG2:AG1170=J2)*data!N2:N1170;IF(NOT(NOT(COUNTIF(Range;"<>0")));Return;""))

I need the formula to return the max date that corresponds to the criteria J2. If the criteria J2 isn't found, then I want to get a blank result: ""
Column N corresponds to date values, and column AG is where the criteria J2 will be looked up.

If I copy and paste each of the names I defined in the formula, "Return" and "Range", to a different cell I get a result that isn't an error, so the problem is with the IF formula.

Is there a way to change my formula so that it works, or is there an alternate solution?

Thank you.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

heretolearnexcel

Board Regular
Joined
Jan 22, 2019
Messages
58
Office Version
  1. 365
Platform
  1. Windows
I came up with these changes, which seem to make the formula work now, but I don't know if there's a better way. Here's the formula:

LET(Available;XLOOKUP(J2;data!AG2:AG1171;data!N2:N1171;;;1);Return;XLOOKUP(MAX(productos_por_almacen_dila!N2:N1170);(data!AG2:AG1170=J2)*data!N2:N1170;data!N2:N1170;"not found";-1);IF(ISNA(Available);"";Return))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,762
Office Version
  1. 365
Platform
  1. Windows
Unless I've misunderstood, you can just use maxifs like
Excel Formula:
=IFERROR(1/(1/MAXIFS(Data!N:N,Data!AG:AG,J2)),"")
 

heretolearnexcel

Board Regular
Joined
Jan 22, 2019
Messages
58
Office Version
  1. 365
Platform
  1. Windows
I came up with a formula, which seems to work, but I don't know if there's a better way. Here's the formula:

LET(Available,XLOOKUP(J2;data!AG2:AG1171;data!N2:N1171;;;1);Return;XLOOKUP(MAX(productos_por_almacen_dila!N2:N1170);(data!AG2:AG1170=J2)*data!N2:N1170;data!N2:N1170;"not found";-1);IF(ISNA(Available);"";Return))
Yeah, that actually works, thank you. My solution was too resource intensive... every time I refreshed the sheet I had to wait for all the threads to load up.

I'm guessing the divisions in the formula can be explained algebraically: 1/(1/x) = (1/1)/(1/x) = x/1 = x. So when the formula runs into empty cells it has to divide by 0, which returns an error, and that's where the IFERROR part of the formula comes in to makes it return a "" blank value.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,762
Office Version
  1. 365
Platform
  1. Windows
So when the formula runs into empty cells it has to divide by 0, which returns an error, and that's where the IFERROR part of the formula comes in to makes it return a "" blank value.
That's right, if you're happy to have 0s then you can get rid of that part.
 

Forum statistics

Threads
1,181,695
Messages
5,931,496
Members
436,790
Latest member
aelbaitam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top