Finding Two closest match from array

I'm not eactly sure what you are planning to do with the closest matching numbers once they are found, so I'm just going to toss out a few options to find those numbers. Once you try these, either modify them to meet your purpose or come on back with further questions. If your Column A looks like this,then try the 2 formulas I've posted below: A 1 2 2 4 3 6 4 8 Put this in B1: =IF(OR(AND(A1<$C$1,A2>$C$1),AND(A1>$C$1,A2<$C$1)),A1,"") Put this in B2 and drag it down to the bottom of your list: =IF(OR(AND(A2<$C$1,A3>$C$1),AND(A2>$C$1,A1<$C$1)),A2,"") If you put e.g. 3 in C1, a 2 will be returned in B1, and a 4 will be returned in B2, showing you the 2 closest numbers. Another option is this code, which will ask you for a number and then highlight the 2 closest numbers in Column A. Sub ClosestMatch() 'Determine length of data in Column A lastRw = Range("A" & Rows.Count).End(xlUp).Row 'Clear highlighting in Column A Range("A1:A" & lastRw).Interior.ColorIndex = xlNone 'Get value from user myVal = Application.InputBox("Enter number", Type:=1) 'Quit if canceled If myVal = False Then Exit Sub 'Loop through data, highlight cells with value above 'input and value below input For x = 1 To lastRw If Cells(x, 1) < myVal And Cells(x + 1, 1) > myVal Then Cells(x, 1).Interior.ColorIndex = 6 Cells(x + 1, 1).Interior.ColorIndex = 6 End If Next End Sub For an input value of e.g. 5, the 4 and 6 will be highlighted. Obviously, these suggestions are not the final answer to your question, but I hope they are a way to get you started. Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you for replying . I was not clear about my problem. kindly Find the html link to the file http://www.mediafire.com/view/?nq73... I require values in Gray cells which are searched from table thanks Regards

Your image does not show column letters or row numbers so I don't know what cells your data table is in. Without that information I can't offer an exact solution. What cell is the input entered into, e.g. 33.51? Generically, it seems like VLOOKUP should work. Let's assume your input (33.51) is in F10. Let's assume your table is in A50:B100. For the lower value try: =VLOOKUP($F$10,$A$50:$B$100,2,1) For the higher value, try: =VLOOKUP($F$10+.5,$A$50:$B$100,2,1) Question: What do you want to happen if an exact match is entered, e.g. 33.5? Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.