# Thread: Find Value within Datarange

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

regards,

2. 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...match-function

3. Or maybe you want the trend

=TREND(result_range,lookup_range,lookup_value)

4. 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(D2:D12,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(D2:D12,MATCH(F3,C2:C12)):INDEX(D2:D12,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(D2:D12,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).

5. Oh groan…
https://www.excelforum.com/excel-for...datarange.html
This is a requirement, not just a request.