vlook up (average)

[jatman]

i have the following data:
A B
1 10 140
2 20 95
3 30 70
4 40 60
5 50 55

in C1 i enter 20, in D1 i have the formulae as follows:
= vlookup(C1,A:B,2,FALSE) and the return result is 95. can the formula be
changed so that it can look up a number (average) that is not in the A
column. example vlookup 35 returns a value of 65, or 31 returns a value of
69?

thank you,

jat

 

[Lars-Åke Aspelin]

i have the following data:
A B
1 10 140
2 20 95
3 30 70
4 40 60
5 50 55

in C1 i enter 20, in D1 i have the formulae as follows:
= vlookup(C1,A:B,2,FALSE) and the return result is 95. can the formula be
changed so that it can look up a number (average) that is not in the A
column. example vlookup 35 returns a value of 65, or 31 returns a value of
69?

thank you,

jat

Try this formula in D1:

=IF(C1=A5,B5,INDEX(B1:B5,MATCH(C1,A1:A5,1))+
(INDEX(B1:B5,MATCH(C1,A1:A5,1)+1)-INDEX(B1:B5,MATCH(C1,A1:A5,1)))*

(C1-INDEX(A1:A5,MATCH(C1,A1:A5,1)))/(INDEX(A1:A5,MATCH(C1,A1:A5,1)+1)-
INDEX(A1:A5,MATCH(C1,A1:A5;1))))

This will give you the linearly interpolated B values for C1 values
inbetween the values in column A. Is that what you wanted?

Hope this helps. / Lars-Åke

 

[Sandy Mann]

If the value in Column A are always indexing by 10 then will this formula
return the proportional response that you are looking for?

=IF(FLOOR(C1,10)=C1,VLOOKUP(C1,A2:B6,2,TRUE),VLOOKUP(C1,A2:B6,2,TRUE)-(C1-FLOOR(C1,10))/(CEILING(C1,10)-FLOOR(C1,10))*((INDEX(B2:B6,MATCH(FLOOR(C1,10),A2:A6,FALSE)))-(INDEX(B2:B6,MATCH(CEILING(C1,10),A2:A6,FALSE)))))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk