Complicated Lookup

[Classick]

Hey guys,

So i have a pretty complicated lookup im trying to perform, but its based on a formula, so i think its possible. I need this to be performed as a cell function, not as a VBA macro as it will be used as a just in time lookup, if that makes any sense.

So i have a table with the following structure. Proof is the index column, Temperature is the column header

Rules:
Temperature only increases by increments of 2 degrees
Proof increases by increments of 5 proof


A1 B1 C1 D1 E1 F1
--------------------------------------------
Temperature 22 24 26 28 30 etc
Proof
5
10
15
20
25
30
etc
etc
etc
200

I also have my 2 proof and temperature input cells and the results cell, lets call them AA1, AB1, AC1 respectively

Now, if the temperature i am looking up is an even number, no problems because MATCH() will find it and reference it in the multi-key lookup as such

VLOOKUP(AA1,A:Z,(MATCH(AB1,A:Z,1)))

However, if the temperature is NOT an even number, then the formula becomes more complicated.

Here is the description of the formula i am trying to create.

Where the spirits to be corrected are of an odd temperature, 1/2 of the difference, if any, between the factors for the next higher and lower temperature, should be added to the factor for the next higher temperature.

example: it is desired to correct a spirit of 180 proof at 51 degrees

1.006 ( which is the value in the table for 50 degrees) - 1.005 ( which is the value in the table for 50 degrees) = .001 / 2 = .0005

.0005 + 1.005 ( which is the value in the table for 50 degrees) = 1.0055 ( which would be the correction factor at 51 degrees)

You can see its very confusing just to begin with.

I guess i am looking for help with the logic a bit.

So far i have

If(find if temp is even using MOD()) then
VLOOKUP(AA1,A:Z,(MATCH(AB1,A:Z,1)))
else
this is the part i am having trouble visualizing


Any help is greatly appreciated excel gurus!

 

[Classick]

Well... this is the way i came up with to do it... but id be curious if anyone could think of a better way.

C46 = Proof
C47 = Temperature


=IF(MOD(C47,2)=0,VLOOKUP(C46,A:AQ,MATCH(C47,A1:AQ1,1)),VLOOKUP(C46,A:AQ,MATCH(C47+1,A1:AQ1,1))+((VLOOKUP(C46,A:AQ,MATCH(C47-1,A1:AQ1,1))-VLOOKUP(C46,A:AQ,MATCH(C47+1,A1:AQ1,1)))/2))