VBA To find nearest biggest number and lowest number

[gan_xl]

Hi Team,

Please kindly look at the attached file.
I need a VBA code to find the nearest biggest number and nearest lowest number between the data of D2 to H2.

In the attached file, I have mentioned my required output (Column A and B - blue highlighted)


I didn't get required VBA code for the post below, so I have posted here again

http://www.excelforum.com/excel-prog...ml#post3636085


Thanks

[:) Sixthsense :)]

In B6 Cell

=IFERROR(INDEX($D$2:$H$2,,MATCH(IF(COUNTIF($D$2:$H$2,B5),B5-1,B5),$D$2:$H$2,1)),0)

In B7 Cell

=IFERROR(SMALL($D$2:$H$2,COUNTIF($D$2:$H$2,"<="&D6)+1),"-")

Select B6 and B7 cells and do copy and paste it in B10:B11 & B14:B15

[gan_xl]

Thanks Sixthsense.
The second formula is not working as I requested. If I enter '100' in B5 then output would be '200' in B7 (nearest biggest number), however currently it shows '100' in B7. Can you please check it and help me to get out of this

Please Login or Register  to view this content.