Formula help

[vharp91]

I need a formula that will lookup the latest quarter and return the word "latest" and return blank from a range of cells with quarter data in it. Below is a screenshot of what I am working on. So I need column F to populate "latest" in each cell where there is the latest quarter data in column G. So for the below data set, cells F3:F5 and F11 would populate the word "latest" while the other cells in column F would remain blank when looking up from the range of G3:G11. Thank you!

 

[Trebor76]

Hi vharp91,

There's probably a more efficient way but based on the formula from here this will do the job when pasted into cell F3 and copied down to F11:

=IF(G3=LOOKUP(2,1/(COUNTIF($G$3:$G$11,">"&$G$3:$G$11)=0),$G$3:$G$11),LOOKUP(2,1/(COUNTIF($G$3:$G$11,">"&$G$3:$G$11)=0),$G$3:$G$11),"")

Regards,

Robert

 

[vharp91]

Hi vharp91,

There's probably a more efficient way but based on the formula from here this will do the job when pasted into cell F3 and copied down to F11:

=IF(G3=LOOKUP(2,1/(COUNTIF($G$3:$G$11,">"&$G$3:$G$11)=0),$G$3:$G$11),LOOKUP(2,1/(COUNTIF($G$3:$G$11,">"&$G$3:$G$11)=0),$G$3:$G$11),"")

Regards,

Robert

That formula did not work

 

[Phuoc]

Try this:

=IF(COUNTIF($G$3:$G$11,">"&G3)=0,"latest","")

 

[Trebor76]

That formula did not work

That's odd - it did for me

When you say "did not work" what do you actually mean? Was nothing returned, was an error produced something else??

 

[vharp91]

Try this:

=IF(COUNTIF($G$3:$G$11,">"&G3)=0,"latest","")

This worked! Thank you so much!