Advanced hlookup - find closest value

[smide]

Hello.


In row 2 there are a time stamps and in row 3 I have a values for each of those stamps.


Example.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Time stamps[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]42[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD] Values [/TD]
[TD]2.63[/TD]
[TD]2.53[/TD]
[TD]2.48[/TD]
[TD]2.44[/TD]
[TD]2.60[/TD]
[TD]2.67[/TD]
[TD]2.69[/TD]
[/TR]
</tbody>[/TABLE]

With a simple hlookup formula I could find value for a let say time stamp 39 (exact match), =hlookup(39,B2:H3,2,FALSE) and the result is 2.44.

However sometimes I need to find value for the closest time stamp in row 2, so if I need value for time stamp 38 (which of course does not exist in row 2) then I'm expecting that result should be also 2.44 because
39 is closest value from row 2. Also for let say time stamp 44 result is 2.53 (45 is closest number from row 2) and so on...

I'm asking this because simple change from FALSE to TRUE in hlookup formula does not solve issue, so I would like a formula (if possible) for this problem.

Note: the range of values and time stamps (rows 2 and 3) may contain zero values or blank cells and if that happens then the whole column is either zero or blank

 

[Rickmaurinus]

Hi Smide,

You can use the "MATCH" formula, to find which of the columns is smaller than your searched value.
Then the column bigger than your searched value, is the columns previously calculated +1.

With that result, you can use an INDEX formula on the VALUES, to find the VALUES related to the time stamp, for the column bigger than your searched value, and the column smaller than your bigger value. Then when you calculate which of the two is closest to your searched value, you can decide which to take.

I did this for you in a file:
http://s000.tinyupload.com/?file_id=05914197051512484284

The formula looks rather complex in the end, but I've shown each single step.
In the completed formula, I've simply replaced the cell references, with the real formulas.

Cheers!
Rick

 

[FryGirl]

Sorry, wrong answer

 

[smide]

Thank you for a such a detailed explanation.
Works great.

 

[Rickmaurinus]

As I got a comment from the administrator, let me clarify my post (please note, my data starts in column B, instead of column A, different from Smide's example):

First look for the column that has the time stamp smaller than the one you fill in by using a match formula:

=MATCH(  B6;C2:I2;-1)

then have a formula to look for the one bigger than that

=MATCH(  B6;C2:I2;-1) +1

Those two column numbers you can feed to an Index formula, to get the right VALUE. So:

=INDEX(  C2:I2; 0; MATCH(  B6;C2:I2;-1))

And

=INDEX(  C2:I2; 0; MATCH(  B6;C2:I2;-1)+1)

It's then important to see which of the two formula's has the time stamp, closest to the INPUT VARIABLE.

So substract the answers of the previous formula, from the INPUT value, and see which one is closer. I only want the Absolute number (to compare). So I don't have the risk of comparing a positive number, to a negative number. The end formula then is:

=IF(   ABS(INDEX(  C2:I2; 0; MATCH(  B6;C2:I2;-1)) - $B$6)  <=  ABS(INDEX(  C2:I2; 0; MATCH(  B6;C2:I2;-1)+1)-$B$6);         
       INDEX( C3:I3;;  MATCH(  B6;C2:I2;-1));
       INDEX(C3:I3;;   MATCH(  B6;C2:I2;-1)+1))

Please note, in my example cell B6 is the INPUT value.

That should do it!

Regards,
Rick

 

[steve the fish]

Note that the match example will only work on a sorted list of time stamps. If they are not in order it will give an incorrect result.