finding non empty cellsl within a range

**enilegna** · 05-31-2013, 10:15 AM

Hi I would like to ask if someone can help me. Im trying to find the non empty cell values along my year column and also try to find out the corresponding year the value falls into.

I have attached the excel sheet. I dont do macro by the way just excel formula.

Thank you very much

**XOR LX** · 05-31-2013, 11:00 AM

Hi,

This array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) in O2 and then copied to other relevant cells:

=LARGE(IF($B2:$N2<>"",$B2:$N2),ROUNDUP(COLUMNS($A$1:A$1)/2,0))

This (non-array) formula in P2 and copied to other relevant cells:

=INDEX($B$1:$M$3,1,MATCH(O2,$B2:$M2,0))

Regards