Index, match, and max combined

[Mr Ramos]

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

 

[Mr Ramos]

Weasel,

Lets change the scenario. What if we take your data set above and say that I want to pull the information on Column C, by matching PO in column A and from the most recent date on column B?
Remember the POs have duplicates and I want the result from the most recent input/

 

[Weazel]

Have you tried Aladin's formula based on your original question?

to address the change though maybe something like...

=INDEX(C2:C6,MATCH(MAX(IF(A2:A6=E2,B2:B6)),B2:B6,0)) Control shift enter

with E2 housing the PO number, Dates in Column B, and Comments in column C.

 

[Mr Ramos]

Aladin,

Thansk for your contribution. I tried your formula and adjusted as needed. I have a question about the "open". What are you referring to? I mean the notes vary from PO from PO, meaning I have all sorts of comments. For example

9/16/14 Job dispositioned

9/14/14 not received

9/16/13 In process

etc etc

 

[Aladin Akyurek]

Aladin,

Thansk for your contribution. I tried your formula and adjusted as needed. I have a question about the "open". What are you referring to? I mean the notes vary from PO from PO, meaning I have all sorts of comments. For example

9/16/14 Job dispositioned

9/14/14 not received

9/16/13 In process

etc etc

Then you should specify the comment of interest as a variable like PO number.

Sheet2

A1 houses a PO number of interest.

A2 houses a comment of interest.

A3, control+shift+enter, not just enter:

=INDEX(Sheet1!$B$2:$B$5,MATCH(TEXT(MAX(IF(Sheet1!$A$2:$A$5=A$1,
  IF(REPLACE(Sheet1!$B$2:$B$5,1,FIND(" ",Sheet1!$B$2:$B$5),"")=A$2,
  LEFT(Sheet1!$B$2:$B$5,FIND(" ",Sheet1!$B$2:$B$5))+0))),"m/d/yy")&" "&A$2,
  Sheet1!$B$2:$B$5,0))

You must realize that the comments separated from dates and put in cells of their own, say, in column C, gives you a lay-out tha is to process.

 

[Mr Ramos]

Aladin,

I agree. Problem is the dates and comments are on the same column (in my case column B). I cannot reference any coments or dates. All I would like to do is pull the info on column B by referencing the PO, taking into account that column contains dates and texts that are more than 100 characters long in some cases.
Is it possible?

Thanks for your help.

 

[Aladin Akyurek]

Aladin,

I agree. Problem is the dates and comments are on the same column (in my case column B). I cannot reference any coments or dates. All I would like to do is pull the info on column B by referencing the PO, taking into account that column contains dates and texts that are more than 100 characters long in some cases.
Is it possible?

Thanks for your help.

Didn't I alredy show that it's possible?
See the workbook how the suggested formula works:
https://dl.dropboxusercontent.com/u/65698317/Mr%20Ramos%20%20conditional%20max%20date%20tucked%20in%20text.xlsx

 

[Mr Ramos]

Aladin,

I checked your excel file. And it works great. But remember what I said. "I cannot reference any coments or dates". You are referencing the text "open" in your formula. How can I do that with column B which contains both dates and text that are more than 100 characters long in some cases.

Thanks for your input by the way.

 

[Aladin Akyurek]

Aladin,

I checked your excel file. And it works great. But remember what I said. "I cannot reference any coments or dates". You are referencing the text "open" in your formula. How can I do that with column B which contains both dates and text that are more than 100 characters long in some cases.

Thanks for your input by the way.

The formula references and processes the column B of Sheet1 which consists of date + comment strings. Have a better look at the file provided to you for perusal.