Excel Lookup to the Nearest, Above or Below

This article is about how use lookup function to find the nearest item above or below in Excel or Calc. Currently, the default function in VLOOKUP or HLOOKUP only rounds to the smaller value, so it takes a few tricks to round the value to the closest one above or below.

Let us consider the following practical example. Suppose there is a marathon race, and in case if you have no idea, it has a running distance of 42 km. The organiser has made the following rule regarding first aid. At each interval of 7 km, including the start and the finish, there shall be an first-aid station which helps the injured runners.In other words, there are altogether 7 stations in total. It is expressed in the table below:

Location (Distance from the Start)	First-aid Station
0 km	Station 1
7 km	Station 2
14 km	Station 3
21 km	Station 4
28 km	Station 5
35 km	Station 6
42 km	Station 7

The policy of the organiser is that, if a runner is injured, the closest station will be responsible to come to rescue. Say, if a runner is injured at 4 km from the start, he is further away from Station 1 (the Start) then Station 2 (at 7 km), so Station 2 shall go to help him, and so on. In Excel or Calc, how can you programme this logic, so that when you enter the distance of runner from the Start, the responsible station could be found immediately?

There are many ways to do it, but the simplest one that does not involve loops of IF’s is like the following. First set up the following template:

The first step is to add two working columns in the table on the left, like the following. The new column B is almost like A, but just shifting the data one cell down. Column D is exactly like column A.

In G1, enter the distance of the injury from the Start, let us say it is 13. Then enter the following formulae in the these cells: (Note that this example is done with OpenOffice. If you are using Excel or Google Sheet, please replace all semicolons with commas.)

• G3: =VLOOKUP(G1;A1:C8;3;1)
• G4: =VLOOKUP(G3;C1:D8;2;1)
• G5: =G1-G4
• I3: =VLOOKUP(G1;B1:C8;2;1)
• I4: =VLOOKUP(I3;C1:D8;2;1)
• I5: =I4-G1
• G7: =IF(G5<I5;G3;I3)
• G8: =VLOOKUP(G7;C1:D8;2;1)

And then it will be like the following:

So, for an injury occurring at 13 km from the starting point, he shall be rescued by Station 3, located at 14 km from the starting point and 1 km away from the runner. You may change the value in G1 to see different results.

You may hide columns B and D after you have finished for aesthetic purpose. Also, after you have become familiar with the concept, you may skip all the intermediate steps and combine all the formulae into one single cell, as long as you do not feel that it is too complicated.