Excel - Max date by range from another workbook

alythree3

New Member
Joined
May 2, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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)
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top