Return value based on latest (max) date using multiple matching criteria

[maximusben]

Hello Excel users, may I please get your help?
I would like to return the latest (max) value based on multiple matching criteria.
I have a workbook with three excel sheets - Work_Order, General_Contract, and Contract. In the Work_Order sheet is where I want to have the results from the other two sheets; if the order number (Column A) has "Contract" as the Work Type (Column B), then I need to lookup the order number in the Contract worksheet, find the latest (max) Completion Date (Column B) for Order Type (Column D), and return the value in "Construction/Operation Status" (Column C) . For example, in the Work_Order sheet, order number 126418 has "Contract" as the Work Type, so I need to lookup the order number in the Contract Worksheet, find the latest Completion Date for "Civil" Order Type, in this case it is 10/25/2020, so I need to pull in cell c2 "Customer not ready" into cell c2 in the Work_Order sheet. The latest Completion Date for "Non-Civil" Order Type, in this case it is 10/22/2020, so I need to pull in cell c4 "Customer not ready" into cell d2 in the Work_Order sheet. If the Work Type for an order is "General_Contract", then I need to perform the same lookup in the General_Contract worksheet using the same logic. I've attached an example in excel.
Thanks in advance for your help!

 

[etaf]

try
=INDEX(INDIRECT($B2&"!C:C"),MATCH(MAXIFS(INDIRECT($B2&"!B:B"),INDIRECT($B2&"!A:A"),$A2,INDIRECT($B2&"!D:D"),C$1),INDIRECT($B2&"!B:B"),0))
As this is quite complicated I have also attached a dropbox link, BUT the spreadsheet will be deleted in a month time
hence the details below

Book2
	A	B	C	D
1	Order	Worktype	Civil	Non-Civil
2	126418	contract	A	C
3	135720	contract	F	G
WORK_ORDER

Cell Formulas
Range	Formula
C2:D3	C2	=INDEX(INDIRECT($B2&"!C:C"),MATCH(MAXIFS(INDIRECT($B2&"!B:B"),INDIRECT($B2&"!A:A"),$A2,INDIRECT($B2&"!D:D"),C$1),INDIRECT($B2&"!B:B"),0))

Book2
	A	B	C	D	E	F	G	H	I
1	Order No	Date	Status	Order type
2	126418	10/25/20	A	Civil		126418	Civil	10/25/20	A
3	126418	10/23/20	B	Civil		135720	Civil	10/14/20	F
4	126418	10/22/20	C	Non-Civil
5	126418	10/16/20	D	Civil
6	126418	10/15/20	E	Non-Civil
7	135720	10/14/20	F	Civil
8	135720	10/7/20	G	non-Civil
9	135720	10/7/20	H	Civil
10	135720	10/5/20	I	Civil
11	135720	10/2/20	J	Civil
12
13
Contract

Cell Formulas
Range	Formula
H2:H3	H2	=MAXIFS(B:B,A:A,F2,D:D,G2)
I2:I3	I2	=INDEX(C:C,MATCH(MAXIFS(B:B,A:A,F2,D:D,G2),B:B,0))

 

[etaf]

I have added the General_contract sheet now as well

Lookup 2 tables MAXIFS - ETAF.xlsx
	A	B	C	D
1	Order	Worktype	Civil	Non-Civil
2	126418	contract	C-1	C-3
3	135720	contract	C-6	C-7
4	126418	General_contract	GC-1	GC-3
5	135720	General_contract	GC-6	GC-7
WORK_ORDER

Cell Formulas
Range	Formula
C2:D5	C2	=INDEX(INDIRECT($B2&"!C:C"),MATCH(MAXIFS(INDIRECT($B2&"!B:B"),INDIRECT($B2&"!A:A"),$A2,INDIRECT($B2&"!D:D"),C$1),INDIRECT($B2&"!B:B"),0))

Lookup 2 tables MAXIFS - ETAF.xlsx
	A	B	C	D	E	F	G	H	I
1	Order No	Date	Status	Order type		Order	Type	MAX DATE	STATUS
2	126418	10/25/20	C-1	Civil
3	126418	10/23/20	C-2	Civil		126418	Civil	10/25/20	C-1
4	126418	10/22/20	C-3	Non-Civil		126418	non-Civil	10/22/20	C-3
5	126418	10/16/20	C-4	Civil
6	126418	10/15/20	C-5	Non-Civil
7	135720	10/14/20	C-6	Civil		135720	Civil	10/14/20	C-6
8	135720	10/7/20	C-7	non-Civil		135720	non-Civil	10/7/20	C-7
9	135720	10/7/20	C-8	Civil
10	135720	10/5/20	C-9	Civil
11	135720	10/2/20	C-10	Civil
Contract

Cell Formulas
Range	Formula
H3:H4,H7:H8	H3	=MAXIFS(B:B,A:A,F3,D:D,G3)
I3:I4,I7:I8	I3	=INDEX(C:C,MATCH(MAXIFS(B:B,A:A,F3,D:D,G3),B:B,0))

Lookup 2 tables MAXIFS - ETAF.xlsx
	A	B	C	D	E	F	G	H	I
1	Order No	Date	Status	Order type		Order	Type	MAX DATE	STATUS
2	126418	10/25/20	GC-1	Civil
3	126418	10/23/20	GC-2	Civil		126418	Civil	10/25/20	GC-1
4	126418	10/22/20	GC-3	Non-Civil		126418	non-Civil	10/22/20	GC-3
5	126418	10/16/20	GC-4	Civil
6	126418	10/15/20	GC-5	Non-Civil
7	135720	10/14/20	GC-6	Civil		135720	Civil	10/14/20	GC-6
8	135720	10/7/20	GC-7	non-Civil		135720	non-Civil	10/7/20	GC-7
9	135720	10/7/20	GC-8	Civil
10	135720	10/5/20	GC-9	Civil
11	135720	10/2/20	GC-10	Civil
General_Contract

Cell Formulas
Range	Formula
H3:H4,H7:H8	H3	=MAXIFS(B:B,A:A,F3,D:D,G3)
I3:I4,I7:I8	I3	=INDEX(C:C,MATCH(MAXIFS(B:B,A:A,F3,D:D,G3),B:B,0))

 

[maximusben]

I have added the General_contract sheet now as well

Lookup 2 tables MAXIFS - ETAF.xlsx
	A	B	C	D
1	Order	Worktype	Civil	Non-Civil
2	126418	contract	C-1	C-3
3	135720	contract	C-6	C-7
4	126418	General_contract	GC-1	GC-3
5	135720	General_contract	GC-6	GC-7
WORK_ORDER

Cell Formulas
Range	Formula
C2:D5	C2	=INDEX(INDIRECT($B2&"!C:C"),MATCH(MAXIFS(INDIRECT($B2&"!B:B"),INDIRECT($B2&"!A:A"),$A2,INDIRECT($B2&"!D:D"),C$1),INDIRECT($B2&"!B:B"),0))

Lookup 2 tables MAXIFS - ETAF.xlsx
	A	B	C	D	E	F	G	H	I
1	Order No	Date	Status	Order type		Order	Type	MAX DATE	STATUS
2	126418	10/25/20	C-1	Civil
3	126418	10/23/20	C-2	Civil		126418	Civil	10/25/20	C-1
4	126418	10/22/20	C-3	Non-Civil		126418	non-Civil	10/22/20	C-3
5	126418	10/16/20	C-4	Civil
6	126418	10/15/20	C-5	Non-Civil
7	135720	10/14/20	C-6	Civil		135720	Civil	10/14/20	C-6
8	135720	10/7/20	C-7	non-Civil		135720	non-Civil	10/7/20	C-7
9	135720	10/7/20	C-8	Civil
10	135720	10/5/20	C-9	Civil
11	135720	10/2/20	C-10	Civil
Contract

Cell Formulas
Range	Formula
H3:H4,H7:H8	H3	=MAXIFS(B:B,A:A,F3,D:D,G3)
I3:I4,I7:I8	I3	=INDEX(C:C,MATCH(MAXIFS(B:B,A:A,F3,D:D,G3),B:B,0))

Lookup 2 tables MAXIFS - ETAF.xlsx
	A	B	C	D	E	F	G	H	I
1	Order No	Date	Status	Order type		Order	Type	MAX DATE	STATUS
2	126418	10/25/20	GC-1	Civil
3	126418	10/23/20	GC-2	Civil		126418	Civil	10/25/20	GC-1
4	126418	10/22/20	GC-3	Non-Civil		126418	non-Civil	10/22/20	GC-3
5	126418	10/16/20	GC-4	Civil
6	126418	10/15/20	GC-5	Non-Civil
7	135720	10/14/20	GC-6	Civil		135720	Civil	10/14/20	GC-6
8	135720	10/7/20	GC-7	non-Civil		135720	non-Civil	10/7/20	GC-7
9	135720	10/7/20	GC-8	Civil
10	135720	10/5/20	GC-9	Civil
11	135720	10/2/20	GC-10	Civil
General_Contract

Cell Formulas
Range	Formula
H3:H4,H7:H8	H3	=MAXIFS(B:B,A:A,F3,D:D,G3)
I3:I4,I7:I8	I3	=INDEX(C:C,MATCH(MAXIFS(B:B,A:A,F3,D:D,G3),B:B,0))

Thank you so much for your help ETAF. Unfortunately, I still cannot get it to work. Do you have the link to your dropbox for this exercise?

Thank you!

 

[etaf]

sorry missed i had note linked

Dropbox - File Deleted - Simplify your life

www.dropbox.com