Yes, you are correct.

thank you for your prompt attention in addressing the most critical portion
of my posting.

I really do appreciate it.

It looks like the original table is a second order polynomial function that
has been rounded to the nearest integer or tenth of an integer.  The
following formula should give you a close enough answer

=-0.54128+0.01848*A1-0.000045*A1^2

where A1 is the cell containing the value you want to convert

"Niek Otten" <nicol...@xs4all.nl> wrote...

...

Why a udf? The OP's problem requires simple linear interpolation. If
the OP's table were in a range named Tbl, and the measured value
entered in a cell named MV, the resulting interpolated index value
would be given by the formulas

=((INDEX(Tbl,MATCH(MV,INDEX(Tbl,0,1))+1,1)-MV)*VLOOKUP(MV,Tbl,2)
+(MV-VLOOKUP(MV,Tbl,1))*INDEX(Tbl,MATCH(MV,INDEX(Tbl,0,1))+1,2))
/(INDEX(Tbl,MATCH(MV,INDEX(Tbl,0,1))+1,1)-VLOOKUP(MV,Tbl,1))

or

=TREND(OFFSET(Tbl,MATCH(MV,INDEX(Tbl,0,1))-1,1,2,1),
OFFSET(Tbl,MATCH(MV,INDEX(Tbl,0,1))-1,0,2,1),MV)

or if you use another cell named FI containing the formula

=MATCH(MV,INDEX(Tbl,0,1))

you could return the desired result with the formula

=TREND(INDEX(Tbl2,FI,2):INDEX(Tbl2,FI+1,2),
INDEX(Tbl2,FI,1):INDEX(Tbl2,FI+1,1),MV)

Perhaps none of these is elegant, but linear interpolation into a
table with the key values in the leftmost column is elementary. IMO,
simplicity is lost in your udf due to overly long variable names which
distract and obscure. Also, the udf should include initial error
checking to catch common errors.

I can't see any benefit to using a For loop repeatedly calling
Application.WorksheetFunction.Index vs a simple assignment calling
Application.WorksheetFunction.MATCH once. Also no clear benefit to
multiple Application.WorksheetFunction.Index calls to fetch table
values rather than using the .Cells property of the table range
object.

Finally, this is simple to generalize to tables containing more than 2
columns which would allow the user to specify which column to
interpolate. Default to the presumably most common 2nd column.

Function lininterp( _
x As Double, _
tbl As Range, _
Optional ycol As Long = 2 _
) As Variant
'------------------------
 Dim k As Long
 Dim xlo As Double, xhi As Double, ylo As Double, yhi As Double

 If tbl.Rows.Count < 2 Or tbl.Columns.Count < ycol Then
   lit = CVErr(xlErrRef)
   Exit Function
 End If

 If x < tbl.Cells(1, 1).Value2 _
  Or x >= tbl.Cells(t.Rows.Count, 1).Value2 Then
   lit = CVErr(xlErrNA)
   Exit Function
 End If

 k = Application.WorksheetFunction.Match(x, tbl.Resize(, 1))

 xlo = tbl.Cells(k, 1).Value2
 xhi = tbl.Cells(k + 1, 1).Value2
 ylo = tbl.Cells(k, ycol).Value2
 yhi = tbl.Cells(k + 1, ycol).Value2

 lininterp = ((xhi - x) * ylo + (x - xlo) * yhi) / (xhi - xlo)

End Function

[Digression: maybe long variable names serve some purpose in long and/
or complex nonmathematical procedures, but they're decidedly
deleterious in simple mathematical procedures.]

Very nice!
--ron

Now that's what I call elegance.
Very nice indeed Lori

wow.

I've now read up on the percentile & percentrank functions,
picked apart the statement and I see how this works-
that is amazing-  I am dumbfounded..  I haven't comprehended/absorbed this
into my
understanding of excel yet, but I must say- you have definately hit my
desire to a T

I was expecting something I'd have to merge with an if statement for when it
was a perfect table match result--
but that is an absolute killer, take a bow--  thank you, both for the answer
and the education....

Just wow.