Index Match multiple criteria with MAX & closed workbooks

spydey

Active Member
Joined
Sep 19, 2017
Messages
313
Office Version
  1. 2013
Platform
  1. Windows
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
 

Some videos you may like

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
Joined
Sep 19, 2017
Messages
313
Office Version
  1. 2013
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

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

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
Top