how to match value with table Solved/Closed

Question

Hello,
 I have a problem to match the value from sheet 2 with another sheet name table. I want to compare if the value near with the value in the table then get the value to insert in the new field.  This is my table 

	Flowrate (m3/hr) at various flow velocity		
Size	     1.0 m/s	 2.0 m/s	   3.0 m/s
25 mm	1.77	         3.53	           5.30
50 mm	7.07   	14.14	21.21
80 mm	18.10	36.20	54.29
100 mm	28.28	56.56	84.83
150 mm	63.63	127.25	190.88
200 mm	113.11	226.22	339.34
250 mm	176.74	353.48	530.21
300 mm	254.50	509.00	763.51
350 mm	346.41	692.81	1,039.22
400 mm	452.45	904.90	1,357.34
450 mm	572.63	1,145.26	1,717.89
500 mm	706.95	1,413.90	2,120.85
600 mm	1,018.01	2,036.02	3,054.02

i want to get the size if  value same or near with the data in table list.

venkat1926 · Answer

your sheet is this 

Size(mm)	1 m/s	2 m/s	3 m/s
25	1.77	3.53	5.3
50	7.07	14.14	21.21
80	18.1	36.2	54.29
100	28.28	56.56	84.83
150	63.63	127.25	190.88
200	113.11	226.22	339.34
250	176.74	353.48	530.21
300	254.5	509	763.51
350	346.41	692.81	1,039.22
400	452.45	904.9	1,357.34
450	572.63	1,145.26	1,717.89
500	706.95	1,413.90	2,120.85
600	1,018.01	2,036.02	3,054.02

what is your "table" how is it configures.

with respect to above sheet what do you want to find?

venkat1926 · Answer

try this macsro on the sample sheet given previously BY NE
if it is ok use it in your file. BUT PARK THE ORIGINAL FILE SAFELY SOMEWHERE FOR RETRIEVAL. 
PLEASE POST FEEDBACK. 
The macro is 

Sub test()

Dim j As Double, k As Range, m As Range, r As Range
Dim z As Range, x As Range
Range("A1").CurrentRegion.Sort key1:=Columns("B:B"), header:=xlYes
Set r = Range(Range("B1"), Range("B1").End(xlDown))
Set k = Range("A1").End(xlDown).Offset(5, 0)
j = 27.78
k = "=vlookup(" & j & "," & r.Address & ",1,1)"
MsgBox k
With Columns("B:B")
Set x = Cells.Find(what:=k.Value, lookat:=xlWhole)
End With
MsgBox x.Address
If j - x.Value > x.Offset(1, 0).Value - j Then
Set z = x.Offset(1, 0)
Else
Set z = x
End If
MsgBox "the reqd value is    " & z.Offset(0, -1).Value
End Sub

How to match value with table

2 responses

Subscribe To Our Newsletter!