Excel - Max date by range from another workbook

[alythree3]

Hello,

This is my first time posting in such a forum.
I'm not very good with excel but I understand the basics.
I've tried looking around the internet for a fitting formula to my problem and couldn't find something specific.

I need a formula that will take 2 numbers from Workbook 1 Columns A & B(1&3) for example.
To search them as a range in Workbook 2 which will return to Column C the Max date in Column F between numbers A-B. (1-3)
To simplify here is an Example:

Workbook 1:
A B C
1 3
4 5
6 8
9 10

Workbook 2:
D F
1 15/04/2021
2 18/04/2021
3 12/03/2021

4 15/04/2021
5 15/04/2021

6 11/04/2021
7 12/04/2021
8 13/04/2021

9 02/05/2021
10 15/04/2021

Thank you for any help.

 

[jasonb75]

The easiest way would be with MAXIFS. Where the ranges, F:F and D:D refer to those in the second workbook, so the workbook and sheet names will need to be included as well. If you're not sure how to write it correctly, simply type =MAXIFS( into the first workbook, then click on column F in the second workbook and it will enter it for you.

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	From	To	Date	Number		Date
2	1	3	18/04/2021	1		15/04/2021
3	4	5	15/04/2021	2		18/04/2021
4	6	8	13/04/2021	3		12/03/2021
5	9	10	02/05/2021	4		15/04/2021
6				5		15/04/2021
7				6		11/04/2021
8				7		12/04/2021
9				8		13/04/2021
10				9		02/05/2021
11				10		15/04/2021
Sheet7

Cell Formulas
Range	Formula
C2:C5	C2	=MAXIFS(F:F,D:D,">="&A2,D:D,"<="&B2)

 

[alythree3]

The easiest way would be with MAXIFS. Where the ranges, F:F and D:D refer to those in the second workbook, so the workbook and sheet names will need to be included as well. If you're not sure how to write it correctly, simply type =MAXIFS( into the first workbook, then click on column F in the second workbook and it will enter it for you.

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	From	To	Date	Number		Date
2	1	3	18/04/2021	1		15/04/2021
3	4	5	15/04/2021	2		18/04/2021
4	6	8	13/04/2021	3		12/03/2021
5	9	10	02/05/2021	4		15/04/2021
6				5		15/04/2021
7				6		11/04/2021
8				7		12/04/2021
9				8		13/04/2021
10				9		02/05/2021
11				10		15/04/2021
Sheet7

Cell Formulas
Range	Formula
C2:C5	C2	=MAXIFS(F:F,D:D,">="&A2,D:D,"<="&B2)

I don't know why, I simply selected the cells in the columns I needed according to the formula you gave me exactly. and for some reason it keeps returning 0.

 

[alythree3]

The easiest way would be with MAXIFS. Where the ranges, F:F and D:D refer to those in the second workbook, so the workbook and sheet names will need to be included as well. If you're not sure how to write it correctly, simply type =MAXIFS( into the first workbook, then click on column F in the second workbook and it will enter it for you.

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	From	To	Date	Number		Date
2	1	3	18/04/2021	1		15/04/2021
3	4	5	15/04/2021	2		18/04/2021
4	6	8	13/04/2021	3		12/03/2021
5	9	10	02/05/2021	4		15/04/2021
6				5		15/04/2021
7				6		11/04/2021
8				7		12/04/2021
9				8		13/04/2021
10				9		02/05/2021
11				10		15/04/2021
Sheet7

Cell Formulas
Range	Formula
C2:C5	C2	=MAXIFS(F:F,D:D,">="&A2,D:D,"<="&B2)

=MAXIFS('[Workbook2.xlsx]Sheet1'!$AG$4:$AG$5553,'[Workbook2.xlsx]Sheet1'!$B$4:$B$5553,">="&A5,'[Workbook2.xlsx]Sheet1'!$B$4:$B$5553,"<="&B5)

 

[alythree3]

I don't know why, I simply selected the cells in the columns I needed according to the formula you gave me exactly. and for some reason it keeps returning 0.

Never mind! It works!!! Thank you so so much! I wish you have a wonderful day like you.