Latest Date based on criteria in Ecel

Wanted to get latest date from column C that is not 100% from column B based on column A criteria.  See highlighted yellow, green and brown on attachment.
C--Users-lfreund-Desktop-LATEST-DAT.xlsx
LUIS FREUNDAsked:
Who is Participating?
 
Ejgil HedegaardConnect With a Mentor Commented:
With a normal formula.
Assy no in G2 then latest date < 100 % in H2.
=MAX(INDEX(($A$2:$A$86=G2)*($B$2:$B$86<1)*$C$2:$C$86,,))
See sheet
C--Users-lfreund-Desktop-LATEST-DAT.xlsm
0
 
Ryan ChongConnect With a Mentor Commented:
try array formula like:

=MAX(IF(A2:A100=G4,C2:C100))

Open in new window


check this further:

Max if criteria match
https://exceljet.net/formula/max-if-criteria-match
C--Users-lfreund-Desktop-LATEST-DAT.xlsx
0
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
For puling out a single value for a set of criteria you can use the DMAX function. Syntax:

=DMAX(Database, Field, Criteria)

Database - your data in columns A to C, including headers
Field - date field, just refer to cell containing header
Criteria - for this you need to set up a small additional table.

So for your data:
=DMAX($A$1:$C$86,C1,$I$1:$J$2)

In I1:J2 I have created the following:
ASSY_NO      PERCENT
900-62350      <1
0
 
LUIS FREUNDAuthor Commented:
AWESOME!  Thank you guys for the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.