Using MAX IF function with an operator

Ray167

New Member
Joined
Jun 9, 2015
Messages
7
Hi, I have been scratching my head at this problem the whole day and have not found a solution to it by myself. Basically I have two sheets, in the first sheet I need to find the latest date a material is to be delivered, but that date needs to be before today's date. The second sheet contains a list of materials and corresponding delivery dates for materials. I thought I could use the following formula
MAX(IF('sheet1'!A2='sheet2'A:A,'sheet2'!B:B< TODAY(),0)). This formula unfortunately does not work since I receive a 0 every time. Your input would be greatly appreciated.

Thanks
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,196
Office Version
  1. 365
Platform
  1. Windows
I am not sure what exactly your data looks like, but here is an example of using MAX If with Index Match.

<table><tr><td bgcolor = "#C00000"></td><td bgcolor = "#A6A6A6">A</td><td bgcolor = "#A6A6A6">B</td><td bgcolor = "#A6A6A6">C</td></tr><tr><td bgcolor = "#A6A6A6">1</td><td>7/13/2015</td><td>E</td><td>G</td></tr><tr><td bgcolor = "#A6A6A6">2</td><td>7/14/2015</td><td>F</td><td></td></tr><tr><td bgcolor = "#A6A6A6">3</td><td>7/15/2015</td><td>G</td><td></td></tr><tr><td bgcolor = "#A6A6A6">4</td><td>7/16/2015</td><td>H</td><td></td></tr><tr><td bgcolor = "#A6A6A6">5</td><td>7/17/2015</td><td>I</td><td></td></tr><tr><td bgcolor = "#A6A6A6">6</td><td>7/18/2015</td><td>J</td><td></td></tr><tr><td bgcolor = "#A6A6A6">7</td><td>7/19/2015</td><td>K</td><td></td></tr><tr><td bgcolor = "#A6A6A6">8</td><td>7/20/2015</td><td>L</td><td></td></tr><tr><td bgcolor = "#A6A6A6">9</td><td>7/21/2015</td><td>M</td><td></td></tr><tr><td bgcolor = "#A6A6A6">10</td><td>7/22/2015</td><td>N</td><td></td></tr><tr><td bgcolor = "#A6A6A6">11</td><td>7/23/2015</td><td>O</td><td></td></tr></table><br><br><table border = 3; bordercolor = "#33FF00"><tr><td><b>Formulas</b></td></tr><tr><td><table><tr><td bgcolor = "#A6A6A6">Cell</td><td bgcolor = "#A6A6A6">Formula</td></tr><tr><td>F3</td><td>{=INDEX($E$3:$E$13,MATCH(MAX(IF($D$3:$D$13<TODAY(),$D$3:$D$13)),$D$3:$D$13,0))}</td></tr></table></td></tr><tr><td><b>Array Formula</b><br>Enter formula while holding Ctrl + Shift + Enter</td></tr></table><br><b>LEGO HTML</b>


I don't know why it is cutting off the end of the formula. it should have ",0))" at the end of it.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,797
Office Version
  1. 2010
Platform
  1. Windows
@ lrobbo: Add a space following the < so it's not interpreted as the start of an HTML tag.

@ Ray: You don't want to use full-column references.
 

Ray167

New Member
Joined
Jun 9, 2015
Messages
7
Here is an example to go with my formula. Sorry didn't know how to make columns and rows, so basically I am looking for the delivery date of material AA on sheet one, but I need to find the MAX material date before TODAY()'s date. As you can tell the MAX date before TODAY()'s date, assuming today's date is 7/16/2015, would be 7/2/2015. I hope that clarifies what I am trying to achieve here.

Thanks


sheet 1

Column A Column B

Material QTY
AA 2
BB 5
CC 1
XX 3
DD 5


Sheet 2

Column A Column B

MATERIAL Delivery Dates
AA 6/20/2015
BB
CC
AA 7/2/2015
BB
XX
DD
AA 7/25/2015
 

Ray167

New Member
Joined
Jun 9, 2015
Messages
7

ADVERTISEMENT

@ lrobbo: Add a space following the < so it's not interpreted as the start of an HTML tag.

@ Ray: You don't want to use full-column references.

I need the full column because the sheet continuously grows unless I misunderstood what you meant.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,196
Office Version
  1. 365
Platform
  1. Windows
@ lrobbo: Add a space following the < so it's not interpreted as the start of an HTML tag.

@ Ray: You don't want to use full-column references.

SHG, I tried everything. I tried just putting the formula by itself outside of any html tags and somehow it was still cutting it off. It was really weird, i haven't ever seen anything like that before.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,797
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

I need the full column because the sheet continuously grows unless I misunderstood what you meant.
Do you want to take the time to evaluate a million rows when you need only a fraction of that?

Consider a dynamic named range: How to Create and Use Dynamic Range Names in Excel
 

Ray167

New Member
Joined
Jun 9, 2015
Messages
7
So I finally got a combination that works, really surprised it didn't hit me sooner.

=MAX(IF('sheet 1'!E2='sheet 2'!A:A,IF('sheet 2'!A:A<TODAY(),'sheet 2'!D:D)))
 

Forum statistics

Threads
1,136,258
Messages
5,674,678
Members
419,520
Latest member
Jennifer4Dillon

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