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

maximusben

New Member
Joined
Jul 13, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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!


1604517065423.png
1604517578412.png
1604519137215.png
 

Some videos you may like

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
Joined
Oct 24, 2012
Messages
4,161
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Oct 24, 2012
Messages
4,161
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Jul 13, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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!
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,161
Office Version
  1. 365
Platform
  1. MacOS
sorry missed i had note linked
 

Watch MrExcel Video

Forum statistics

Threads
1,119,006
Messages
5,575,523
Members
412,672
Latest member
Tupelo1984
Top