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

#### maximusben

##### New Member
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.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### etaf

##### Well-known Member
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
ABCD
1OrderWorktypeCivilNon-Civil
2126418contractAC
3135720contractFG
WORK_ORDER
Cell Formulas
RangeFormula
C2:D3C2=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
ABCDEFGHI
1Order NoDateStatusOrder type
212641810/25/20ACivil126418Civil10/25/20A
312641810/23/20BCivil135720Civil10/14/20F
412641810/22/20CNon-Civil
512641810/16/20DCivil
612641810/15/20ENon-Civil
713572010/14/20FCivil
813572010/7/20Gnon-Civil
913572010/7/20HCivil
1013572010/5/20ICivil
1113572010/2/20JCivil
12
13
Contract
Cell Formulas
RangeFormula
H2:H3H2=MAXIFS(B:B,A:A,F2,D:D,G2)
I2:I3I2=INDEX(C:C,MATCH(MAXIFS(B:B,A:A,F2,D:D,G2),B:B,0))

#### etaf

##### Well-known Member
I have added the General_contract sheet now as well

Lookup 2 tables MAXIFS - ETAF.xlsx
ABCD
1OrderWorktypeCivilNon-Civil
2126418contractC-1C-3
3135720contractC-6C-7
4126418General_contractGC-1GC-3
5135720General_contractGC-6GC-7
WORK_ORDER
Cell Formulas
RangeFormula
C2:D5C2=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
ABCDEFGHI
1Order NoDateStatusOrder typeOrderTypeMAX DATESTATUS
212641810/25/20C-1Civil
312641810/23/20C-2Civil126418Civil10/25/20C-1
412641810/22/20C-3Non-Civil126418non-Civil10/22/20C-3
512641810/16/20C-4Civil
612641810/15/20C-5Non-Civil
713572010/14/20C-6Civil135720Civil10/14/20C-6
813572010/7/20C-7non-Civil135720non-Civil10/7/20C-7
913572010/7/20C-8Civil
1013572010/5/20C-9Civil
1113572010/2/20C-10Civil
Contract
Cell Formulas
RangeFormula
H3:H4,H7:H8H3=MAXIFS(B:B,A:A,F3,D:D,G3)
I3:I4,I7:I8I3=INDEX(C:C,MATCH(MAXIFS(B:B,A:A,F3,D:D,G3),B:B,0))

Lookup 2 tables MAXIFS - ETAF.xlsx
ABCDEFGHI
1Order NoDateStatusOrder typeOrderTypeMAX DATESTATUS
212641810/25/20GC-1Civil
312641810/23/20GC-2Civil126418Civil10/25/20GC-1
412641810/22/20GC-3Non-Civil126418non-Civil10/22/20GC-3
512641810/16/20GC-4Civil
612641810/15/20GC-5Non-Civil
713572010/14/20GC-6Civil135720Civil10/14/20GC-6
813572010/7/20GC-7non-Civil135720non-Civil10/7/20GC-7
913572010/7/20GC-8Civil
1013572010/5/20GC-9Civil
1113572010/2/20GC-10Civil
General_Contract
Cell Formulas
RangeFormula
H3:H4,H7:H8H3=MAXIFS(B:B,A:A,F3,D:D,G3)
I3:I4,I7:I8I3=INDEX(C:C,MATCH(MAXIFS(B:B,A:A,F3,D:D,G3),B:B,0))

#### maximusben

##### New Member
I have added the General_contract sheet now as well

Lookup 2 tables MAXIFS - ETAF.xlsx
ABCD
1OrderWorktypeCivilNon-Civil
2126418contractC-1C-3
3135720contractC-6C-7
4126418General_contractGC-1GC-3
5135720General_contractGC-6GC-7
WORK_ORDER
Cell Formulas
RangeFormula
C2:D5C2=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
ABCDEFGHI
1Order NoDateStatusOrder typeOrderTypeMAX DATESTATUS
212641810/25/20C-1Civil
312641810/23/20C-2Civil126418Civil10/25/20C-1
412641810/22/20C-3Non-Civil126418non-Civil10/22/20C-3
512641810/16/20C-4Civil
612641810/15/20C-5Non-Civil
713572010/14/20C-6Civil135720Civil10/14/20C-6
813572010/7/20C-7non-Civil135720non-Civil10/7/20C-7
913572010/7/20C-8Civil
1013572010/5/20C-9Civil
1113572010/2/20C-10Civil
Contract
Cell Formulas
RangeFormula
H3:H4,H7:H8H3=MAXIFS(B:B,A:A,F3,D:D,G3)
I3:I4,I7:I8I3=INDEX(C:C,MATCH(MAXIFS(B:B,A:A,F3,D:D,G3),B:B,0))

Lookup 2 tables MAXIFS - ETAF.xlsx
ABCDEFGHI
1Order NoDateStatusOrder typeOrderTypeMAX DATESTATUS
212641810/25/20GC-1Civil
312641810/23/20GC-2Civil126418Civil10/25/20GC-1
412641810/22/20GC-3Non-Civil126418non-Civil10/22/20GC-3
512641810/16/20GC-4Civil
612641810/15/20GC-5Non-Civil
713572010/14/20GC-6Civil135720Civil10/14/20GC-6
813572010/7/20GC-7non-Civil135720non-Civil10/7/20GC-7
913572010/7/20GC-8Civil
1013572010/5/20GC-9Civil
1113572010/2/20GC-10Civil
General_Contract
Cell Formulas
RangeFormula
H3:H4,H7:H8H3=MAXIFS(B:B,A:A,F3,D:D,G3)
I3:I4,I7:I8I3=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!

Replies
2
Views
136
Replies
14
Views
138
Replies
8
Views
450
Replies
8
Views
283
Replies
1
Views
290