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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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:
Upvote 0
If I'm understanding correctly maybe something like....

=MAX(INDEX((A2:A5=E2)*B2:B5,)) where E2 holds the PO Number
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0
Well,

Unfortunately, that is not the case. Column B contains date and text. Can anything be done?
 
Upvote 0
can you post a few rows of data? its just a little easier to see what you have to work with
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,211,792
Messages
6,103,998
Members
447,889
Latest member
birdman15

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
Back
Top