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]

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

Man, i just went in and took out all of the cells with the Value and put them blank and the formula worked. Why is that?

 

[Aladin Akyurek]

Man, i just went in and took out all of the cells with the Value and put them blank and the formula worked. Why is that?

=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))))

should also work.

When there is an error value in a range, most functions cannot ignore them, so the result also takes on an error value.

 

[ambepat]

=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))))

should also work.

When there is an error value in a range, most functions cannot ignore them, so the result also takes on an error value.

Thanks Aladin for your help. I heard a lot about you from Mike Girvin Excel fun so was going to as you what is the best way to learn VBA. I am trying to make my skills better. I find it difficult to understand VBA watching videos and I am thinking of getting a mentor or something. Do you have any good advice?

 

[Aladin Akyurek]

Thanks Aladin for your help. I heard a lot about you from Mike Girvin Excel fun so was going to as you what is the best way to learn VBA. I am trying to make my skills better. I find it difficult to understand VBA watching videos and I am thinking of getting a mentor or something. Do you have any good advice?

See whether this helps: https://www.mrexcel.com/forum/excel-questions/1032861-searching-vba-video-course-examples-learn-vba-short-time.html?highlight=hiker