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