# Find Latest value that meets Criteria

#### HeyItsDizzy

##### New Member
I am wondering how can i find the latest information that meets my criteria, because all my table are run form a formula we the "Blank" cells will always read as a')', there fore i am having a Countif, Sumif, Maxif, etc issues, i am probably just typing my formula incorrectly.

Based on the following table, I need the Red X to equal '5'. Lets say for example i have already collated my data and all I am looking at is Salesmen 'John', lets also preten that John made not sales in Month 3 because he was on vacation, but all i want to know when that last sale was made in this case the '5th' month was his latest sale. as stated all cells will have numbers in them as that infor is fed through other formulas, as you know if a formula returns 'Nil' as a result then a '0' is placed in the sell

 Name John Lates sale made 'x' 'th Month Name Month Sales Made Sales (\$) John 1 1 100 John 2 5 200 John 3 0 0 John 4 2 200 John 5 4 300 John 6 0 0

<tbody>
</tbody>

Hi, Try Below:

#### Sam_D_Ben

##### Active Member
Perfect, i like it.

#### DanteAmor

##### Well-known Member

=LOOKUP(2,1/((C5:C10 > 0)*(A5:A10=C1)),(B5:B10))

#### Aryatect

##### Active Member
Another option:

=MAXIFS(B5:B10,A5:A10,C1,C5:C10,">"&0)

#### HeyItsDizzy

##### New Member
Thank you So much! all 3 options work perfectly!,

#1. =AGGREGATE(14,6,(\$A\$5:\$A\$10=\$C\$1)*(\$C\$5:\$C\$10>0)*(\$B\$5:\$B\$10),1)
- By Aryatect

#2. =LOOKUP(2,1/((C5:C10 > 0)*(A5:A10=C1)),(B5:B10))
-
By DanteAmor

#3. =MAXIFS(B5:B10,A5:A10,C1,C5:C10,">"&0)
- By Aryatect

Now depending how complex the overall task is, with over 100 salesmen across 12 different sheets I will try them all and see which works best for larger data bases

Last edited:

#### Aryatect

##### Active Member
Glad we could help and thanks for the feedback !