Index and Match Help

getting there

In Cell B3,
=SUMPRODUCT(MAX((DB_IFR!$G2:$G18882=Report!B$2)*(DB_IFR!$C2:$C18882=$A3)*(DB_IFR!$J2:$J18882)))

Fill down.


You need Find / Replace spaces in J:J to make the column dates instead of text.

Format your report as dates.

Add conditional formatting for dates=0 to be white font, or add an If clause to show "" instead of 0 (slower formula and already is slow)

Originally Posted by Neil_

Add conditional formatting for dates=0 to be white font, or add an If clause to show "" instead of 0 (slower formula and already is slow)

The reference to 18882 (almost 19k rows) vs. the 1882 (almost 2k) in DB_IFR is a big factor in the slowdown. Along with this, it looks like your formula only references the DB_IFR workbook. Row 1 in report references IFR, IFA, and IFC tabs.

@mcmahobt. I think your dates are wrong. B6 (MQAE622-K0H-5212) should be 18-Jan-2015 not 30-Dec-2014

From Filters
KentzDocNo Revision Date (Uploaded)
MQAE622-K0H-5212 B1 30-Dec-2014
MQAE622-K0H-5212 B1 18-Jan-2015

Filled in rest of cells. Posted partial solution earlier as a pointer. Thanks for noticing my deliberate mistake macmahobt

Added a helper row in the Report worksheet (row 2) because Excel doesn't like Merged cells too much. It was unclear to me which date column you wanted returned, so I referenced column J in the formula. If that is incorrect, just change that reference and it should work.

With Neil's point that you're looking for the MAX date (missed that the first time), you could use this in my previously posted workbook in cell B4 of the Report worksheet:

=MAX(IF(--((INDIRECT("DB_"&B$2&"!$C$2:$C$2000")=$A4)*(INDIRECT("DB_"&B$2&"!$G$2:$G$2000")=B$3)),DATEVALUE(INDIRECT("DB_"&B$2&"!$J$2:$J$2000"))))

Entered as an ARRAY FORMULA with CTRL+SHIFT+ENTER

This does not require any data manipulation to the Text Strings you have currently entered as Dates (thanks, DATEVALUE), but I agree with Neil in that you should format those as dates.

This is processing intensive with how many arrays are being used, so I turned off the formula calculations, so it may take a few minutes to calculate. I suggest when you aren't trying to calculate the entire sheet to turn off automatic calculations.

Can you elaborate on why the formula (which does not necessarily need the date ranges to function) should return a 1 instead of a 3? That is throwing me off.