Index, match, and max combined

Mr Ramos

New Member
Joined
Jan 24, 2014
Messages
17
Hello all,

I have a set of data pretty big. A sample given below

A B
PO NUmber Comments
202020 9/16/14 Open
202020 9/2/14 Open
202020 9/17/14 Closed
202020 9/11/14 Open

I have been successful at pulling information from column B by matching PO number from column A using an =INdex(B:B,match(true,index(A:A=sheet2A1,0),0). I want the most recent update. However, the function does not work with multiple entries because I always get the first found entry (in the example above 9/16/14 Open). Is there a way to modify the function to pull the most recent input by date?

Note: colunm B contains date and text.

Any help will be appreciatted
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

bschwartz

Well-known Member
Joined
Jan 5, 2013
Messages
1,367
Maybe this?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">PO Number</td><td style=";">Date</td><td style=";">Comments</td><td style="text-align: right;;"></td><td style=";">Newest</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">202020</td><td style="text-align: right;;">9/16/2014</td><td style=";">Open</td><td style="text-align: right;;"></td><td style="text-align: right;;">9/17/2014</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">202020</td><td style="text-align: right;;">9/2/2014</td><td style=";">Open</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">202020</td><td style="text-align: right;;">9/17/2014</td><td style=";">Closed</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">202020</td><td style="text-align: right;;">9/11/2014</td><td style=";">Open</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">202021</td><td style="text-align: right;;">9/15/2014</td><td style=";">Closed</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">{=LARGE(<font color="Blue">IF(<font color="Red">A2:A999=Sheet2!A1,B2:B999</font>),1</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">202020</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br />
 
Last edited:

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
If I'm understanding correctly maybe something like....

=MAX(INDEX((A2:A5=E2)*B2:B5,)) where E2 holds the PO Number
 

Mr Ramos

New Member
Joined
Jan 24, 2014
Messages
17
Hello fellow board member. Actually, column B contains both date and comments. You do not need column C. However, you did not use it on your answer so I will give it a go. Thanks
 

Mr Ramos

New Member
Joined
Jan 24, 2014
Messages
17

ADVERTISEMENT

Hi Weazel,

I will try it out tomorrow when I get to work. Thanks alot...
 

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155

ADVERTISEMENT

I'm not sure why you are getting a #NUM error, I was expecting a #value error after your reply in post 4.

If you have text comments in the same column as the Date then my formula wouldn't work since its looking for a number only

Ideally column B would need to be dates only

Excel 2012
ABCDE
1PO NumberDateCommentsPO Number
22020209/16/2014Open202020
32020209/2/2014Open9/17/2014
42020209/17/2014Closed
52020209/11/2014Open
62020219/15/2014Closed

<tbody>
</tbody>
Sheet12

Worksheet Formulas
CellFormula
E3=MAX(INDEX((A2:A6=E2)*B2:B6,))

<tbody>
</tbody>

<tbody>
</tbody>
 

Mr Ramos

New Member
Joined
Jan 24, 2014
Messages
17
Well,

Unfortunately, that is not the case. Column B contains date and text. Can anything be done?
 

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
can you post a few rows of data? its just a little easier to see what you have to work with
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Hello all,

I have a set of data pretty big. A sample given below

A B
PO NUmber Comments
202020 9/16/14 Open
202020 9/2/14 Open
202020 9/17/14 Closed
202020 9/11/14 Open

I have been successful at pulling information from column B by matching PO number from column A using an =INdex(B:B,match(true,index(A:A=sheet2A1,0),0). I want the most recent update. However, the function does not work with multiple entries because I always get the first found entry (in the example above 9/16/14 Open). Is there a way to modify the function to pull the most recent input by date?

Note: colunm B contains date and text.

Any help will be appreciatted

Let Sheet1, A:B, house the data.

Sheet2

A1 houses a PO number of interest.

A2, control+shift+enter, not just enter:
Rich (BB code):
=INDEX(Sheet1!$B$2:$B$5,MATCH(TEXT(MAX(IF(Sheet1!$A$2:$A$5=$A1,
  IF(REPLACE(Sheet1!$B$2:$B$5,1,FIND(" ",Sheet1!$B$2:$B$5),"")="Open",
  LEFT(Sheet1!$B$2:$B$5,FIND(" ",Sheet1!$B$2:$B$5))+0))),"m/d/yy")&" Open",
  Sheet1!$B$2:$B$5,0))

Note that the foregoing has the formula in Sheet2 and the data in Sheet1. Adjust to suit.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,911
Messages
5,525,593
Members
409,652
Latest member
strangelyangely

This Week's Hot Topics

Top