Vlookup or Formula to pull the earliest and latest date.

[nike]

Hello everyone,

I'm trying to do a vlookup without success to return the earliest and latest date in column K and column N, where it pulls the dates in Payment Applied tab (column C). Some clients will have several dates but i only need the earliest and latest date populate in column K and N. Also to calculate the day difference compared to Settle Date (column J), and to populate in column L and O. Once it has the day difference counts, if it has 1-5 days difference to put 2%, 5-15 to put 5%, and 15 and above to 10%, to populate in column M and P. If someone can please help with a formula to make this work, I'd greatly appreciate it. Thank you in advance!



Payment Late (tab)

Payment Applied (tab)

 

[Anthony47]

Which version of Excel do you use? Is the function MAXIFS available?

 

[nike]

Thanks for your replay, I have excel office 365, and it does have the MAXIFS function.

 

[Anthony47]

Then you may use MINIFS and MAXIFS instead of VLookup. For example in K2:

=MAXIFS('Payment Applied'!$C$2:$C$100, 'Payment Applied'!$A$2:$A$100, A2)

(check the syntax on your real data)

Bye

 

[Fluff]

I have excel office 365,

Can you please update your account details to show this. It saves members having to ask.

 

[nike]

Thanks for the formula, unfortunately it wasn't working, but when i selected the whole columns it gave the dates with invalid dates which is not even in the range of the earliest and latest date for the client. Could it be the formatting of the dates?

=MAXIFS('Payments Applied'!$C:$C,'Payments LATE'!$A:$A,A2)

 

[Anthony47]

=MAXIFS('Payments Applied'!$C:$C,'Payments LATE'!$A:$A,A2)

I don't have your workbook to test, but I seem you are filtering the wrong column: based on the images I think yu have to use

=MAXIFS('Payments Applied'!$C:$C,'Payments Applied'!$A:$A,A2)

I.e. you examine 'Payments Applied' column A; those rows whose value match A2 (in 'Payments Late') are used to calculate the Max value in column C

Or share a sample of your workbook and we will tune the formula

Bye

 

[nike]

Thank you Anthony47! That worked, very much appreciated

 

[Anthony47]

Thank you for the feedback