# 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
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Business Systems Analyst , ex-Senior Application EngineerCommented:
try array formula like:

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

check this further:

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

=DMAX(Database, Field, Criteria)

Field - date field, just refer to cell containing header
Criteria - for this you need to set up a small additional table.

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

Experts Exchange Solution brought to you by