# Excel - Max date by range from another workbook

#### alythree3

##### New Member
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.

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### jasonb75

##### Well-known Member
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
ABCDEF
1FromToDateNumberDate
21318/04/2021115/04/2021
34515/04/2021218/04/2021
46813/04/2021312/03/2021
591002/05/2021415/04/2021
6515/04/2021
7611/04/2021
8712/04/2021
9813/04/2021
10902/05/2021
111015/04/2021
Sheet7
Cell Formulas
RangeFormula
C2:C5C2=MAXIFS(F:F,D:D,">="&A2,D:D,"<="&B2)

#### alythree3

##### New Member
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
ABCDEF
1FromToDateNumberDate
21318/04/2021115/04/2021
34515/04/2021218/04/2021
46813/04/2021312/03/2021
591002/05/2021415/04/2021
6515/04/2021
7611/04/2021
8712/04/2021
9813/04/2021
10902/05/2021
111015/04/2021
Sheet7
Cell Formulas
RangeFormula
C2:C5C2=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

##### New Member
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
ABCDEF
1FromToDateNumberDate
21318/04/2021115/04/2021
34515/04/2021218/04/2021
46813/04/2021312/03/2021
591002/05/2021415/04/2021
6515/04/2021
7611/04/2021
8712/04/2021
9813/04/2021
10902/05/2021
111015/04/2021
Sheet7
Cell Formulas
RangeFormula
C2:C5C2=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

##### New Member
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.

Replies
5
Views
67
Replies
0
Views
33
Replies
10
Views
120
Replies
3
Views
68
Replies
3
Views
110

1,132,707
Messages
5,654,840
Members
418,156
Latest member
juliapearson

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back