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

 

[kwangyeow]

Hi Peter_SSs,

I have tried both formula mention from your last post, and Aggregate formula and get the return dates result on 70% rows but left some rows return 0 result.
Not sure why there is a inconsistency result from this formula.

1)=MAX(IF((Outgoing!$E:$E=F5)*(Outgoing!$F:$F=E5),Outgoing!$A:$A)) Array-entered
2)=MAX(IF((F5=Outgoing!E$12:E$1275)*(E5=Outgoing!F$12:F$1275),Outgoing!A$12:A$1275)) Array-entered
3)=AGGREGATE(14,6,Outgoing!$A:$A/((Outgoing!$E:$E=F5)*(Outgoing!$F:$F=E5)),1)


Column O is where I input this formula, and Column Q is my expected result I manually pull from the outgoing tab.

Google link: https://drive.google.com/open?id=1AuzPeCyw4j7ln9pt8JRggE3YeTpf7wy8

Please help..

Thank you so much

 

[Aladin Akyurek]

Hi Peter_SSs,

I have tried both formula mention from your last post, and Aggregate formula and get the return dates result on 70% rows but left some rows return 0 result.
Not sure why there is a inconsistency result from this formula.

1)=MAX(IF((Outgoing!$E:$E=F5)*(Outgoing!$F:$F=E5),Outgoing!$A:$A)) Array-entered
2)=MAX(IF((F5=Outgoing!E$12:E$1275)*(E5=Outgoing!F$12:F$1275),Outgoing!A$12:A$1275)) Array-entered
3)=AGGREGATE(14,6,Outgoing!$A:$A/((Outgoing!$E:$E=F5)*(Outgoing!$F:$F=E5)),1)


Column O is where I input this formula, and Column Q is my expected result I manually pull from the outgoing tab.

Google link: https://drive.google.com/open?id=1AuzPeCyw4j7ln9pt8JRggE3YeTpf7wy8

Please help..

Thank you so much

Your data is not clean.

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

returns 9-Mar-18 if we trim DSOX3034A. This means we have in the E range DSOX3034A with a trailing space.

=IFERROR(LOOKUP(9.99999999999999E+307,1/(Outgoing!$F$12:$F$69=$E6+0)/(Outgoing!$E$12:$E$69=$F6),Outgoing!$A$12:$A$69),"")

returns 8-Mar-17 if we transform the text number in $E6 into a number. This means 2400 in E6 is not a number while the F range houses 2400 as a true number.

It seems you have ditched the suggestion I made on wrong grounds. The moral: Please clean up your data.

 

[kwangyeow]

Hi Aladin Akyurek,

I'm sorry that I didn't try out the Lookup function as I am not familiar with this formula. What I read up is Lookup formula return value is valid if the table of data is not edited for example remove row or column. As for index and match formula is not affected by this, and the Outgoing data will keep increasing.

How can I clean up my data and so excel can look up the cell as what it is display ?

Thank you

 

[Peter_SSs]

An adaptation of the AGGREGATE formula that I posted earlier that I think should work with your existing data would be

=IFERROR(AGGREGATE(14,6,Outgoing!A$12:A$69/((TRIM(Outgoing!E$12:E$69)=F5&"")*(TRIM(Outgoing!F$12:F$69)=E5&"")),1),"No result")

Adjust the ranges but I would still avoid whole column references. Just use an upper row number greater than your data will ever get to (perhaps 5000?).

In any case I agree with Aladin that ideally your data would be better in 'tidy' & uniform format.

 

[Aladin Akyurek]

Hi Aladin Akyurek,

I'm sorry that I didn't try out the Lookup function as I am not familiar with this formula. What I read up is Lookup formula return value is valid if the table of data is not edited for example remove row or column. As for index and match formula is not affected by this, and the Outgoing data will keep increasing.

How can I clean up my data and so excel can look up the cell as what it is display ?

Thank you

Select the E range on Outgoing. Run Find/Replace for space character (replace space with nothing)

Select an unused cell and copy it.
Select again the E range, run Paste Special >> Add.

Run the foregoing recipe for the F range on Outgoing.

I'd suggest to record a macro while running the recipe manually. This would allow you to rerun it whenever it's needed.

Once done, try to implement dynamic named ranges I suggested in post #9 and, of course, the LOOKUP formula (without TRIM, etc.) for we have dates here in chronological order.

 

[kwangyeow]

Hi Aladin, Peter_SSs,

Thank you on those suggestion, i will try out and improve my excel knowledge. Work require us to use it, thus have to learn the hard way.

Cheers and have a good day !