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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

bschwartz

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


Excel 2010
ABCDE
1PO NumberDateCommentsNewest
22020209/16/2014Open9/17/2014
32020209/2/2014Open
42020209/17/2014Closed
52020209/11/2014Open
62020219/15/2014Closed
Sheet1
Cell Formulas
RangeFormula
E2{=LARGE(IF(A2:A999=Sheet2!A1,B2:B999),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.



Excel 2010
A
1202020
Sheet2
 
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,210
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,982
Members
430,100
Latest member
namhnz

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