How to get value corresponding to the input value from a table

[digimart]

Hi,
I have a table with 2 columns, copy attached.
One column has length ( in millimeters ), and other has volume ( in liters ).

a. I would like that if I input a length in mm, then its corresponding volume be displayed.
b. At number of times, the length I input will not be present in the data table. In such case I be displayed the entry above and below than my figure. And further based on these two entries, the average volume be displayed.

Example from the attached table :

(a) I input 400, so in this case the volume corresponding to 400 being 2326 liters be displayed.
(b) I input 428. Now there is no such entry in the table, rather there is 425 and then 430. So in such case the differences between these table values calculated ; meaning 430-425 = 5, and then 2586 - 2534 = 52. Then divide these numbers; 52 / 5 = 10.4. So it means that for each 1 mm increment, the volume rises by 10.4 liters. As our required length is 428, so we get total volume by adding these increments into the liters corresponding to 425; as 2534 + (10.4x3 ) = 2565.2

Can anyone help me with the above ?

Thanks in advance & Regards.

Omar

[bebo021999]

Is it similar to this thread?

Is the length interval alway 5?

[samba_ravi]

If you have your criteria in Cell C1 then try
=IFERROR(INDEX(B2:B23,MATCH(C1,A2:A23,0)),INDEX(B2:B23,MATCH(C1,A2:A23))+(INDEX(B2:B23,MATCH(C1,A2:A23)+1)-INDEX(B2:B23,MATCH(C1,A2:A23)))/(INDEX(A2:A23,MATCH(C1,A2:A23)+1)-INDEX(A2:A23,MATCH(C1,A2:A23)))*(C1-INDEX(A2:A23,MATCH(C1,A2:A23))))

[digimart]

It may be similar to that Vlookup thread, but I am not sure myself.

YES, the interval is always "5 ".

[digimart]

It may be similar to that Vlookup thread, but I am not sure myself.

YES, the interval is always "5 ".

[digimart]

i am unable to understand how to go about the Criteria

[bebo021999]

So, does Siva's solution work?

[samba_ravi]

see the attached file

[Czeslaw]

If you have your criteria in Cell C1 then try

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[bebo021999]

My solution:

=IFERROR(LOOKUP(LOOKUP(C1,$A$2:$A$23),$A$2:$A$23,$B$2:$B$23)+LOOKUP(LOOKUP(C1,$A$2:$A$23,$A$3:$A$24),$A$3:$A$23,$B$3:$B$23-$B$2:$B$22)*(C1-LOOKUP(C1,$A$2:$A$23))/5,$B$23)

[bebo021999]

@Czeslaw,

Percentile and percentrank do a great job! Although it does not work for first and last value.

It should be perfect with iferror combination! Thanks!

[digimart]

@nflsales . Thank you very much.
Your help has solved my problem 100%. Thanks once again
Regards.
Omar

see the attached file