Thread: Conditional format closest number in a range

I've google searched this site and cannot see any posts that accomplishes what I'm after. A few are close but I'm not getting the desired effect.

I have a number in cell L32, say 6214. I would like to conditional format cells M33:M44 so that it highlights the first number below it without going over the number higher than it.

So in this case, Aug - 5697 would be the conditional format.

Thanks in advance!

6214

Jan	712
Feb	1424
Mar	2136
Apr	2848
May	3560
Jun	4273
Jul	4985
Aug	5697
Sep	6409
Oct	7121
Nov	7833
Dec	8545

Provided the numbers are increasing as in the example, you could use the formula:

=M33=VLOOKUP($L$32,$M$33:$M$44,1,TRUE)


Select all the cells in the range M33:M44 and apply the formula.


Regards, TMS

Hi purdue,

Got your point and it is quite practical as well.
See the attached file and let us know if this is what you were looking for


Regards,
DILIPandey

<click on below 'star' if this helps>

Highlight the table > Condition Formatting > Custom Rule > Enter =MATCH($A$1,$B$4:$B$15,1)=ROW($A1)

You're welcome, thanks for the rep.

Post deleted

Thanks purdue..!!

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>