Index Match with 2 criteria and return latest date

[kwangyeow]

Hi,

I have a question with finding the latest date using Index & Match function with 2 criteria. In the list of table, there are repeated row of entry that matches this 2 criteria, but I needed the latest date.

Base on the my formula, it always give me old dates.
=IF(J5<>"",IF(ISNA(INDEX(Outgoing!A:A,MATCH(F5&E5,Outgoing!E:E&Outgoing!F:F,0))),"",(INDEX(Outgoing!A:A,MATCH(F5&E5,Outgoing!E:E&Outgoing!F:F,0)))),"")

From what I search online, most of the answer reply with Max function and I tried it, but it didn't work well for me.

Need some help on this.

Thank you so much !

Google drive link : https://drive.google.com/file/d/1v1i_n2fEDzF6WZmts-JT3Tg6B1GGRg3M/view?usp=sharing

Cheers,
Kwang Yeow

 

[Special-K99]

You cant do it like that, you cant MATCHF5&E5 against the entire columns E & F

Use this instead

=MAX(IF((F5=Outgoing!E$12:E$1175)*(E5=Outgoing!F$12:F$1175),A$12:A$1175))
Array formula, use Ctrl-Shift-Enter

 

[kwangyeow]

It didn't work out, return result as 1/0/1900. I am clueless that index match function can't return latest date. Is there any way to go around it and get the desired result ?

 

[Peter_SSs]

It didn't work out,..

It will, or at least it might, if you abide also abide by the last line of Special_K99's post. The formula must be confirmed as described, not just with Enter.

(I also think the ranges in the suggested formula may be a typo. The ranges should go to row 1275)

 

[Peter_SSs]

.. and if you would prefer a formula that does not require the array formula entry, you could try

=AGGREGATE(14,6,Outgoing!A$12:A$1275/((Outgoing!E$12:E$1275=F5)*(Outgoing!F$12:F$1275=E5)),1)

 

[Rickmaurinus]

Learned something here today, thanks for posting!

 

[Special-K99]

It will, or at least it might, if you abide also abide by the last line of Special_K99's post. The formula must be confirmed as described, not just with Enter.

(I also think the ranges in the suggested formula may be a typo. The ranges should go to row 1275)

Oops, yes 1275, misread the row number as it wasnt at 100% magnification.

 

[kwangyeow]

Hi Special-K99,

I notice the range and was edited to 1275, abide with the array formula press CTRL-SHIFT-ENTER. But it still returned me 0-Jan-00.
Uploaded the edited excel with MAX formula, please review what went wrong. Thank you.
Google link : https://drive.google.com/open?id=1kw1uqdckur6u3ZLXnO8og8Vl2Mi7NefP

Hi Peter_SSs,

Aggregate works great for my needed return result. As my outgoing tab is ever increasing entry, I have edited the formula to
=IFERROR(AGGREGATE(14,6,Outgoing!$A:$A/((Outgoing!$E:$E=F5)*(Outgoing!$F:$F=E5)),1),"")

Didn't know Aggregate formula can resolved my problem, I am not aware of this formula till today. Sorry for being noob..

Thank you so much !

Cheers

 

[Aladin Akyurek]

This might be faster, assuming that the dates are in chronological order:

=IFERROR(LOOKUP(9.99999999999999E+307,1/(Outgoing!$F$12:$F$1275=$E5)/(Outgoing!$E$12:$E$1275=$F5),Outgoing!$A$12:$A$1275),"")

I would suggest using dynamic named ranges instead of whole column references. This amounts to the following.

Define Lrow in the Name Manager as referring to:

=MATCH(9.99999999999999E+307,Outgoing!$A:$A)

Define also the following:

Date >>

=Outgoing!$A$12:INDEX(Outgoing!$A:$A,Lrow)

Serial >>

=Outgoing!$F$12:INDEX(Outgoing!$F:$F,Lrow)

Model >>

=Outgoing!$E$12:INDEX(Outgoing!$E:$E,Lrow)

The look up formula now becomes:

=IFERROR(LOOKUP(9.99999999999999E+307,1/(Serial=$E5)/(Model=$F5),Date),"")

 

[Peter_SSs]

..abide with the array formula press CTRL-SHIFT-ENTER. But it still returned me 0-Jan-00.

In your first sample file, you apparently realised that the formula suggested by Special-K99 of

=MAX(IF((F5=Outgoing!E$12:E$1175)*(E5=Outgoing!F$12:F$1175),A$12:A$1175)) (Array-entered)

Needed to be changed to reference column A on the Outgoing sheet and you used
=MAX(IF((Outgoing!$E:$E=F5)*(Outgoing!$F:$F=E5),Outgoing!$A:$A)) (but you forgot to array-enter it with Ctrl+Shift+Enter

In your latest file, you have remembered to array-enter the formula (& restrict the row references) but have now forgotten that red reference to the Outgoing sheet for the column A range & that is why it is not working.
You have
{=MAX(IF((F5=Outgoing!E$12:E$1275)*(E5=Outgoing!F$12:F$1275),A$12:A$1275))}
You should have
{=MAX(IF((F5=Outgoing!E$12:E$1275)*(E5=Outgoing!F$12:F$1275),Outgoing!A$12:A$1275))}