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

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
Joined
Dec 30, 2008
Messages
12,914
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 2, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 2, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 2, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top