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

 

[bschwartz]

Maybe this?

Excel 2010
	A	B	C	D	E
1	PO Number	Date	Comments		Newest
2	202020	9/16/2014	Open		9/17/2014
3	202020	9/2/2014	Open
4	202020	9/17/2014	Closed
5	202020	9/11/2014	Open
6	202021	9/15/2014	Closed
Sheet1

Cell Formulas
Range	Formula
E2	{=LARGE(IF(A2:A999=Sheet2!A1,B2:B999),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Excel 2010
	A
1	202020
Sheet2

 

[Weazel]

If I'm understanding correctly maybe something like....

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

 

[Mr Ramos]

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]

Hi Weazel,

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

 

[Mr Ramos]

Weasel,

Formula returns a NUM# error. Advise please

 

[Weazel]

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

	A	B	C	D	E
1	PO Number	Date	Comments		PO Number
2	202020	9/16/2014	Open		202020
3	202020	9/2/2014	Open		9/17/2014
4	202020	9/17/2014	Closed
5	202020	9/11/2014	Open
6	202021	9/15/2014	Closed

<tbody>
</tbody>

Sheet12​

Worksheet Formulas Cell Formula E3 =MAX(INDEX((A2:A6=E2)*B2:B6,)) <tbody> </tbody>

<tbody>
</tbody>

 

[Mr Ramos]

Well,

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

 

[Weazel]

can you post a few rows of data? its just a little easier to see what you have to work with

 

[Aladin Akyurek]

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:

=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.