Extracting the newest date based on 2 criteria

[ambepat]

Hi Guys,

I am trying to extract records for the newest date within a set of data that meets 2 criteria.

I have tried this array formula below but it returns a #VALUE error.

Here is the formula I have tried. For some reason it is not working and I need to understand why
=LARGE(IF($L$2:$L$671=$J2,IF($D$2:$D$6584=N$1,$G$2:$G$6584)),1)

Any help will be appreciated.

Thanks,

Patrick

 

[ambepat]

About the formulas in ranges, Yes we have Number value formula in the U range, Index Match formula in the H range and Int formula in the M range

 

[Aladin Akyurek]

About the formulas in ranges, Yes we have Number value formula in the U range, Index Match formula in the H range and Int formula in the M range

Do these formulas produce errors like #N/A, put otherwise, do we have error values in these ranges?

 

[ambepat]

Do these formulas produce errors like #N/A, put otherwise, do we have error values in these ranges?

We don't have N/A in the formulas but we have about 6 rows that have a #VALUE ! in it.

 

[Aladin Akyurek]

We don't have N/A in the formulas but we have about 6 rows that have a #VALUE ! in it.

In which range?

 

[ambepat]

In which range?

We have about 59 rows in the M range that has the #Value because the cell for which the int formula points to is blank.

And in the U range we have about 29 rows with the #Value

 

[Aladin Akyurek]

We have about 59 rows in the M range that has the [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL] because the cell for which the int formula points to is blank.

And in the U range we have about 29 rows with the [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL]

You could try to suppress the #VALUE ! errors by adjusting the output of the formulas which generate them.

Or control+shift+enter, not just enter:

=MAX(IF(IFERROR('Data - PMC'!$U$2:$U$6584),"")='Service Matrix'!$C18,IF('Data - PMC'!$H$2:$H$6584='Service Matrix'!AW$17,IF(ISNUMBER('Data - PMC'!$M$2:$M$6584),'Data - PMC'!$M$2:$M$6584)))

If you would rather modify the error-generating formulas, try to post them.

 

[ambepat]

You could try to suppress the #VALUE ! errors by adjusting the output of the formulas which generate them.

Or control+shift+enter, not just enter:

=MAX(IF(IFERROR('Data - PMC'!$U$2:$U$6584),"")='Service Matrix'!$C18,IF('Data - PMC'!$H$2:$H$6584='Service Matrix'!AW$17,IF(ISNUMBER('Data - PMC'!$M$2:$M$6584),'Data - PMC'!$M$2:$M$6584)))

If you would rather modify the error-generating formulas, try to post them.

Hi Aladin,

Thanks for your reply. Still doesn't work even with the ISNumber. I am not sure what else to do to extract the Max of this unique values. Is there any other way of doing it? I am just baffled it is not giving me what I want as it really should be working.

P

 

[Aladin Akyurek]

Hi Aladin,

Thanks for your reply. Still doesn't work even with the ISNumber. I am not sure what else to do to extract the Max of this unique values. Is there any other way of doing it? I am just baffled it is not giving me what I want as it really should be working.

P

What did you get with the new formula?

 

[ambepat]

What did you get with the new formula?

Still the same Value# error.

 

[Aladin Akyurek]

Still the same Value# error.

That is impossible, unless you also have #VALUE! errors in Data - PMC'!$H$2:$H$6584. Is that also case?