Find Value within Datarange

Hello,

I am trying to find a value between a range but unable to do that, i tried Index & match combination too but unable....

Level 0 10 20 30 40 50 60 70 80 90 100
Volume 0 0.34 0.97 1.75 2.63 3.55 4.47 5.35 6.13 6.76 7.1

I need to find Volume when Level will be 35? I want to use Level and Volume in dynamic range....

Please give me the solution....

regards,

You probably need to set the MATCH TYPE argument to 1:

=INDEX(result_range,MATCH(lookup_value,lookup_range,1))

https://exceljet.net/excel-functions/excel-match-function

Or maybe you want the trend

=TREND(result_range,lookup_range,lookup_value)

To do this one to death…
If you use the TREND formula using all the data, sometimes the results will be significantly off because the relationship is not a straight line:


using the chart, you'd expect a volume of roughly 2.2 for level 35.

Using a straight line through all the points you get 2.55 (dotted line on chart)
Formula:
=TREND(D212,C2:C12,F3,FALSE)

You can get better results using the small straight line between the points directly before and after 35, this gives you 2.19
Formula:
=TREND(INDEX(D212,MATCH(F3,C2:C12)):INDEX(D212,MATCH(F3,C2:C12)+1),INDEX(C2:C12,MATCH(F3,C2:C12)):INDEX(C2:C12,MATCH(F3,C2:C12)+1),F3)
or a bit shorter:
=TREND(OFFSET(C2:C12,MATCH(F3,C2:C12)-1,1,2),OFFSET(C2:C12,MATCH(F3,C2:C12)-1,0,2),F3)

You'll very likely get even better results using the fifth order polynomial (the what?) which is a good fit, which, surprisingly has a simpler (well, shorter) formula, and this gives you 2.18
Formula:
=SUMPRODUCT(LINEST(D212,C2:C12^{1,2,3,4,5},FALSE),F3^{5,4,3,2,1,0})

See attached workbook where you can alter the value in cell F3 (light green).

Dead horse flogged.

Oh groan…
cross posted without links:
https://www.excelforum.com/excel-formulas-and-functions/1364887-find-value-within-datarange.html
mfaisal.ce, for your information, you should always provide links to your cross posts.
This is a requirement, not just a request.
If you have cross posted at other places, please add links to them too.
Why? Have a read of http://www.excelguru.ca/content.php?184