Finding Two closest match from array

November 15, 2012 at 08:02:44
Specs: Windows XP, p4

Can you tell me how can i find closest two match of a number so that i can interpolate its vlaues eg

2 .22
4 .33
6 .55
8 .77
Now I ask to find 3 in column A, As its not present it take two values from column A which will be 2 and 4 and now interpolate it by straight line method to calculate value which can be aken as "B"


See More: Finding Two closest match from array

Report •

November 15, 2012 at 09:28:38

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:

1   2
2   4
3   6
4   8

Put this in B1:


Put this in B2 and drag it down to the bottom of your list:


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
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.

Report •

November 15, 2012 at 20:43:24

Thank you for replying . I was not clear about my problem. kindly Find the html link to the file

I require values in Gray cells which are searched from table


Report •

November 16, 2012 at 02:25:47

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:


For the higher value, try:


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.

Report •

Related Solutions

Ask Question