# Index Match multiple criteria with MAX & closed workbooks

#### spydey

##### Active Member
Hello to all.

I am trying to develop an in-cell formula with Index Match. The Match will have multiple criteria, with one of them being a MAX.
I cannot seem to get it correct, and I believe it is the MAX as a criteria of the Match which I am not doing correctly.
Also, this will be against a close workbook.

For reference, Workbook A contains the formulas, whereas Workbook B contains the data.
Workbook A contains the following fields: ID, Type, Status, Created Date, Expiration Date.
Workbook A: ID, can contain repeating IDs, but the combination of ID and Type will never repeat.
Workbook A: Type, contains multiple different values (types), such as shoe, shirt, pant, sock, hat, etc.
Workbook B contains ID, Type, Status, Created Date, Expiration Date.
Workbook B: ID, contains repeating IDs. There can be multiple of the same ID. However, the combination of ID, Type, Status, and Created Date will never duplicate.
Workbook B: Type, contains multiple different values (types), such as shoe, shirt, pant, sock, hat, etc.
Workbook B: Status, contains either Active or Inactive.
Workbook B: Created Date & Expiration Date, are simply dates.
The data in both workbooks is in a range format (no tables).

I need to pull over the Status from Workbook B to Workbook A based upon the ID, Type, and the most recent (MAX) created date.

So it would be something like this:

=INDEX([Workbook B]Sheet1!\$C:\$C,MATCH(1,([Workbook 8]Sheet1!\$A:\$A=A2)*([Workbook B]Sheet1!\$B:\$B=B2)*(MAX([Workbook B]Sheet1!\$D:\$D)),0),1)

In looking at the above formula, I see that the MAX doesn't have anything to compare the range to, like the other criterias have \$B:\$B=B2.
I believe that is the issue I am having, but I cannot figure out how to correctly incorporate the MAX into the formula.

Any thoughts, ideas, suggestions, you might have, would be greatly appreciated!

Thank you!!

-Spydey

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### spydey

##### Active Member
I think I figured it out using Index & Aggregate, but Holy Cow!!! Is it ever a long formula and take a while to process.

Any ideas on how I might get a much cleaner formula and/or one that is not as resource hungry?

Replies
6
Views
100
Replies
13
Views
153
Replies
10
Views
183
Replies
4
Views
555
Replies
1
Views
78

1,127,651
Messages
5,626,090
Members
416,161
Latest member
David1966Lewis

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

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