# Index, match, and max combined

#### Mr Ramos

##### New Member
Hello all,

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

A B
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
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:
If I'm understanding correctly maybe something like....

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

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

Hi Weazel,

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

Weasel,

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

Well,

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

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

Hello all,

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

A B
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.

Replies
13
Views
688
Replies
2
Views
171
Replies
3
Views
324
Replies
5
Views
268
Replies
1
Views
289

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.

### Which adblocker are you using?

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

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