Using MAX IF function with an operator

[Ray167]

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

 

[lrobbo314]

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]

@ 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]

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]

@ 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]

@ 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]

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]

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

Will do thanks.

Irobbo314 did you have a chance to figure out how to paste your example? I will even take a screen shot if typing isn't working out.

Thanks

 

[Ray167]

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)))