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

#### heretolearnexcel

##### Board Regular
Here's the formula I'm trying to use, but I keep getting an array filled with #VALUE errors:

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
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:

#### Fluff

##### MrExcel MVP, Moderator
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
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:

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
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.

Replies
3
Views
28
Replies
5
Views
386
Replies
2
Views
106
Replies
3
Views
106
Replies
5
Views
47

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.

### Which adblocker are you using?

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

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