Hi

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

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"Regards

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 8Put 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 SubFor 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

thanksRegards

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.

Ask Your Question

Weekly Poll

Would you trust self-driving commercial trucks?

Discuss in The Lounge

Poll History